Stuck on a formula - working on a comp plan

Robertpetry

New member
Joined
Feb 25, 2013
Messages
7
Reaction score
0
Points
0
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))
 

Attachments

  • Screen Shot 2013-02-25 at 5.03.18 PM.jpg
    Screen Shot 2013-02-25 at 5.03.18 PM.jpg
    100.5 KB · Views: 28
  • Screen Shot 2013-02-25 at 5.03.34 PM.png
    Screen Shot 2013-02-25 at 5.03.34 PM.png
    93.6 KB · Views: 33

Ankit

New member
Joined
Dec 24, 2012
Messages
3
Reaction score
0
Points
0
Location
India
Website
www.exceltrick.com
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))))))
 

Robertpetry

New member
Joined
Feb 25, 2013
Messages
7
Reaction score
0
Points
0
Ankit,

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

Attachments

  • Screen Shot 2013-02-26 at 7.45.39 AM.jpg
    Screen Shot 2013-02-26 at 7.45.39 AM.jpg
    95.7 KB · Views: 19

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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))))))
 

Robertpetry

New member
Joined
Feb 25, 2013
Messages
7
Reaction score
0
Points
0
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!
 

Attachments

  • Comp Plan Analysis.xlsx
    37.4 KB · Views: 18

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
What answer should you be getting?
 

Robertpetry

New member
Joined
Feb 25, 2013
Messages
7
Reaction score
0
Points
0
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!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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?
 

Robertpetry

New member
Joined
Feb 25, 2013
Messages
7
Reaction score
0
Points
0
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.
 

Robertpetry

New member
Joined
Feb 25, 2013
Messages
7
Reaction score
0
Points
0
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))))))
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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 )
 

Robertpetry

New member
Joined
Feb 25, 2013
Messages
7
Reaction score
0
Points
0
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!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top