Formula Syntax Query - Excell 2010

**Matt_Cox** · 07-29-2010, 06:04 AM

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)+(B22-5)*C17)),IF(B22<=15,((5*B17)+(5*C17)+(B22-10))*D17)

Attached is a screenshot that displays what I am attempting.

Any assistance would be greatly appreciated.

Best regards

**martindwilson** · 07-29-2010, 06:33 AM

maybe
=IF(B22<=5,B22*B17,IF(B22<=10,((5*B17)+(B22-5))*C17,IF(B22<=15,((5*B17)+(5*C17)+(B22-10))*D17,"something")))

fyi its easier to check if you split it into its components
=IF(B22<=5,B22*B17,
IF(B22<=10,(5*B17)+(B22-5)*C17)),
IF(B22<=15,((5*B17)+(5*C17)+(B22-10))*D17
then you can see
IF(B22<=10,(5*B17)+(B22-5)*C17)), is not quite right
it needs to be IF(B22<=10,(5*B17)+(B22-5)*C17,
or perhaps
IF(B22<=10,((5*B17)+(B22-5))*C17),
but i dont know which you actually want
also you have not specified what to do if all 3 conditions not met

**teylyn** · 07-29-2010, 06:37 AM

Instead of a screenshot, you should have posted the Excel sheet (replace confidential info with dummy data)

counting the brackets in your formula, I think you are closing some IFs too early. Try

=IF(B22<=5,B22*B17,IF(B22<=10,(5*B17)+(B22-5)*C17,IF(B22<=15,((5*B17)+(5*C17)+(B22-10))*D17)))

Your last nested if does not have a statement for False. You may want to do something about that.

**teylyn** · 07-29-2010, 06:38 AM

ah, Martin, you in the morning, me at night, but the same gist!

**DonkeyOte** · 07-29-2010, 06:39 AM

The parentheses are still out of kilter if this is a tiered calculation - only B22-10 should be multiplied by D17.

If preferred you can conduct tiered commission calcs with SUMPRODUCT - see: http://www.mcgimpsey.com/excel/variablerate.html

**DonkeyOte** · 07-29-2010, 06:50 AM

For sake of SUMPRODUCT example - see attached
(add cap if nec.)

**martindwilson** · 07-29-2010, 06:55 AM

hmm run before walk springs to mind!

**DonkeyOte** · 07-29-2010, 07:45 AM

Perhaps though personally I would argue that when it comes to tiered calculations the SUMPRODUCT approach is generally the simplest to manage and adapt long term.
The greater the number of tiers the more sense it makes to use SUMPRODUCT.