Problem calculating something in Excel

pts.
Tags:
Data analysis
Data mining/analysis
Development
Microsoft Windows
The production goal for a quarter is 1.474 now if they meet that goal they get 100% of the bonus but if their number is 1.54844 then they get 180% of the bonus and if their number is 1.43036 they only get 50% of the bonus. so if their number is 1.52 they should get approx. 170 % of the bonus. 1.54844 is 105% of 1.4747 and 1.43036 is 97% of 1.4747. My problem is figuring out the bonus when they fall in the middle some were. The most bonus you can get is 180% and the lowest bonus is 50% Any Help would be apriciated

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try the formula in this file. You can test by entering the actual production. This assumes linear increase or decrease based on the numbers you gave. It is assumed that bonus is zero if production is lower than the minimum you gave.
Oops it seems I cannot attach a file, I will send a personal mail

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bvwatson
    I don't understand how you got the 170% bonus on 1.52. I computed 149.44%. I assume that the interval between 1.43036 and 1.474 is not pertinent, since the number (1.52) falls in the next interval between 1.474 and 1.54844. That interval is 0.07444 for an incremental bonus of 80%. Since 1.52 exceeds 1.474 by 0.046, that's 61.79% of the interval; therefore, you get 61.79% of the incremental bonus (80%), or 49.44%. Add that to the 100% bonus, and the total bonus is 149.44%. I've sent you a private message...I can provide an Excel spreadsheet with the calculation. Of course, I'm making some assumptions about the linearity of the intervals. bryan
    0 pointsBadges:
    report
  • Coffey
    If the other responses don't seem to work, send me a e-mail and I'll tell you how to put your entries into a table and then use vlookup to find the values for your percentages. Even if they fall in the middle some place.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following