metrics for bonus calculations

[mewea143]

I am trying to put together a bonus calculator in excel. I cannot figure how to calculate this:

If 70 = 0%, 80 = 100% and 90 is 200%. I need for the numbers between 70 and 90 to be the appropriate %.

Example: 75 is 50% and 85 is 175%.

I have other problems using dollars.

 

[BigBeachBanana]

I am trying to put together a bonus calculator in excel. I cannot figure how to calculate this:

If 70 = 0%, 80 = 100% and 90 is 200%. I need for the numbers between 70 and 90 to be the appropriate %.

Example: 75 is 50% and 85 is 175%.

I have other problems using dollars.

How do you determine the approximate %?
I would think 85 is 150% since it's half-way between 80 and 90 (100% and 200%).
Also, which version of Excel are you using?

 

[mewea143]

You are correct. 85 would be 150%. my apologies. I am using the latest version of Excel. I can complete the formula portion, but I need to get my head around the math.

 

[Otis]

70 = 0%, 80 = 100% and 90 is 200%
… 75 is 50% and 85 is 175%.

Hi mewea. If you're hoping for a single formula, then you'll probably need to accept some minor adjustments to the percents. The reason: it's not likely that a single curve will fit each data point exactly.

I tried a few standard regression types (i.e., the process for fitting data to a curve), and the best result I obtained is through least-squares regression for a cubic polynomial.

y = -(1/30)*x^3 + (47/6)*x^2 – 600*x + 15050

x=70 outputs y=0
x=75 outputs y=76. 6666…
x=80 outputs y=116.6666…
x=85 outputs y=175
x=90 outputs y=200

I have other problems using dollars

Don't we all.

Seriously, though, please feel free to start new threads for new problems. Cheers
[imath]\;[/imath]

 

[Otis]

85 would be 150%. my apologies.

Ah, well that greatly simplifies things. The relationship between x and y is now linear.

y = 10*x – 700
[imath]\;[/imath]

 

[BigBeachBanana]

You are correct. 85 would be 150%. my apologies. I am using the latest version of Excel. I can complete the formula portion, but I need to get my head around the math.

Would you get a number below 70 or more than 90? If so, how would you like those to be handled?