Stuck on a formula - working on a comp plan

Hi all,

I am building a "simple" spreadsheet to use when showing off a compensation plan. I want to be able to quickly adjust the attainment and quota numbers and see the results. Attached is a screen shot of the comp plan and also a screen shot of the sheet and formula I am stuck on.

I am a real novice on formulas so I am not sure what I did wrong. I am assuming I can nest IF and AND formulas? Maybe my parentheses are messed up?

Thanks for any help you can provide!

Robertpetry

This is the formula as it is in the sheet right now. Excel says it is incorrect and won't let me save it:

=if(C11<(C5*.85),0,(if(C11>=(C5*.85)and(C11<(C5*.9),C5*.005),if(C11>=(C5*.9)and(C11>(C5*.95),C11*.006),If(C11>=(C5*.95)and(C11<C5),C11*.007,if(C11>=C5)and(C11<(C5*1.05),(C11*.01),If(C11>=(C5*1.05)and(C5<(C5*1.1),(C11*.015),(C11*.02))

Hi Robert, What I suspect the main problem in your formula is that you are using 'and' as an operator but actually it’s a function in excel.
I have made some changes in the formula to make it syntactically correct. Please see if it works:

Ankit,

Thank you very much. So close! But now I get a value error. I don't understand this error. Any ideas?

Try

=IF(C11<C5*0.85,0,
IF(C11<C5*0.9,C5*0.005,
IF(C11>C5*0.95,C11*0.006,
IF(AND(C11>=C5*0.95,C11<C5),C11*0.007,
IF(AND(C11>=C5,C11<C5*1.05),C11*0.01,
IF(AND(C11>=C5*1.05,C5<C5*1.1),C11*0.015,C11*0.02))))))

So Close, but I am getting the wrong answer. Also, I was wrong in my original formula - I want to calculate the override rate in that formula, not the actual commission. I fixed that, but still getting the wrong answer.

I have attached the spreadsheet for you to look at. Thanks all!

What answer should you be getting?

Hi Bob,

I want it to show the payout percentage so I can then multiply that times the attained revenue (C11) and get the correct payment in cell C18. The payout schedule is <85%=0, 85%=.5%, 90%=.6%, 95%=.7%, 100%=1%, 105%=1.5%, and 110% or more is 2% payout.

Thanks!

Why are you testing C11 against C5 times some multiple some times, other times you are testing multiplying C11 against some multiple, and yet other times against C5 time some multiple AND against C5 alone? That is not what you state in the last post, so which is it?

Wow Bob, I don't know. I can't follow you post and am so lost versions of this formula that I don't know what the answer is.

Maybe I should start from scratch. What I need to do is have a formula that looks at the Attainment (C11) and compares it to Quota (C5) and comes up with commission multiplier as outlined 2 posts above. Then I can multiply the Attainment (C11) by the multiplier (G18) to come up with the commission number (C18).

Does that make sense?

Thanks again. Sorry to be confused.

I think this may be close but I am still getting an error in Excel

=IF(C11<(C5*0.85),0,IF(C11<=(C5*0.9),0.005,IF(C11<=(C5*0.95),0.006,IF(C11<=C5),0.007,IF(C11<=(C5*1.05),0.01,IF(C5<=(C5*1.1),0.015,0.02))))))

How about this

=VLOOKUP(C5/C11,{0,0;0.85,0.005;0.9,0.006;0.95,0.007;1,0.01;1.05,0.015;1.1,0.02},2,TRUE )

Sooooo close! You did it except you needed to have C11/C5 not C5/C11!

Once I did that the formula worked great. I don't understand the VLOOKUP function but I will try to read up on it. I never would have thought of that.

Thank you very much for your help!

The bit in curly brackets is essentially a two-column table, the lower threshold in column 1, the percentage multiple in column 2 (unfortunately you cannot use percentages such as 85%, you have to use fractions of 1), and the VLOOKUP just looks up the result of C11/C5 in column 1, and returns the equivalent value in column 2.