# Thread: Stuck on a formula - working on a comp plan

1. ## 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?

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))  Reply With Quote

2. 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:

Code:
`=IF(C11<(C5*0.85),0,(IF(AND(C11>=(C5*0.85),(C11<(C5*0.9))),C5*0.005),IF(AND(C11>=(C5*0.9),(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))))))`  Reply With Quote

3. Ankit,

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

4. 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))))))  Reply With Quote

5. 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!  Reply With Quote

6. What answer should you be getting?  Reply With Quote

7. 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!  Reply With Quote

8. 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?  Reply With Quote

9. 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.  Reply With Quote

10. 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))))))  Reply With Quote

formulas 