This formula uses more levels of nesting than you can use in the current file format

I have a really long nested formula, is there anyway I can make it shorter to work?

=IF(OR(AND(BR2="Closed",BS2="Not Started",BS2="Open",BT2="Not Started",BT2="Open",BU2="Not Started",BU2="Open"),"25%",IF(OR(AND(BR2="Closed",BS2="Closed",BT2="Not Started",BT2="Open",BU2="Not Started",BU2="Open"),"50%",IF(OR(AND(BR2="Closed",BS2="Closed",BT2="Closed",BU2="Not Started",BU2="Open"),"75%",IF(OR(AND(BR2="Closed",BS2="Closed",BT2="Closed",BU2="Closed"),"100%","0%"))))

enter image description here

enter image description here

  • 1
    you have OR(AND( in each part, but closing brackets are not matching (only one ) exists for each of them, that makes much more difficult to understand your formula. Please fix it. Also please post some sample output with desired results to help us understand your situation – Máté Juhász Sep 12 at 7:54
  • 1
    You can use helper columns to break up the conditions on smaller portions – Andreas Sep 12 at 8:25
  • I don't understand the constructions like ... AND( ..., BS2="Not Started",BS2="Open", ...). How does the cell value can be equal to 2 different values at the same time? – Akina Sep 12 at 8:27
  • Recommendation: =SWITCH(1+(BR2="Closed")+(BS2="Closed")+(BT2="Closed")+(BU2="Closed"),"0%","25%","50%","75%","100%"). – Akina Sep 12 at 8:29
  • 2
    Your formula is entirely bogus and doesn't work. Unless you provide the formula you're actually using or at least a run down of its logic there isn't really much anyone can do. If it is the formula you're using it isn't working because it's syntactically incorrect, not because it's nested too deeply. If all you react on is the "closed" text just check from the "most" important field to the least. – Seth Sep 12 at 9:49

Just had another thought to really simplify it, assuming I fully understood the question. You are trying to get the percentage of 4 tasks completed so then the formula could simply be. =countif(BR2:BU2,"Closed')/4

enter image description here

  • this last one has worked, thank you very much. – Stellamarie Sep 12 at 13:30

I thought of three things when I saw your formula. The first simple one (not a solution). Press Alt&Enter before your start your next if statement. It makes it easier to read the formula as you will be able to compare the statements. As mentioned above, a helper column might make things a lot easier. You use this column to create values based off values in the other so the formula is less complex.

I was then curious about what you were looking to do; and as I typed this i had another idea. It looks like your percentages are based on the number closed. How about a formula like this. =if(countif(BR2:BU2)=1,.25,if(countifs(BR2:BU2)=2,.5() and nest from there. Please see the simple sample below.

enter image description here

To keep things cleaner you could pu the countif formula in a helper column, then just have a simpler formula using the count from that column.

I hope this helps, Brad

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.