Function Has Too Many Arguments

  • Thread starter Thread starter tb
  • Start date Start date
T

tb

I am using Excel 2007 for Windows.

Excel complains that one of the functions in the following formula has too
many arguments. I am not quite sure which function is the problem... Any
suggestions on how to fix this?
Thanks.

=if(m4="IT1P",if(or(or(t4=40,n4="KAN"),and(t4=52,r4>0)),ac$6/5*7+ad$6/5*7+ae$6/5*7+af$6+ag$6,if(t4=52,if(q4="E",ac$6/5*7+ad$6/5*7+s4+ae$6/5*7+af$6+ag$6,if(q4="F",ac$6/5*7+ad$6/5*7+o4+p4/5*7+ae$6/5*7+af$6+ag$6,"N/A")),"N/A"),"N/A"),if(m4="IT1L",if(or(n4="ACQ",n4="ERM",n4="MOF",n4="MOR",n4="PRO",r4>0),ac$4/5*7+ad$4/5*7+ae$4/5*7+af$4+ag$4,if(q4="E",ac$4/5*7+ad$4/5*7+s4+ae$4/5*7+af$4+ag$4,if(q4="F",ac$4/5*7+ad$4/5*7+o4+p4/5*7+ae$4/5*7+af$6+ag$6,"N/A"))),”N/A”),"N/A”)
 
OP is not exceeding the nested limit, they truly have too many arguments.

Corrected formula:
=IF(M4="IT1P",IF(OR(OR(T4=40,N4="KAN"),AND(T4=52,R4>0)),AC$6/5*7+AD$6/5*7+AE$6/5*7+AF$6+AG$6,IF(T4=52,IF(Q4="E",AC$6/5*7+AD$6/5*7+S4+AE$6/5*7+AF$6+AG$6,IF(Q4="F",AC$6/5*7+AD$6/5*7+O4+P4/5*7+AE$6/5*7+AF$6+AG$6,"N/A")),"N/A")),IF(M4="IT1L",IF(OR(N4="ACQ",N4="ERM",N4="MOF",N4="MOR",N4="PRO",R4>0),AC$4/5*7+AD$4/5*7+AE$4/5*7+AF$4+AG$4,IF(Q4="E",AC$4/5*7+AD$4/5*7+S4+AE$4/5*7+AF$4+AG$4,IF(Q4="F",AC$4/5*7+AD$4/5*7+O4+P4/5*7+AE$4/5*7+AF$6+AG$6,"N/A"))),"N/A"))

You had an extra "N/A" arguement at the end of your sets. A few comments,
the math operation of "AC$4/5*7+AD$4/5*7+O4+P4/5*7+AE$4/5*7" can be
simplified to "(AC$4+AD$4+P4+AE$4)/5*7+O4" which makes debugging a little
easier. Also, you'll notice that there are several IF functions that have
"N/A" as a possible outcome. In a pure logic tree, each output need only be
listed once (usually through the use of AND and OR) However, since the
function "appears" to be working, it may be "good enough" as is.
--
Best Regards,

Luke M
ozgrid.com said:
See;
http://www.ozgrid.com/Excel/seven-nested.htm
and
http://www.ozgrid.com/Excel/nested-function-limit.htm
Click to expand...
 
An example of formula with reduced math operations and fewer nested
functions:

=IF(M4="IT1P",IF(OR(OR(T4=40,N4="KAN"),AND(T4=52,R4>0)),(AC$6+AD$6+AE$6)/5*7+AF$6+AG$6,IF(AND(T4=52,Q4="E"),(AC$6+AD$6
+AE$6)/5*7+AF$6+AG$6+S4,IF(AND(T4=52,Q4="F"),(AC$6+AD$6+P4+AE$6)/5*7+AF$6+AG$6+O4,"N/A"))),IF(AND(M4="IT1L",OR(N4={"ACQ","ERM","MOF","MOR","PRO"},R4>0)),(AC$4+AD$4+AE$4)/5*7+AF$4+AG$4,IF(AND(M4="IT1L",Q4="E"),(AC$4+AD$4
+AE$4)/5*7+AF$4+AG$4+S4,IF(AND(M4="IT1L",Q4="F"),(AC$4+AD$4+P4+AE$4)/5*7+AF$6+AG$6+O4,"N/A"))))

--
Best Regards,

Luke M
Luke M said:
OP is not exceeding the nested limit, they truly have too many arguments.

Corrected formula:
=IF(M4="IT1P",IF(OR(OR(T4=40,N4="KAN"),AND(T4=52,R4>0)),AC$6/5*7+AD$6/5*7+AE$6/5*7+AF$6+AG$6,IF(T4=52,IF(Q4="E",AC$6/5*7+AD$6/5*7+S4+AE$6/5*7+AF$6+AG$6,IF(Q4="F",AC$6/5*7+AD$6/5*7+O4+P4/5*7+AE$6/5*7+AF$6+AG$6,"N/A")),"N/A")),IF(M4="IT1L",IF(OR(N4="ACQ",N4="ERM",N4="MOF",N4="MOR",N4="PRO",R4>0),AC$4/5*7+AD$4/5*7+AE$4/5*7+AF$4+AG$4,IF(Q4="E",AC$4/5*7+AD$4/5*7+S4+AE$4/5*7+AF$4+AG$4,IF(Q4="F",AC$4/5*7+AD$4/5*7+O4+P4/5*7+AE$4/5*7+AF$6+AG$6,"N/A"))),"N/A"))

You had an extra "N/A" arguement at the end of your sets. A few comments,
the math operation of "AC$4/5*7+AD$4/5*7+O4+P4/5*7+AE$4/5*7" can be
simplified to "(AC$4+AD$4+P4+AE$4)/5*7+O4" which makes debugging a little
easier. Also, you'll notice that there are several IF functions that have
"N/A" as a possible outcome. In a pure logic tree, each output need only
be listed once (usually through the use of AND and OR) However, since the
function "appears" to be working, it may be "good enough" as is.
Click to expand...
 
Your formula works perfectly! Thank you, Luke M., for the solution and for
having made the extra effort to optimize the formula. I really appreciate
it.
Best regards.
 
Back
Top