Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Has Too Many Arguments
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,r 40)),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",r40),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”) -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Has Too Many Arguments
See;
http://www.ozgrid.com/Excel/seven-nested.htm and http://www.ozgrid.com/Excel/nested-function-limit.htm -- Regards Dave Hawley www.ozgrid.com "tb" wrote in message ... 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,r 40)),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",r40),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") -- tb |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Has Too Many Arguments
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,R 40)),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",R40),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" wrote in message ... See; http://www.ozgrid.com/Excel/seven-nested.htm and http://www.ozgrid.com/Excel/nested-function-limit.htm -- Regards Dave Hawley www.ozgrid.com "tb" wrote in message ... 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,r 40)),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",r40),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") -- tb |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Has Too Many Arguments
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,R 40)),(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+P 4+AE$6)/5*7+AF$6+AG$6+O4,"N/A"))),IF(AND(M4="IT1L",OR(N4={"ACQ","ERM","MOF","M OR","PRO"},R40)),(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" wrote in message ... 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,R 40)),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",R40),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" wrote in message ... See; http://www.ozgrid.com/Excel/seven-nested.htm and http://www.ozgrid.com/Excel/nested-function-limit.htm -- Regards Dave Hawley www.ozgrid.com "tb" wrote in message ... 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,r 40)),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",r40),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") -- tb |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Has Too Many Arguments
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. -- tb "Luke M" wrote in message ... 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,R 40)),(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+P 4+AE$6)/5*7+AF$6+AG$6+O4,"N/A"))),IF(AND(M4="IT1L",OR(N4={"ACQ","ERM","MOF","M OR","PRO"},R40)),(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" wrote in message ... 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,R 40)),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",R40),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" wrote in message ... See; http://www.ozgrid.com/Excel/seven-nested.htm and http://www.ozgrid.com/Excel/nested-function-limit.htm -- Regards Dave Hawley www.ozgrid.com "tb" wrote in message ... 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,r 40)),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",r40),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") -- tb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum function changes arguments | Excel Worksheet Functions | |||
FUNCTION arguments | Excel Worksheet Functions | |||
function arguments | Excel Discussion (Misc queries) | |||
MIN Function with arguments | Excel Worksheet Functions | |||
IF function with too many arguments | Excel Worksheet Functions |