If Formula

TracyS

I'm trying to add and additional item to this IF Statement, when the total column is exceeding 150 and the Total Percent Used Column is Blank. This is my existing IF Statement

=IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds"))))

I'm trying to add the following: IF(AND([Total Percent Used]@row = 0, [Total]@row >150000, "Review")

The second formula is not working when added or returns a blank. What am I missing

TracyS

Thank you for your help, this was my final formula

=IF(AND([Total Percent Used]@row < 0.01, Total@row > 150000), "Review", IF([Total Percent Used]@row < 0.01, "", IF(AND([Total Percent Used]@row > 0.02, [Total Percent Used]@row <= 0.5), "In Range", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF(AND([Total Percent Used]@row >= 0.75, [Total Percent Used]@row < 1), "Notify", IF([Total Percent Used]@row >= 1, "Exceeds"))))))

Lucas Rayala

Hi @TracyS — the percent used column seems to be empty in most cases. You are looking for the specific value of "0", which does not occur in the screenshot. Try this instead:

=IF(AND([Total Percent Used]@row = "", [Total]@row >150000, "Review")

bisaacs

Hey @TracyS,

Since you have a formula already checking if the % is less than 50, it'll trigger that true statement before it'll trigger the one you're trying to add (assuming you're adding it at the end of the chain of IFs. I'd put the new IF statement as the first IF in the larger formula first.

Secondly, it appears you're missing a closing parenthesis around the AND portion of your statement:

IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review")

So as a whole, the formula should work if it's entered like this:

=IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review", IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds")))))

Hope this helps!

Lucas Rayala

If you do sometimes see the value 0 and also want to include that criteria, then update it to:

=IF(AND(OR([Total Percent Used]@row = 0, [Total Percent Used]@row = ""), [Total]@row >150000, "Review")

TracyS

Ok, so nothing returned for the review on the blue line. I added the formula you provided:

=IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review", IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds")))))

TracyS

Thank you for your help, this was my final formula

=IF(AND([Total Percent Used]@row < 0.01, Total@row > 150000), "Review", IF([Total Percent Used]@row < 0.01, "", IF(AND([Total Percent Used]@row > 0.02, [Total Percent Used]@row <= 0.5), "In Range", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF(AND([Total Percent Used]@row >= 0.75, [Total Percent Used]@row < 1), "Notify", IF([Total Percent Used]@row >= 1, "Exceeds"))))))