+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    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. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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"))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    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. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #5
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

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

    You're are lifesaver. Thank you

  6. #6
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    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. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #8
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    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. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    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"))
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  11. #11
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    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. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #13
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

    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. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    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. #15
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    170

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

    That did work, thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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