Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

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

  1. #1

    Question Stuck on a formula - working on a comp plan



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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))
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screen Shot 2013-02-25 at 5.03.18 PM.jpg 
Views:	21 
Size:	100.5 KB 
ID:	1156   Click image for larger version. 

Name:	Screen Shot 2013-02-25 at 5.03.34 PM.png 
Views:	25 
Size:	93.6 KB 
ID:	1157  

  2. #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))))))
    Thanks,
    Ankit Kaul

  3. #3
    Ankit,

    Thank you very much. So close! But now I get a value error. I don't understand this error. Any ideas?
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screen Shot 2013-02-26 at 7.45.39 AM.jpg 
Views:	15 
Size:	95.7 KB 
ID:	1158  

  4. #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))))))

  5. #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!
    Attached Files Attached Files

  6. #6
    What answer should you be getting?

  7. #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!

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

  9. #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.

  10. #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))))))

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •