Results 1 to 5 of 5
Like Tree2Likes
  • 2 Post By Gerald Higgins

How to do a complex if and formula in excel

This is a discussion on How to do a complex if and formula in excel within the Excel Questions forums, part of the Question Forums category; I want to know how to do the following in excel I've written if and loops but I haven't been ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    34

    Default How to do a complex if and formula in excel

    I want to know how to do the following in excel I've written if and loops but I haven't been able to figure this one out.

    I have numbers in column B

    If the value in column B are greater then 0 and less than or equal to .25 I want to display the number 1
    If the value in column B are greater then .25 but less than or equal to .50 I want to display the number 2
    If the value in column B are greater then .50 but less than or equal to .75 I want to display the number 3
    If the value in column B are greater then .75 but less than or equal to 1.00 I want to display the number 4

  2. #2
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: How to do a complex if and formula in excel

    =IF(OR(B1="",B1>1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))

    I did not take into account negative numbers but I hope you will not enter them
    but if this is going to happen
    =IF(OR(B1="",B1>1,B1<1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,150

    Default Re: How to do a complex if and formula in excel

    An alternative approach, if you are 100% certain that the values will ALWAYS be between 0 and 1.

    =roundup(B1/.25,0)
    Robert Mika and 1968rm like this.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    Board Regular
    Join Date
    Dec 2010
    Posts
    172

    Default Re: How to do a complex if and formula in excel

    Quote Originally Posted by Robert Mika View Post
    I did not take into account negative numbers but I hope you will not enter them
    but if this is going to happen
    =IF(OR(B1="",B1>1,B1<1),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
    You probably meant =IF(OR(B1="",B1>1,B1<0),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
    "It is necessary; therefore, it is possible."

  5. #5
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: How to do a complex if and formula in excel

    Quote Originally Posted by 1968rm View Post
    You probably meant =IF(OR(B1="",B1>1,B1<0),"",LOOKUP(B1,{0,0.25,0.5,0.75},{1,2,3,4}))
    Yes.
    Thank you for the correction.
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

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
  •  


DMCA.com