IF / OR formula help

Hi community,

hope somebody may be able to help.

I am trying to link a % of completion column with a progress bar. Formula is as follows:

=IF([% complete]1 = "0", "Empty", IF(OR([% complete]1 > "0", [% complete]1 <= "0.25"), "Quarter", IF(OR([% complete]1 > "0.25", [% complete]1 <= "0.5"), "Half", IF(OR([% complete]1 > "0.5", [% complete]1 <= "0.75", "Three Quarter"), IF(OR([% complete]1 > "0.75", [% complete]1 <= "1", "Full"))))))

It only correctly returns "Empty"results, it also returns "Quarter" although incorrectly as it applies "Quarter" to all results diverse from zero.

Thank you in advance

Alex

Find more posts tagged with

Finance

Formulas

Marketing

IT & Operations

Project Management

Comments

L_123

=IF([% complete]1 = 0, "Empty", IF( [% complete]1 <= .25, "Quarter", IF( [% complete]1 <= 0.5, "Half", IF([% complete]1 <= .75, "Three Quarter", "Full"

Give that a once over and a try.

Alessandro Terranova

Thank you for your response, I eventually came up with a solution that actually works:

=IF([% complete]1 = "0", "Empty", IF(AND([% complete]1 > "0", [% complete]1 <= "0.25"), "Quarter", IF(AND([% complete]1 > "0.25", [% complete]1 <= "0.5"), "Half", IF(AND([% complete]1 > "0.5", [% complete]1 <= "0.75"), "Three Quarter", IF(AND([% complete]1 > "0.75", [% complete]1 <= "1"), "Full")))))

Mike Wilday

L@123's formula should work. But for teaching purposes, your original formula had an OR statement for the Quarter that stated IF the %complete was greater than 0 OR less than .25. It could have been written with an AND instead of OR to make that work. Once the formula saw it was over 0 (any value) it would apply the quarter to everything over 0. IF formulas always stop at the first true statement it finds. Your quarter statement was true for every value over 0.

BUT

L@123's works as well (and is less complicated to write) because it first looks at the value and determines if its zero. Ifi its true, it puts "empty", then it looks for anything that is less than .25 (0's have already been dealt with) then it looks for anything that is less than half, and so forth and so on. You don't need the range of values because the IF statement already processed those cases before moving on to the 2nd and 3rd statements.

Hope that helps explain what is going on.

Alessandro Terranova

Thank you Mike, appreciate your feedback

Mike Wilday

You're welcome!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…