Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF functions
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(K1 0<"",if(K9<"",if(K8<"",if(K7<"",if(K6<"",if(K 5<"",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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF functions
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 "Chris" wrote in message ... 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(K1 0<"",if(K9<"",if(K8<"",if(K7<"",if(K6<"",if(K 5<"",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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF functions
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 "Chris" wrote in message ... 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(K1 0<"",if(K9<"",if(K8<"",if(K7<"",if(K6<"",if(K 5<"",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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF functions
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 "Chris" wrote: 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(K1 0<"",if(K9<"",if(K8<"",if(K7<"",if(K6<"",if(K 5<"",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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF functions
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 -- Biff Microsoft Excel MVP "pinmaster" wrote in message ... 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 "Chris" wrote: 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(K1 0<"",if(K9<"",if(K8<"",if(K7<"",if(K6<"",if(K 5<"",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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF functions
Yes I see that now, thanks for the correction.
Regards! Jean-Guy "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "pinmaster" wrote in message ... 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 "Chris" wrote: 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(K1 0<"",if(K9<"",if(K8<"",if(K7<"",if(K6<"",if(K 5<"",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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too many nested IF functions! | Excel Worksheet Functions | |||
Nested Functions with OR | Excel Worksheet Functions | |||
Nested functions HELP! | Excel Worksheet Functions | |||
Nested Functions | Excel Worksheet Functions | |||
Nested Functions | Setting up and Configuration of Excel |