Maximum Number of Functions in a Formula?

[entreri]

I am having difficulty in entering a formula in excel with multiple
embedded "if" statements. The formula will work correctly if I only
enter up to 8 of these statements. As soon as the ninth one is
entered I recieve a "statement error" message. Is there a maximum
number of times "if" can be entered into a formula? Any tips or
suggestions would be greatly appreciated.

Regards

PS- here is an formula which I am referring to:

+IF($D$18>=$AA$14,AA15*$D$9,IF($D$18>=$Z$14,Z15*$D$9,IF($D$18>=$Y$14,Y15*$D$9,IF($D$18>=$X$14,X15*$D$9,IF($D$18>=$W$14,W15*$D$9,IF($D$18>=$V$14,V15*$D$9,IF($D$18>=$U$14,U15*$D$9,IF($D$18>=$T$14,T15*$D$9,
THIS IS WHERE I WOULD RECIEVE MY ERROR...

 

[Don Guillett]

You can only have 7 nested ifs and the formula itself can get too long so
try to rewrite using some of these ideas
1. get rid of the $'s if possible
2. All is getting multiplied by d9, so do that last ie: =myformula*d9
3. a way to determine max of ranges is =MAX(G13,I13,L13)
Play with is some more

 

[Kevin McClement]

Don is right, the way i go from one to the next is by
having the last false reference point to the next cell
over where i continue my IF's. Depending on the
situation, there may be a better alternative to all the
IF's though. feel free to contact me direct, just remopve
the NOSPAM from the address.
HTH
Kevin McClement

 

[Ron Rosenfeld]

I am having difficulty in entering a formula in excel with multiple
embedded "if" statements. The formula will work correctly if I only
enter up to 8 of these statements. As soon as the ninth one is
entered I recieve a "statement error" message. Is there a maximum
number of times "if" can be entered into a formula? Any tips or
suggestions would be greatly appreciated.

Regards

PS- here is an formula which I am referring to:

+IF($D$18>=$AA$14,AA15*$D$9,IF($D$18>=$Z$14,Z15*$D$9,IF($D$18>=$Y$14,Y15*$D$9,IF($D$18>=$X$14,X15*$D$9,IF($D$18>=$W$14,W15*$D$9,IF($D$18>=$V$14,V15*$D$9,IF($D$18>=$U$14,U15*$D$9,IF($D$18>=$T$14,T15*$D$9,
THIS IS WHERE I WOULD RECIEVE MY ERROR...

It is not possible to nest more than seven functions. Usually another approach
works better and is easier to modify and debug.

For example, depending on exactly what you are trying to do, an HLOOKUP
function might be better. One possibility would be:

=HLOOKUP($D$18,$P$14:$AA$15,2)*$D$9

This, of course, depends on the values in row 14 being sorted in ascending
order.

See Help for HLOOKUP for further information.


--ron

 

[Ken Wright]

I am assuming you have data in Row 14 that is increasing in value, eg 1,3,5,7,9 etc and that you
have corresponding values in Row 15. You are looking to compare the data in D18 with that in Row
14, get the closest match in Row 14 that is lower than the value in D18 and then use the
corresponding value in Row 15 multiplied by D9. If so, then assuming the first value in your
liust is in Col V:-

=INDEX($V$15:$AA$15,MATCH($D$18,$V$14:$AA$14))*$D$9

Just change the column reference of V to whatever your first column is (Note there are two
references to that Column and you need to change both).

 

[entreri]

Thank you everyone for your suggestions...I'm am going to try and use
all of your ideas and see how they turn out. If I have any more
problems, I know where to come to.

Kind regards

 

[entreri]

Hello Ken,

I have just tried out the expression you had suggested and it was
brilliant...I have never used the index function, and I now have a
feeling that it will forever make my life much easier. Thanks again
for taking the time to help (and of course to everyone else who took
the time to help as well).

Neil.

 

[Ken Wright]

My pleasure - Are you OK with how it works? as I can always post an explanation if you are not
sure. The help menu on these functions is pretty good, but just post back if you want a hand.

 

[mitch]

You can only nest 7 functions within a function. So after
8 IF statements you have exceeded that limit.

 

[Ken Wright]

If you are using that many IFs in a formula, you will usually find that you can acheive your
objective by using a different function, most notably something along the lines of VLOOKUP /
HLOOKUP / LOOKUP / INDEX & MATCH / OFFSET & MATCH etc.