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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…