I am trying to develop a tiered commission plan. My Excel formula returns a #VALUE! in the cell. Thoughts?

Question

My formula is for total annual sales, where cell B3 is 2510000 and the formula is =IF(B3>2500000,((B3-500000)*0.11)+(500000*0.1)+(500000*0.09)+(300000*0.08)+(300000*0.07)+(600000*0.05)+(300000*0.04)),IF(B3>2000000,((B3-2000000)*0.1)+(500000*0.09)+(300000*0.08)+(300000*0.07)+(600000*0.05)+(300000*0.04)),IF(B3>1500000,((B3-1500000)*0.09)+(300000*0.08)+(300000*0.07)+(600000*0.05)+(300000*0.04)),IF(B3>1200000,((B3-1200000)*0.08)+(300000*0.07)+(600000*0.05)+(300000*0.04)),IF(B3>900000,((B3-900000)*0.07)+(600000*0.05)+(300000*0.04)),IF(B3>600000,((B3-600000)*0.05)+(300000*0.04)). I have minimized my parentheses to get rid of the formula errors, but now #VALUE!

Answer 1

You formula is overly complex when it doesn't need to be. Try to simplify the formula first.

For example, you have 5 "IF" conditions. Just looking at the first IF condition, you have the following calculation when TRUE:

(( B3-500000) *0.11) +(500000 *0.1)+ (500000 *0.09) +(300000 *0.08) +(300000 *0.07) +(600000 *0.05) +(300000 *0.04))

You would get the exact same result using this calculation when TRUE:

(B3-500000) *0.11) +182000)

My point is this; simplify your calculations first. It will then become far easier to pinpoint cause of #VALUE error. All I did was perform the static calculations (the ones where numbers never change) and came up with 182000. Do the same for each of your IF conditions.

You find out you formula is just too long or something simple like missing a parentheses

Hope this helps!
Kind Regards,
QwertyKPH @ Yahoo

Answer 2

there is not any thank you to question a cellular to envision if it features a formulation, i.e. you won't be able to write an IF formulation that assessments if yet another cellular features a formulation. regrettably Excel looks on the cost in a cellular fairly than the underlying formulation, while suitable. the closest element you're able to do is Edit | flow to, then click on particular, then %. formulation. you additionally can press F5 or CTRL+G to get admission to the flow to verbal substitute. If Edit | flow to isn't proper, then a macro (in VBA) ought to do it utilising the Hasformula factors yet i will supply no extra help on that provided that i do no longer write VBA.

Answer 3

Your formula is wrong, try building simple if constructs first
if(a1>1000,true,false)
if(a1>1000,true,if(a>500,true,false))
adding complexity along the way.

Answer 4

try making the column wider. also, you are missing 2 ')'s