# 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

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"))

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?

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?

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

You're are lifesaver. Thank you

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.

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"))

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?

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...

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"))

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

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")))

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.

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?

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

That did work, thank you

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1