lookup values within a range and calculate new value

[NabMo]

Please see ref table below which has a multiplier per percentage range, which means that based on what range your percentage falls in, it should return the multiplier however it should also add 0.01 increments for every percentage difference.

[TABLE="width: 302"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 2"][TABLE="class: grid"]
<tbody>[TR]
[TD]Percentage Min[/TD]
[TD]Percentage Max[/TD]
[TD]Multiplier[/TD]
[TD]Increment per 1%[/TD]
[/TR]
[TR]
[TD]0.00%[/TD]
[TD]79.99%[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80.00%[/TD]
[TD]91.99%[/TD]
[TD]1[/TD]
[TD]0.01[/TD]
[/TR]
[TR]
[TD]92.00%[/TD]
[TD]100%[/TD]
[TD]1.2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As an example, if i have 82%, then multiplier would be 1.02 (1 for 80% + 0.02 for extra 2%)
likewise 95% would result in a multiplier of 1.23 (1.3 for 92% + 0.03 for extra 3%)

Please help with a formula.

Thank you in advance.

NabMo,
Cape Town

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Percentage Min​

[/td][td]

Percentage Max​

[/td][td]

Multiplier​

[/td][td]

Increment per 1%​

[/td][td][/td][td]

Percentage​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

0,00%​

[/td][td]

79,99%​

[/td][td]

0​

[/td][td][/td][td][/td][td]

82%​

[/td][td]

1,02​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

80,00%​

[/td][td]

91,99%​

[/td][td]

1​

[/td][td]

0,01​

[/td][td][/td][td]

95%​

[/td][td]

1,23​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

92,00%​

[/td][td]

100%​

[/td][td]

1,2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in G2 copied down
=VLOOKUP(F2,$A$2:$C$4,3)+(F2-VLOOKUP(F2,$A$2:$A$4,1))*$D$3*100

Hope this helps

M.

 

[NabMo]

Perfect!

Thanks alot!