Results 1 to 3 of 3

Thread: Combining 2 or more IF(AND statements in to one IF(AND(OR statement

  1. #1

    Combining 2 or more IF(AND statements in to one IF(AND(OR statement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Is it possible to combine 2 or more IF(AND statements into one IF(AND(OR statement in Excel 2007?

    Following are two examples in which I'm attempting to make it easier to follow my question.

    Example One: Written separately it would like this:

    =IF(AND(Length<=0.875,Nominal_Size<=0.625),0.031,0.094)
    =IF(AND(Length>=1.000,Nominal_Size<=0.625),0.063,0.094)

    Length = Looks at a cell with a value (Example 0.500, 1.000, 2.000 etc.).
    Nominal_Size = Looks at a cell with a value (Example 0.500, 0.625, 0.750 etc.).

    Is there a way to combine these two statements into a single cell?

    Example Two: Written separately it would like this:

    =IF(AND(Length<=0.875,Nominal_Size<=0.625),0.250,0.313)
    =IF(AND(Length =1.000,Nominal_Size<=0.625),0.407,0.438)
    =IF(AND(Length =1.125,Nominal_Size<=0.625),0.407,0.438)
    =IF(AND(Length>=1.250,Nominal_Size<=0.625),0.594,0.625)

    Length = Looks at a cell with a value (Example 0.500, 1.000, 2.000 etc.).
    Nominal_Size = Looks at a cell with a value (Example 0.500, 0.625, 0.750 etc.).

    Is there a way to combine these four statements into a single cell?

    Thank you in advance for your help.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Like this?

    =IF(AND(Length<=0.875,Nominal_Size<=0.625),0.031,IF(AND(Length>=1.000,Nominal_Size<=0.625),0.063,0.094))

    I'll admit though, you can keep nesting IF's up to a limit, but you're probably better to build a table and use VLOOKUP to get this kind of info.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    As an alternative to what Ken has already provided:

    Example One

    Code:
    =IF(Nominal_Size<=0.625,IF(Length<=0.875,0.031,IF(Length>=1,0.063,0.094)),0.094)
    However, your original formulas don't appear to provide an answer for the example data:

    Length=0.9 and Nominal_Size=0.5 other than 0.094, is this by design?

    Example Two

    Again, the original formulas don't seem to take into account certain number ranges, i.e.:

    Length=0.9 and Nominal_Size=0.5
    Length=1.1 and Nominal_Size=0.5
    Length=1.2 and Nominal_Size=0.5

    are you missing some < or > signs, or do you intend to ignore these examples?

Posting Permissions

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