Formula for figuring a multiplier in a range

[Doc_H]

Hi all, I'm new to the forum and I'm thankful for it as it is a great source of knowledge.

Here's my dilemma:
I am in need of a formula that will figure out a multiplier number from a range of numbers. Here's the example I'm working with. This will be used in a worksheet to measure performance and incentive pay for my group and below is a sample of one of the many goals for the group. Incentive will be calculated on a point system. The multiplier is based on the "Actual Result" or performance of the individual. With that said, if the value falls between a range within say, the threshold rate of 1.75% and the target rate of 1.25%, what formula can assist in figuring out the actual multiplier?


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Available
Points[/TD]
[TD][/TD]
[TD](Multiplier)[/TD]
[TD]Targets[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"]ACTUAL RESULT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]POINTS
EARNED[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD][/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]Threshold[/TD]
[TD="align: center"]1.75%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.36%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]=Multiplier X Available Points[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]Target[/TD]
[TD="align: center"]1.25%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Multiplier[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]200%[/TD]
[TD]Maximum[/TD]
[TD]0.75%[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any assistance is greatly appreciated!

Thank you!

 

[Scott Huish]

=A2*LOOKUP(B2,{0.0075,0.0125,0.0175},{2,1,0.5})

Excel Workbook
	A	B	C
1	Available Points	Actual Result	Points Earned
2	15	1.36%	15
Sheet1

 

[Doc_H]

Thanks for the response, HOTPEPPER. After inputting the formula into my spreadsheet, it is calculating the multiplier as 1500.00%. I'm probably missing something but can't figure out what it is.

Thanks again!

Doc_H

=A2*LOOKUP(B2,{0.0075,0.0125,0.0175},{2,1,0.5})

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Scott Huish]

My formula is not returning the multiplier, it is returning the Points Earned, per your example (Multiplier x Available Points)

 

[Doc_H]

Thanks for clearing that up. I entered it into the Points Earned field and it assumed a multiplier of 1x or 100% based on a result of 1.36%.

Is there a way to get a more accurate multiplier or "points earned" result based on the actual result percentage? For instance, if the actual result were 1.70%, which is closer to threshold, the multiplier in that case would be closer to 50% or 7.5 points but if the multiplier were 56% then the points would increase to 8.4. In essence, I'd like to multiplier to be anywhere in the range of 50% and 200% and not necessarily 50%, 100%, and 200%. I hope i'm not convoluting this response too much.

 

[shg]

Maybe ...

       --A-- -B-- ---------------------------C----------------------------
   1   1.75%  50%                                                         
   2   1.25% 100%                                                         
   3   0.75% 200%                                                         
   4                                                                      
   5   0.75% 200% B5: =PERCENTILE($B$1:$B$3, 1-PERCENTRANK($A$1:$A$3, A5))
   6   0.85% 180%                                                         
   7   0.95% 160%                                                         
   8   1.05% 140%                                                         
   9   1.15% 120%                                                         
  10   1.25% 100%                                                         
  11   1.35%  90%                                                         
  12   1.45%  80%                                                         
  13   1.55%  70%                                                         
  14   1.65%  60%                                                         
  15   1.75%  50%

 

[Doc_H]

Thanks, shg. This is really good stuff. Do I need to input the code using VBA or can I use the formula directly?

 

[shg]

It's just a formula.

 

[Doc_H]

I tried the formula but didn't have much luck. =PERCENTRANK(ARRAY,X,SIGNIFICANCE) gives the right idea but the formula assumes that 1.75% is 100%, 1.25% is 50%, and 0.75% is 0%. Any thoughts?

Thx

 

[shg]

That's the table in A1:B3.