Results 1 to 8 of 8

Thread: Simple "IF" statement for a newbie

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Simple "IF" statement for a newbie

    I have a sheet which has a list of numbers ranging from -10000 to 10000. I need an IF statement which says if the number is less than -1500 then it should read "red" if it is less than -500 then read "amber" and if it is greater than -500 then should read "green"

    I can get it do parts but struggling really, any help would be appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,395
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Simple "IF" statement for a newbie

    Welcome to the MrExcel board!

    Quote Originally Posted by ahmedsadak View Post
    .. if it is less than -500 then read "amber" and if it is greater than -500 then should read "green" ..
    This doesn't tell us what to do if exactly equal to -500, but see if this is any use.

    redambergreen

    AB
    1-5000red
    28000green
    3-285green
    4-55green
    5-721amber
    60green
    7200green

    Spreadsheet Formulas
    CellFormula
    B1=LOOKUP(A1,{-10000,-1500,-500},{"red","amber","green"})


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple "IF" statement for a newbie

    Hi
    thanks for the answer, sorry i wasnt clear enough, it should be less than or equal to or Greater than or equal to. - would i use LOOKUP rather than IF with an AND

  4. #4
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple "IF" statement for a newbie

    Nice, had no clue you could use Lookup in that way. I would have gone straight to conditional formatting with three color and use the => to set the values.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,395
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Simple "IF" statement for a newbie

    This still isn't clear. Which one is it?
    Quote Originally Posted by ahmedsadak View Post
    .. it should be less than or equal to or Greater than or equal to. ...
    Please spell out again carefully in full the requirement for each colour result.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple "IF" statement for a newbie

    Quote Originally Posted by Peter_SSs View Post
    This still isn't clear. Which one is it?


    Please spell out again carefully in full the requirement for each colour result.

    Apologies.

    A1 has a list of numbers.
    between -10,000 and 10,000
    B2 needs to say Red if the number in A1 is below or equal to -1500 or Amber if it is less than or equal to -500 or Red if it is greater than 500

    Thanks in advance

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple "IF" statement for a newbie

    Quote Originally Posted by ahmedsadak View Post
    Apologies.

    A1 has a list of numbers.
    between -10,000 and 10,000
    B2 needs to say Red if the number in A1 is below or equal to -1500 or Amber if it is less than or equal to -500 or Red if it is greater than 500

    Thanks in advance
    Are you saying you want the work RED, the word AMBER, and then the word RED again?

    -1501 RED
    -600 AMBER
    600 RED

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,395
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Simple "IF" statement for a newbie

    Quote Originally Posted by ahmedsadak View Post
    B2 needs to say Red if the number in A1 is below or equal to -1500 or Amber if it is less than or equal to -500 or Red if it is greater than 500
    Carefully?? Is that last number supposed to be -500? And Red a second time?

    Guessing, try
    =IF(A1<=-1500,"Red",IF(A1<=-500,"Amber","Green"))

    If the 500 at the end of that last quote is correct, what happens above -500 up to and including 500?
    Last edited by Peter_SSs; Sep 19th, 2019 at 08:18 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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