# Excel Formula using IF to get one of two true results or false results

1. ## Excel Formula using IF to get one of two true results or false results

Help...

I am trying to write a formula that looks at B3 and if it's 0 to leave it that way but if B3's value is greater than or equal to 900 or C3's value is greater than or equal to 175 to enter STOP but if not then enter OK. But I also want it to see is B3's value falls between 800 and 899 or C3's value falls between 100 and 174 to enter CAUTION if true but if not "".

Any ideas.

=IF(\$B3=0," ",IF(OR(\$B3>=900,\$C3>=175),"STOP","OK")) This one works for the STOP portion but I tried adding the extra criteria to indicate caution or OK or "" and nothing works

This is what I tried
=IF(\$B3=0," ",IF(OR(\$B3>=900,\$C3>=175),"STOP","OK")(IF(\$B3=MEDIAN(800:899),"CAUTION","")))

and

=IF(\$B3=0," ",IF(OR(\$B3=MEDIAN(800:899)),"CAUTION",("\$B3>=900,\$C3>=175),"STOP","OK")

nothing works  Register To Reply

2. ## Re: Excel Formula using IF to get one of two true results or false results

Probably:

=IF(\$B3=0,"",IF(OR(\$B3>=900,\$C3>=175),"STOP",IF(\$B3=MEDIAN(800:899),"CAUTION","OK"))  Register To Reply

3. ## Re: Excel Formula using IF to get one of two true results or false results

Tried this and the Stop function works but when the results are between 800-899 I get #NUM. Any suggestions?  Register To Reply

4. ## Re: Excel Formula using IF to get one of two true results or false results

there should be a comma between the values, not a semi:colon...

not sure what you mean to do with the MEDIAN() function, though... as that gives a single result of 849.5?  Register To Reply

5. ## Re: Excel Formula using IF to get one of two true results or false results

You're are lifesaver. Thank you  Register To Reply

6. ## Re: Excel Formula using IF to get one of two true results or false results

Nevermind it didn't work. I was thinking median could work to see if the value falls between 800 and 899. If it does, show CAUTION but if it doesn't then OK. Any ideas? I did a google search and someone suggested that.  Register To Reply

7. ## Re: Excel Formula using IF to get one of two true results or false results

Then apply similar logic as your first part:

=IF(\$B3=0,"",IF(OR(\$B3>=900,\$C3>=175),"STOP",IF(AND(\$B3>=800,\$B3<=899),"CAUTION","OK"))  Register To Reply

8. ## Re: Excel Formula using IF to get one of two true results or false results

I also need it to look at \$C3>=100,\$C3<=174, how would I incorporate that?  Register To Reply

9. ## Re: Excel Formula using IF to get one of two true results or false results

insert them within the same AND() function in the formula...  Register To Reply

10. ## Re: Excel Formula using IF to get one of two true results or false results

What cell is all of this happening in? Because this
I am trying to write a formula that looks at B3 and if it's 0 to leave it that way
makes it sound like you want all this to occur in B3.

Based on the rest of your first post, (assuming it's not in B3)
=IF(OR(B3>=900,C3>=175), "STOP", IF(OR(B3>=800, C3>= 100),"Caution", "OK"))  Register To Reply

11. ## Re: Excel Formula using IF to get one of two true results or false results

I tried =IF(\$B3=0,"",IF(OR(\$B3>=900,\$C3>=175),"STOP",IF(AND(\$B3>=800,\$B3<=899,\$C3>=100,\$C3<=174),"CAUTION","OK")))

and B3 has 800 so it should bring in CAUTION but it's showing OK. I even tried changing the AND to OR and Caution did show but then when I change B3 to 799 it still showed caution when it should show OK.

I have attached my spread sheet and only have the tabs that pertain showing. "STUDENT WORKER LIMITS" is the tab that I am working on and I am only messing with line 2 and 3 for now trying to get this to work. The hours and days info pulls from the first tab.

12-13 budget.xlsx  Register To Reply

12. ## Re: Excel Formula using IF to get one of two true results or false results

Then it's:

=IF(\$B2=0,"",IF(OR(\$B2>=900,\$C2>=175),"STOP",IF(OR(AND(\$B2>=800,\$B2<=899),AND(\$C2>=100,\$C2<=174)),"CAUTION","OK")))  Register To Reply

13. ## Re: Excel Formula using IF to get one of two true results or false results

Awesome, Thank you Thank you Thank you. I am learning so much through these forums. Your help is greatly appreciated.  Register To Reply

14. ## Re: Excel Formula using IF to get one of two true results or false results

FYI, I think you don't need the "ANDs" because you've already looked at values higher than that range so the formula can be simplified to

=IF(\$B2=0,"",IF(OR(\$B2>=900,\$C2>=175),"STOP",IF(OR(\$B2>=800,\$C2>=100), "CAUTION","OK")))
Yes?  Register To Reply

15. ## Re: Excel Formula using IF to get one of two true results or false results

That did work, thank you  Register To Reply