How many concurrent nested IF statments does Excel allow?

This formula works:
=IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))

This formula does not, why?
=IF(B4>0,IF(H4 > 449, 1.6,
IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))

Hi!

The top formula has 7 nested levels which is the limit.

The bottom formula has 8.

How many conditions do you have in total?

Biff

"Loudmouth" <[email protected]> wrote in message
news:[email protected]...
> This formula works:
> =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>
> This formula does not, why?
> =IF(B4>0,IF(H4 > 449, 1.6,
> IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))

As Biff has noted, the limit is 7 nested functions in a single formula. Most
people think this limit applies only to nested IF statements, but it is
actually a limit on the total number of nested functions of any type.

The second point to be made is that the limit is per formula. You could put
a + symbol at the end of your first formula and then repeat that same formula
and it would still work (although the result would be doubled) because each
individual formula only has 7 nested levels.

"Loudmouth" wrote:

> This formula works:
> =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>
> This formula does not, why?
> =IF(B4>0,IF(H4 > 449, 1.6,
> IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))

I need about 25 to make it work. Can you suggest another way of comparing
that many numbers?

"Biff" wrote:

> Hi!
>
> The top formula has 7 nested levels which is the limit.
>
> The bottom formula has 8.
>
> How many conditions do you have in total?
>
> Biff
>
> "Loudmouth" <[email protected]> wrote in message
> news:[email protected]...
> > This formula works:
> > =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> > IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
> >
> > This formula does not, why?
> > =IF(B4>0,IF(H4 > 449, 1.6,
> > IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> > IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>
>
>

I don't know exactly what you're doing but you need to create a table. This
table is based on your bottom formula:

0............0..........0
124.....0.45.....0.35
149.....0.55.....0.45
199.....0.7.......0.55
249.....0.9.......0.7
299.....1.05.....0.85
349.....1.25.....1
399.....1.4
499.....1.6

The 2nd column would be used when B4>0 and the third column would be used
when B4<=0. (at least, that's the logic of your IF formulas)

=IF(B4>0,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3,1))

Where D1:E9 is the above table.

Biff

"Loudmouth" <[email protected]> wrote in message
news:[email protected]...
>I need about 25 to make it work. Can you suggest another way of comparing
> that many numbers?
>
> "Biff" wrote:
>
>> Hi!
>>
>> The top formula has 7 nested levels which is the limit.
>>
>> The bottom formula has 8.
>>
>> How many conditions do you have in total?
>>
>> Biff
>>
>> "Loudmouth" <[email protected]> wrote in message
>> news:[email protected]...
>> > This formula works:
>> > =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
>> > IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>> >
>> > This formula does not, why?
>> > =IF(B4>0,IF(H4 > 449, 1.6,
>> > IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
>> > IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>>
>>
>>

This might be confusing:

>=IF(B4>0,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3,1))
>Where D1:E9 is the above table.

Those table ranges, D1:E9 and D1:F7, are based on the unequal number of
entries in each column But I'm guessing that it's incomplete just because
you ran into nesting problems.

To make it less confusing just change it to: (and finish your table)

=IF(B4>0,VLOOKUP(H4,D1:F9,2,1),VLOOKUP(H4,D1:F9,3,1))

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
>I don't know exactly what you're doing but you need to create a table. This
>table is based on your bottom formula:
>
> 0............0..........0
> 124.....0.45.....0.35
> 149.....0.55.....0.45
> 199.....0.7.......0.55
> 249.....0.9.......0.7
> 299.....1.05.....0.85
> 349.....1.25.....1
> 399.....1.4
> 499.....1.6
>
> The 2nd column would be used when B4>0 and the third column would be used
> when B4<=0. (at least, that's the logic of your IF formulas)
>
> =IF(B4>0,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3,1))
>
> Where D1:E9 is the above table.
>
> Biff
>
> "Loudmouth" <[email protected]> wrote in message
> news:[email protected]...
>>I need about 25 to make it work. Can you suggest another way of comparing
>> that many numbers?
>>
>> "Biff" wrote:
>>
>>> Hi!
>>>
>>> The top formula has 7 nested levels which is the limit.
>>>
>>> The bottom formula has 8.
>>>
>>> How many conditions do you have in total?
>>>
>>> Biff
>>>
>>> "Loudmouth" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > This formula works:
>>> > =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
>>> > IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>>> >
>>> > This formula does not, why?
>>> > =IF(B4>0,IF(H4 > 449, 1.6,
>>> > IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
>>> > IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>>>
>>>
>>>
>
>

I think the easiest way to solve this problem is going to be for you to set
up a lookup table with your associated values in it and then use VLOOKUP()
inside of an IF() statement to pick your choices. This is difficult to
explain in words, so I prepared an example workbook which you can get at:
http://www.jlathamsite.com/uploads/v..._Loudmouth.xls

your formula in the cell where you're trying to build it now is going to
look something like
=IF(B4>0,VLOOKUP(H4,H9:I16,2,True),VLOOKUP(H4,H9:J16,3,True))

The ranges mentioned, H9:I16 and H9:J16, will need to be changed to match
the range you set up in the real world.

A couple of things to note, the list must be in ascending order from top to
bottom based on your to-match values (399, 349, etc) in order for it to work
properly and reliably. The lookup matrix does not have to be on the same
sheet, you just have to be sure and reference the lookup range correctly.
For more information, look for VLOOKUP under Excel Help. I hope this helps
some.

"Loudmouth" wrote:

> This formula works:
> =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
>
> This formula does not, why?
> =IF(B4>0,IF(H4 > 449, 1.6,
> IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))

Or even...

=VLOOKUP(H4,D1:F9,if(b4>0,2,3),1)


Biff wrote:
>
> This might be confusing:
>
> >=IF(B4>0,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3,1))
> >Where D1:E9 is the above table.
>
> Those table ranges, D1:E9 and D1:F7, are based on the unequal number of
> entries in each column But I'm guessing that it's incomplete just because
> you ran into nesting problems.
>
> To make it less confusing just change it to: (and finish your table)
>
> =IF(B4>0,VLOOKUP(H4,D1:F9,2,1),VLOOKUP(H4,D1:F9,3,1))
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
> >I don't know exactly what you're doing but you need to create a table. This
> >table is based on your bottom formula:
> >
> > 0............0..........0
> > 124.....0.45.....0.35
> > 149.....0.55.....0.45
> > 199.....0.7.......0.55
> > 249.....0.9.......0.7
> > 299.....1.05.....0.85
> > 349.....1.25.....1
> > 399.....1.4
> > 499.....1.6
> >
> > The 2nd column would be used when B4>0 and the third column would be used
> > when B4<=0. (at least, that's the logic of your IF formulas)
> >
> > =IF(B4>0,VLOOKUP(H4,D1:E9,2,1),VLOOKUP(H4,D1:F7,3,1))
> >
> > Where D1:E9 is the above table.
> >
> > Biff
> >
> > "Loudmouth" <[email protected]> wrote in message
> > news:[email protected]...
> >>I need about 25 to make it work. Can you suggest another way of comparing
> >> that many numbers?
> >>
> >> "Biff" wrote:
> >>
> >>> Hi!
> >>>
> >>> The top formula has 7 nested levels which is the limit.
> >>>
> >>> The bottom formula has 8.
> >>>
> >>> How many conditions do you have in total?
> >>>
> >>> Biff
> >>>
> >>> "Loudmouth" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > This formula works:
> >>> > =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> >>> > IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
> >>> >
> >>> > This formula does not, why?
> >>> > =IF(B4>0,IF(H4 > 449, 1.6,
> >>> > IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> >>> > IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
> >>>
> >>>
> >>>
> >
> >

--

Dave Peterson

Outstanding! Thank you very much. I am now able to complete my project.

"JLatham" wrote:

> I think the easiest way to solve this problem is going to be for you to set
> up a lookup table with your associated values in it and then use VLOOKUP()
> inside of an IF() statement to pick your choices. This is difficult to
> explain in words, so I prepared an example workbook which you can get at:
> http://www.jlathamsite.com/uploads/v..._Loudmouth.xls
>
> your formula in the cell where you're trying to build it now is going to
> look something like
> =IF(B4>0,VLOOKUP(H4,H9:I16,2,True),VLOOKUP(H4,H9:J16,3,True))
>
> The ranges mentioned, H9:I16 and H9:J16, will need to be changed to match
> the range you set up in the real world.
>
> A couple of things to note, the list must be in ascending order from top to
> bottom based on your to-match values (399, 349, etc) in order for it to work
> properly and reliably. The lookup matrix does not have to be on the same
> sheet, you just have to be sure and reference the lookup range correctly.
> For more information, look for VLOOKUP under Excel Help. I hope this helps
> some.
>
> "Loudmouth" wrote:
>
> > This formula works:
> > =IF(B4>0,IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> > IF(H4>124,0.45)))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))
> >
> > This formula does not, why?
> > =IF(B4>0,IF(H4 > 449, 1.6,
> > IF(H4>399,1.4,IF(H4>349,1.25,IF(H4>299,1.05,IF(H4>249,0.9,IF(H4>199,0.7,IF(H4>149,0.55,
> > IF(H4>124,0.45))))))),IF(H4>349,1,IF(H4>299,0.85,IF(H4>249,0.7,IF(H4>199,0.55,IF(H4>149,0.45,IF(H4>124,0.35,0)))))))