PDA

View Full Version : Combining 2 or more IF(AND statements in to one IF(AND(OR statement

Tsqman
2012-04-05, 05:11 PM
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?

Ken Puls
2012-04-05, 10:46 PM
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.

CheshireCat
2012-04-06, 06:52 AM
As an alternative to what Ken has already provided:

Example One

=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?