
Nested If Statements  Tough Formula


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Trying to put syntax together below into one formula. Here is my formula but I keep getting an error that I have entered too many arguments for one function
=IF(and(M$2>=$J5,L5=0), $H5*M$1*0.2,if(and(M$2>=$J5,L5>0, (($L5/L$1)*M$1*1.08)=$J5,L5=0), then calculate $H5*M$1*0.2,
also if the below 3 conditions are met (less than g5)
if(and(M$2>=$J5, L5>0, (($L5/L$1)*M$1*1.08)=$J5, L5>0, (($L5/L$1)*M$1*1.08)>$g5)
then show value as: $G5

If none of these are meet which would mean basically is M$2 is
Similar Excel Video Tutorials
More than 7 Nested IF functions
 See what to do if you must have more than 7 nested IF functions: 1) Use the Ampersand, Use different cells, buy Excel 2007, use VLOOKUP, or Use a Bool ...
Common Sized Financial Statements
 Download Excel workbook http://people.highline.edu/mgirvin/ExcelIsFun.htm Learn how to create Common Sized Financial Statements. Highline Co ...
Nested IF & Short Term Loans
 Nested IF example. See how to use the TEXT function, concatenation using the Ampersand (&) symbol and the IF function to create labels and loan ca ...
Similar Topics
I'm working on a formula and I really need to get this to work by end of the day. Here is the formula and below it what I am trying to say:
=IF(and(M$2>=$J5,l5=0), $H5*M$1*0.2,if(and(M$2>=$J5,l5>0, (($L5/L$1)*M$1*1.08)=$J5,l5=0), then calculate $H5*M$1*0.2,
Then if the below 3 conditions are met
if(and( M$2>=$J5, l5>0 , (($L5/L$1)*M$1*1.08) < $g5 )
Calculate this: ($L5/L$1)*M$1*1.08

If these 3 conditions are met
if(and( M$2>=$J5 , l5>0 , (($L5/L$1)*M$1*1.08) > $g5)
then show: $G5

Finally, If none of these above conditions are meet which would mean basically is M$2 is
Hello
I am trying to create a formula that will consider 5 possibilities and give a result accordingly. I want all 5 conditions to be incorporated into one formula. Here are the conditions.
1) IF D1 = "win", & C1 = "BF", then calculate (A1xB1B1) X 0.95
2) IF D1 = "win", & C1 = "BD", then calculate (A1xB1B1) X 0.97
3) IF D1 = "win", & C1 does not read "BF or BD", the calculate (A1xB1B1)
4) IF D1 = "lose", calculate (B1(B1X2))
5) IF D1 = "void", total B1
At present, I have managed to incorporate the first 4 conditions into the formula and it works fine. Here is the formula:
=IF(D1="win",(A1*B1B1)
*IF(C1="BF",0.95,1)*IF(C1="BD",0.97,1),IF(D1="lose",B1(B1*2),""))
How can I add the 5th condition into the formula. Each time I try it tells me that I have too many arguments in the formula or the parenthesis is incorrect.
Dan Wright
I was wondering if there's a function similar to switch statements in C++ / Java? For example, if I want to create an if statement with multiple scenarios, is there an easier way than to use nested IF statements?
The situation I'm trying to model is very simple, I'm just not sure what the excel syntax would be:
Case 1) IF (formula 1) = "NA" AND (formula 2) = "NA" > (formula 3)
Case 2) IF (formula 1) = "NA" AND (forumla 2) != "NA" > (formula 2)
ELSE > (formula 1)
Any help here would be much appreciated.
Hello
I am trying to create a formula that will consider 5 possibilities and give a result accordingly. I want all 5 conditions to be incorporated into one formula. Here are the conditions.
1) IF D1 = "win", & C1 = "BF", then calculate (A1xB1B1) X 0.95
2) IF D1 = "win", & C1 = "BD", then calculate (A1xB1B1) X 0.97
3) IF D1 = "win", & C1 does not read "BF or BD", the calculate (A1xB1B1)
4) IF D1 = "lose", calculate (B1(B1X2))
5) IF D1 = "void", total B1
At present, I have managed to incorporate the first 4 conditions into the formula and it works fine. Here is the formula:
=IF(D1="win",(A1*B1B1)
*IF(C1="BF",0.95,1)*IF(C1="BD",0.97,1),IF(D1="lose",B1(B1*2),""))
How can I add the 5th condition into the formula. Each time I try it tells me that I have too many arguments in the formula or the parenthesis is incorrect.
Dan Wright
I am struggling with a formula with a 3 x nested IF statements. The error is "Too many arguments" when there are only 3.
I might have a bracket wrong, but doesn't appear to be.
Help please.
Formula is: IF(AND(I6=0,$CW$6>$b6),$CS6,IF(AND(I6=0,$CW$6$b6,((H6*$CS6)+(I6*$CT6))/(H6+I6)),((H6*$CQ6)+(I6*$CR6))/(H6+H6)))
Thanks
Hi,
In a VBA If statement it would be so easy, I know exactly what I want, I just cant get the syntax right in a formula using a nested IF statement.
What I want is a formula in Cell A3 that basically does the below logic all in one formula (nested IF I guess?)...
IF A1 > 21 AND A2 < 21 THEN RETURN 21
IF A1 > 40 AND A2 < 40 THEN RETURN 40
IF A1 > 55 AND A2 < 55 THEN RETURN 55
IF A1 > 64 AND A2 < 64 THEN RETURN 64
I am getting stuck over and over, I just can't get my head around this many arguments!
HELP!
Thanks in advance
Alan
Hello
I am trying to create a formula that will consider 3 possibilities and give an answer accordingly. I want all 3 conditions to be incorporated into one formula. At present, I can only make the expressions work on their own in individual cells.
1) IF D1 = "win", & C1 = "BF", then calculate (A1xB1B1) X 0.95
2) IF D1 = "win", & C1 does not read "BF", the calculate (A1xB1B1)
3) IF D1 = "lose", calculate (B1(B1X2))
Whenever I try to express these three arguments in one formula, Excel tells me that the formula has a error. Thanks.
Dan
I am trying to construct a formula using If and AND function together and needs help with the same.
I want to say IFA5="NO", AND A7>0 and A8>0, then output "Check", Otherwise leave it blank
A5, A7, and A8 also have a If formula nested in them.
It sounds simple and I have done couple of arguments but I am only getting error.
Here are my try's
=IF(A5)="NO",AND(A7>0,A8>0),"CHECK","" I got errors on this formula
OR
=IF(AND(A5="NO",(A7>0,A8>0)),"CHECK","")
This formula said CHECK even when A5 is not equal to NO, said TRUE when both the conditions were true(but I want it to say check instead of true)and said FALSE when IF condition met but AND condition did not meet(I want it to leave the cell blank instead of calling it false.
What am I doing wrong and how can I fix it? Please Help!
Hi there,
I have to write a long if statements with a few conditions in it.
I tried a few variations but sometimes it does not return what i expect it to, in others Excel says missing parenthasis and in others it says error in formula.
=IF(D12="PL",0,IF(AND(D12="FL",E12=2,IF(OR(J12=1,J12=2),35,20)),0)))))
Basically there are main conditions PL, FL, NL.
Its the additional restrictions on the FL and NL that are causing problems i think.
If there is FL in D12 and E12 >=1 and J12=1 or J12=2 then i need to return 35, otherwise 20. same for the rest of the parts in red.
I would appreciate any comments.
TYIA.
I need to display the first, second and third in an array of percentages. I
tried using the 'Statistical Function' 'Large', first by using 'Insert
Function', selecting 'Large' then entering the Array [F11:F100], then the
ordervalue K [3]. The FormulaBar displayed =LARGE(F11:F100 3)and when I
pressed Enter it returned an errorwindow, stating 'too few arguments' were
entered. I then tried entering the Formula directly [using the syntax
suggested in 'Help' (by copy and paste)]. The FormulaBar displayed
=LARGE(F11:F100,3)and when I pressed Enter it again returned an errorwindow,
stating 'too few arguments' were entered. Neither of these [very similar]
syntaxes worked! Please help!
I have had a lengthy hiatus from daily excel work and am having a problem with an IF statement. I am trying to create a pricing grid and use IF and AND statements as conditions for applying certain rates or using certain elements to calculate price. The grid is pricing something that has three sizes. My problems are with the fabric yardage formula. When I use it for just one size it works fine.
=IF(AND(A41=$G$8,D41=$I$15),$H$24+$K$24,$H$24)
But when I try to add the two additional sizes to this formula I get a #VALUE error.
=IF(AND(A41=$G$8,D41=$I$15),$H$24+$K$24,$H$24),IF(AND(A41=$G$9,D41=$I$15),$H$25+$K$25,$H$25),IF(AND( A41=$G$10,D41=$I$15),$H$26+$K$26,$H$26)
I tried adding parentheses and it just tells me I have an error and won't calculate. It's probably simple, but I cannot for the life of me figure out what it is. I uploaded sample file.
Many Thanks
Hello, I am in need of a method for extracting the arguments from a formula entered into an excel cell.
For example, I have a UDF "boswell(arg1, arg2, arg3)". The arguments are of various types; double, string, etc. and the arguments can be entered as values, or as addresses to cells containing the desired values (like typical formulas). I would like to create a function or macro that can return the values of the arguments.
Further Example:
function input: cell(i,j).formula or "=boswell(10, C3, "Risk 1" )" ;
output: the array {10, 5, "Risk 1"} where 5 is the value entered in cell C3.
Thank You in Advance for any help.
I have a formula that continues to return the same error message  "You've entered too many arguments for this function"
I cannot understand what is wrong. The formula captures 15 cells (A2 through O2). All of the cells are "General" in format. Can someone please advise me of my error?
=CONCATENATE("insert into reject_assignment values (",A2,",'",B2,"','",C2,"','",D2,"','",E2,"','",F2,"','",G2,"','",H2,"','",I2,"','",J2,"','",K2,"','",L2,"','",M2,"','",N2,"','",O2,");")
If I remove cell O2, then it works.
I'm currently trying to program several graphs with dynamic axes. Now i know i need to use OFFSET for this but for some reason i'm not getting anywhere. I made the following formula:
=OFFSET(INDIRECT(Berekeningen!C266),0,0,0,COUNTIF(F75:LK75,">1"))
I'm using the indirect function to allow users to have a different starting cell. Changing the function to a normal cellreference has no effect. If i remove the last 2 arguments the formula returns the value of the cell the indirect function is referring to. The COUNTIF function on its own works perfectly. If i look at the formula from the formula box, theres no reference error from one of the individual arguments. So basically all the separate parts work but combined into the OFFSET formula it gives an #REF! error. Can somebody tell me, based on this information, why the formula isn't not working?
Excel help says you can only have 7 nested if statements in a single formula. Someone here once told me that the formula I have below should not be restricted by that rule:
=IF(A3="EUR",A3&"!",IF(A3="GBP",A3&"!",IF(A3="CNY",A3&"!",IF(A3="BRL",A3&"!",IF(A3="USD",A3&"!",IF(A3="JPY",A3&"!",IF(A3="THB",A3&"!",IF(A3="INR",A3&"!","test"))))))))
However I get a "formula error" if I try to add yet another IF statement before the "test".
There are many ways I could get around this, but I'm asking why I can't just add more IF statements here.
Thanks
Excel help says you can only have 7 nested if statements in a single formula. Someone here once told me that the formula I have below should not be restricted by that rule:
=IF(A3="EUR",A3&"!",IF(A3="GBP",A3&"!",IF(A3="CNY",A3&"!",IF(A3="BRL",A3&"!",IF(A3="USD",A3&"!",IF(A3="JPY",A3&"!",IF(A3="THB",A3&"!",IF(A3="INR",A3&"!","test"))))))))
However I get a "formula error" if I try to add yet another IF statement before the "test".
There are many ways I could get around this, but I'm asking why I can't just add more IF statements here.
Thanks
Hi Everyone,
When E35 is 99, this formula works perfectly but anything more than 99 in accordance to the below formula, the value is 0. In short, the formula calculate the arguments in black print but the formulae does not calculate the rest of the arguments in red print.
=SUM(IF($E$35
I want to have IF statements that have multiple conditions. Here is an example of what I want to do:
A1 = 5
A2 = 10
A3 = A2  A1
Now I want to replace A3 with an IF statement that does the following:
If A1 is empty or equal to zero, A3 = " " (blank)
If A2 is empty or equal to zero, A3 = " " (blank)
If both A1 and A2 are empty or equal to zero, A3 = " " (blank)
If both A1 and A2 contain values, A3 = A2  A1
Now I've managed to do this for one cell...like if A1 is empty, etc. with this formula:
IF(A1=0," ",A2A1)
But I can't figure out how to do 2 conditions together. I tried using nested IF statements, and the AND function, but all of those give various problems.
Later on, I'll also have functions I want to do this with that have more than 2 conditions. Can someone show me a way to do this sort of thing for multiple conditions please? Thanks!
Hello,
I have two columns of data in Excel. One stores a month (i.e. January) and the other stores a type of customer transaction (i.e. Workshop). I am trying to put into a single column all the rows that meet the conditions of "January" and the condition of "Workshop". This is my formula:
=SUM(IF($G$39:$G$67="January",IF($E$39:$E$67="Workshop",1,0),0))
When the formula runs I get a "#VALUE!" error from Excel. What am I doing wrong? I got this formula from the Mr. Excel forum but its not working for me.
I am sure this is an easy one for the Pros here. Thank you in advance.
Steve
Hello All,
I trying to program a formula in cell B42, but I'm getting the error message:
"You have entered too many arguments for this function"
I believe there is something with the parenthesis...
Thank you so much,
Cesar
I know there is a maximum of 7 nested IF statements available but I cannot get the following formula to work, even though it only has 7 IF's
Code:
=SUM(IF(Department=J$88,IF(Progress_StatusCNP,IF(Progress_StatusCSO,IF(Progress_StatusNS,IF(Current_Phase=$B91,IF(MID(Progress_Status,1,7)="On Hold",IF(MID(Group_Procurement_Involved?,1,3)="Yes",In_Year_Opportunity__2007,0),0))))/1000)))
CNP, CSO and NS are named cells, all named ranges are the same size and the formula is entered with Ctrl & Shift to make an array formula.
Can anybody explain why this does not work and are there any suggestions how I could improve this formula  I have never cretaed a formula in VBA before but would be willing to try if somebody could give me some basic advice, thanks.
Hi,
I have a list of sales data in column C of which I want to calculate the 40th percentile where certain conditions are met in columns A and B.
I have used the following formula to calculate the answer where I only have two conditions in separate columns which seems to work.
{=PERCENTILE(IF(Data!A:A="u",IF(Data!B:B=590,Data!C:C)),0.4)}
However, I need to add an additional condition to column A which is to include the data in the percentile array if the value is equal to "u" or "h". I have tried a nested IF AND and a nested IF OR but can't seem to get the syntax right. Here's my latest try:
{=PERCENTILE(IF(OR(Data!AW:AW="u",Data!AW:AW="h"),IF(Data!A:A=590,Data!AH:AH)),0.4)}
Can anyone assist?
Cheers,
Tarrant
All, I am new to this forum, and Excel and am hoping that someone can provide some guidance on my below query.
I am attempting to use a nested IF statement to deliver a reslt based on multiple conditions. The first 2 IF statements deliver the correct result, however the third IF statement produces a #VALUE! message. Having played with the syntax for a number of hours I am no where nearer to uncovering the error.
=IF(B22<=5,B22*B17,IF(B22<=10,(5*B17)+(B225)*C17)),IF(B22<=15,((5*B17)+(5*C17)+(B2210))*D17)
Attached is a screenshot that displays what I am attempting.
Any assistance would be greatly appreciated.
Best regards
Hi, I've been having trouble getting this formula to work. I keep getting the "You've entered too many arguments for this function" error.
Here is the formula:
=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))
Hello Bob,
For this formula
=COUNTIF(A2:A$2000)>1
I am getting an error message"You've entered too few arguments for
this function"
Regards,
Jimmy Joseph

