Formula working for half of the IF Statements

S_Ko

Hi there,

I'm writing a formula to translate a %complete Column into the progress bar. So far I have teh formula outlined as:

=IF([% Complete]@row = 0, "Empty", IF(AND([% Complete]@row > 0.01, [% Complete]@row < 0.25), "Quarter", IF(AND([% Complete]@row > 0.26, [% Complete]@row < 0.5), "Half", IF(AND([% Complete]@row > 0.51, [% Complete]@row < 0.75, "Three Quarter", IF(AND([% Complete]@row > 0.76, [% Complete]@row < 1), "Full"))))))

HOWEVER, it works for everything EXCEPT the last IF statement. I'm getting #IncorrectArgument for anything over 50%. I need another set of eyes to trouble shoot why it's breaking for the last 3 but working fine for the first few.

Brian Wilson DC

@S_Ko Good afternoon! I believe that you are receiving this error, because you are missing the closing parentheses after the <0.75. It should be <0.75), "Three Quarter"

Please let me know if that helped!

Regards,

Brian

Brian Wilson DC

@S_Ko Good afternoon! I believe that you are receiving this error, because you are missing the closing parentheses after the <0.75. It should be <0.75), "Three Quarter"

Please let me know if that helped!

Regards,

Brian

S_Ko

OMG Thank you! I've been staring at that trying to see what I missed and completely did not see that missing parentheses. That fixed it. Much appreciated @Brian

Brian Wilson DC

You're welcome! Sometimes it definitely helps to have an extra set of eyes : )

Paul Newcome

Couple of things here to keep in mind moving forward…

Nested IF statements read from left to right and stop on the first true value. That means any IF that is passes to move on to the next is assumed to be false. You can leverage this to cut down on the AND statements when following a logical thread such as this. For example, your second IF doesn't need the AND. You can just use the less than 0.25 piece because it is automatically assumed that it is greater than zero by it passing over the first IF.

This will also help negate the second issue. These percentages won't get flagged: 1%, 25%, 26%, 50%, 51%, 75%, 76%, and 100%. Your first IF says "equals zero", but then your second includes an argument for "greater than 0.01". You do the same throughout with the rest of the "in betweens". Less than 0.25 is followed by greater than 0.26, so on and so forth. You COULD use less/greater than or equal to <= or >= to cover these gaps, but combining that with the first part of this will also help cut down on quite a bit of typing.

=IF([% Complete]@row = 0, "Empty", IF([% Complete]@row <= 0.25, "Quarter", IF([% Complete]@row <= 0.5, "Half", IF([% Complete]@row <= 0.75, "Three Quarter", "Full"))))

The above will cover each of the gaps and requires quite a bit less typing by leveraging these two tips, and could save you quite a bit of time and headache in the future.

S_Ko

@Paul Newcome Thank you for this, I had originally typed out the IF statement this way but was finding it was not moving past the 25% or "Quarter" bar for all projects- which is why I integrated the AND statement and noted it this way.