Nested IF statement error

[Ethan 007]

Hi All,

I was just wondering if you guys could help me with something, i am creating a calculator like excel program, concept behind it is that if the unit is say "m2"(which is entered into a cell) then the formula will calculate something a differant way then if the unit was "m3",

so basically, after entering the first 4 if statements and closing the formula, it worked(unit,m2,m3,m) but when i entered the last unit("100m")(which basically rounds a number to the nearest 100m with the "Ceiling" function, i get a "You have entered too many arguments for this function" error.

Here is the code i used.

IF(E15="M2",$E$5*(1+J15),IF(E15="M3",G15*E5*(1+J15),IF(E15="M",$E$6*(1+J15),IF(E15="unit",$E$7*1+J15),if(E15="100m",ceiling(H15,100)))))

your help & support will be greatly appreciated =)

[AliGW]

Welcome to the forum.

You seem to be missing a final bracket (and a final value_if_false):

IF(E15="M2",$E$5*(1+J15),IF(E15="M3",G15*E5*(1+J15),IF(E15="M",$E$6*(1+J15),IF(E15="unit",$E$7*1+J15),if(E15="100m",ceiling(H15,100),value_if_false)))))

[etaf]

in addition to above
you missed a bracket , so the calc will not be as expected - when E15 = "UNIT"

IF(E15="M2",$E$5*(1+J15),IF(E15="M3",G15*E5*(1+J15),IF(E15="M",$E$6*(1+J15),IF(E15="unit",$E$7* (
1+J15),if(E15="100m",ceiling(H15,100)))))


also a False condition - so if none apply - what should go into the cell

[Ethan 007]

Hi Ali,

Thanks for your quick reply, i think i added the "0" and received the same error.

=IF(E15="M2",$E$5*(1+J15),IF(E15="M3",G15*E5*(1+J15),IF(E15="M",$E$6*(1+J15),IF(E15="unit",$E$7*1+J15),if(E15="100m",ceiling(H15,100),0)))))

[etaf]

see my post - missing a bracket

=IF(E15="M2",$E$5*(1+J15),IF(E15="M3",G15*E5*(1+J15),IF(E15="M",$E$6*(1+J15),IF(E15="unit",$E$7*(1+J15),IF(E15="100m",CEILING(H15,100),"no entry")))))

=IF(E15="M2",$E$5*(1+J15),IF(E15="M3",G15*E5*(1+J15),IF(E15="M",$E$6*(1+J15),IF(E15="unit",$E$7*(1+J15),IF(E15="100m",CEILING(H15,100),0)))))

[Ethan 007]

Hi Guys,

Thanks a lot,i really appreciate the help and speedy replies =)

i will continue feathering my nest =)

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Ethan 007]

Hi All,

I was expanding on the previously enquired Nested If statement and i got the same error.


The code is here:

=IF(F15="M2",F5*(1+N15),IF(F15="M3",H15*F5*(1+N15),IF(F15="M",J15*(1+N15),IF(F15="unit",J15*(1+N15),IF(F15="100m",CEILING(M15,100),IF(F15="50m",CEILING(M15,100),IF(F15="Surface Area",L15*(1+N15),IF(F15="DAY",I15),IF(F15="ITEM",I15*(1+N15),0)))))))))

How should i retype this formula to make it work?

Your guidance would be greatly appreciated =)


***Appoligies for not closing the forum when my original question was answered, i knew i might have more questions of the same nature.

[etaf]

you have a bracket in wrong place
=IF(F15="M2",F5*(1+N15),IF(F15="M3",H15*F5*(1+N15),IF(F15="M",J15*(1+N15),IF(F15="unit",J15*(1+N15),IF(F15="100m",CEILING(M15,100),IF(F15="50m",CEILING(M15,100),IF(F15="Surface Area",L15*(1+N15),IF(F15="DAY",I15),IF(F15="ITEM",I15*(1+N15),0)))))))))

try
=IF(F15="M2",F5*(1+N15),
IF(F15="M3",H15*F5*(1+N15),
IF(F15="M",J15*(1+N15),
IF(F15="unit",J15*(1+N15),
IF(F15="100m",CEILING(M15,100),
IF(F15="50m",CEILING(M15,100),
IF(F15="Surface Area",L15*(1+N15),
IF(F15="DAY",I15,
IF(F15="ITEM",I15*(1+N15),0)))))))))

[AliGW]

You need to pay attention to your parenthesis as I said before.

You need this:

=IF(F15="M2",F5*(1+N15),IF(F15="M3",H15*F5*(1+N15),IF(F15="M",J15*(1+N15),IF(F15="unit",J15*(1+N15),IF(F15="100m",CEILING(M15,100),IF(F15="50m",CEILING(M15,100),IF(F15="Surface Area",L15*(1+N15),IF(F15="DAY",I15,IF(F15="ITEM",I15*(1+N15),0)))))))))

This is WRONG:

IF(F15="DAY",I15),IF(F15="I

[AskMeAboutExcel]

I think the issue is in the IF(F15="DAY",I15) part - if I've understood the formula correctly there shouldn't be a bracket after I15.

Your formula would then be

Please Login or Register  to view this content.

For complex nested IFs, it can be useful to use a tool like https://www.excelformulabeautifier.com/ to respace the formula in a more readable format. E.g. using the formula above comes out as below, which is easier to follow

Please Login or Register  to view this content.

[Ethan 007]

Thanks a million =)
i will try and code it like that from now on

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[etaf]

try setting out as i have done in something like a note pad or some simple text editor
make it much easier to see errors like that
and work out any brackets that are needed or missing

much much easier to see then in excel formula bar - although often excel will give a hint as to where the issue maybe