Nested IF functions

[Chris]

Excel 2007 claims to allow up to 64 nested functions, but I am getting an
error in a spread sheet with much less than that (but more than the 10
previously allowed). Excel is indicating that I must use a different file
format, but it is not clear which one I must use - binary, "default" (which I
have tried), macro enabled, etc. Which one of these is the 2007 format that
will allow more nested functions?

The function I am trying to nest is:

=IF(K14<>"",if(K13<>"",if(K12<>"",if(K11<>"",if(K10<>"",if(K9<>"",if(K8<>"",if(K7<>"",if(K6<>"",if(K5<>"",if(K4<>"",((l3-k3)*.05),((l4-k4)*.05),((l5-k5)*.05),((l6-k6)*.05),((l7-k7)*.05),((l8-k8)*.05),((l9-k9)*.05),((l10-k10)*.05),((l11-k11)*.05,((l12-k12)*.05,((l13-k13)*.05),((l14-k14)*.05),0)

Perhaps this would be better served as an array function?

Regards,

Chris

 

[T. Valko]

I don't have Excel 2007 so I'm trying to figure this out with the nested
limit of previous versions.

Hard to figure out what you're trying to do with this. You don't need to use
this on every separate condition: *0.05. You can put that outside of the
formula and it will still do the same thing.

=IF(...IF(...IF)...)))*0.05

If all the cells in the range K4:K14 <>"" then subtract K3 from I3. If any
cell (other than K14, if K14 ="" then 0) is "" then subtract that cell from
the corresponding cell in range I. That's what your formula would do if it
was working but that doesn't make any sense (to me).

Perhaps you should explain it. I'm sure a better formula can be written to
do what you want.

--
Biff
Microsoft Excel MVP


news[email protected]...

 

[Roger Govier]

Hi Chris

I don't think the problem is with the number of nested functions (you are
using 11), it is the number of arguments to IF that is the problem.

You have all of the tests, followed by 11 possible outcomes.
IF only has 2 outcomes, value if True, value if False.

If the format were
=IF(K14<>"",(K14-L14)*.05,IF(K13<>"",(K13-L13)*.05,0))
then it would work.
Obviously you could have all 11 of your tests, not just the 2 I have typed
above.

What if all of the K values are <>"", what result do you want?
In the above, the test will start at K14 and work upward, and the first
value found to be <>"" will have the result.
Is that what you want?

If not, post back with what you are trying to achieve, and there may be
better ways to obtain the result.
--

Regards
Roger Govier

news[email protected]...

 

[pinmaster]

Hi,

Looks like your trying to substract the last value in K3:K14 from the
matching value in I3:I14 and multiply the result by .05, if that is the case
them try this instead:

=(INDEX(I3:I14,MATCH(LOOKUP(9E+300,K3:K14),K3:K14,0))-LOOKUP(9E+300,K3:K14))*0.05

HTH
Jean-Guy

 

[T. Valko]

If there are duplicate max values in K that will return an incorrect result.

Also, the LOOKUP in MATCH is redundant. Try it like this:

=(INDEX(I3:I14,MATCH(1E10,K3:K14))-LOOKUP(1E10,K3:K14))*0.05

 

[Jean-Guy]

Yes I see that now, thanks for the correction.

Regards!
Jean-Guy