Taking performance goals, converting to scale 0 - 10

[Speedo]

So i have had several Microsoft Techs try to solve this and while we seem to get close, something is missing. I have a spreadsheet that takes employee standards and attempts to score the standards on a scale from 0 - 10. For example

https://www.facebook.com/photo.php?fbid=1628675570594681&set=a.1628676030594635&type=3&theater

The formula looks like this:

=IF(M9="","",IF(M9<H9,3.99*((M9/H9)*100)/100,IF(AND(M9>=H9,M9<I9),5.99*((M9/I9)*100)/100,IF(AND(M9>=I9,M9<J9),7.99*((M9/J9)*100)/100,IF(AND(M9>=J9,M9<K9),9.99*((M9/K9)*100)/100,10)))))

When I do the math with a calculator, I come up with the result shown in N9, but it is not the result I would expect. M9 happens to be exactly 90.000, So I would expect the scaling to return exactly 4.000, NOT 5.776.

Anyway, I have been working on this formula for years, but no one has been able to get it to work. The break points of G9, H9, I9, J9, K9 are there to show milestone in performance and N9 changes colors from Blank, Green, Blue, Purple, Gold.

I have 2 other example cells where this calculation returns a number not expected in N9.

Thank you guys for any time and interest of this project.

Speed

 

[Speedo]

@Eric W

Appreciate your help on that sheet. I'm still a long way from understanding how to complete the task, but you've given me some direct and some hope!

BTW, I lived in Bountiful for many years and had a house in Woods Cross. I worked at Menlove Dodge Toyota back in the day, too. Even got married over at Canterbury place!

Small world, huh?

 

[Speedo]

OK... so I now understand the table and it means a lot to get an accurate formula. When I try to convert the table to visually compatible to my sheet, I loose the accuracy. Turning the table on the side would be make number of entries in by workbook much better for space... what could I be doing wrong?

This calculate perfectly:

=(B10-LOOKUP(B10,$D$3:$D$7))/(INDEX($D$4:$D$8,MATCH(B10,$D$3:$D$7))-LOOKUP(B10,$D$3:$D$7))*(VLOOKUP(B10,$D$3:$F$7,3)-VLOOKUP(B10,$D$3:$F$7,2))+VLOOKUP(B10,$D$3:$F$7,2)

[TABLE="width: 384"]
<colgroup><col style="width:48pt" width="64" span="6"> </colgroup><tbody>[TR]
[TD="class: xl72, width: 64"] [/TD]
[TD="class: xl69, width: 64"]B[/TD]
[TD="class: xl70, width: 64"]C[/TD]
[TD="class: xl70, width: 64"]D[/TD]
[TD="class: xl70, width: 64"]E[/TD]
[TD="class: xl71, width: 64"]F[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]TABLE[/TD]
[TD="class: xl65"]LOW[/TD]
[TD="class: xl65"]HIGH[/TD]
[/TR]
[TR]
[TD="class: xl67"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]0.000[/TD]
[TD="class: xl65"]0.000[/TD]
[TD="class: xl65"]3.999[/TD]
[/TR]
[TR]
[TD="class: xl67"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]85.000[/TD]
[TD="class: xl65"]4.000[/TD]
[TD="class: xl65"]5.999[/TD]
[/TR]
[TR]
[TD="class: xl67"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]88.333[/TD]
[TD="class: xl65"]6.000[/TD]
[TD="class: xl65"]7.999[/TD]
[/TR]
[TR]
[TD="class: xl67"]6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]91.667[/TD]
[TD="class: xl65"]8.000[/TD]
[TD="class: xl65"]9.999[/TD]
[/TR]
[TR]
[TD="class: xl67"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]95.000[/TD]
[TD="class: xl65"]10.000[/TD]
[TD="class: xl65"]10.000[/TD]
[/TR]
[TR]
[TD="class: xl67"]8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl67"]9[/TD]
[TD="class: xl65"]SCORE[/TD]
[TD="class: xl65"]RATING[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl68"]10[/TD]
[TD="class: xl65"]79.000[/TD]
[TD="class: xl65"]3.717[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


Below format is preferred, because it fits the volume of entries in my workbook, but returns a value that is not the same as above, like I would want.

=(I3-LOOKUP(I3,$C$3:$G$3))/(INDEX($D$3:$H$3,MATCH(I3,$C$3:$G$3))-LOOKUP(I3,$C$3:$G$3))*(VLOOKUP(I3,$C$3:$G$5,3)-VLOOKUP(I3,$C$3:$G$5,2))+VLOOKUP(I3,$C$3:$G$5,2)

[TABLE="width: 640"]
<colgroup><col style="width:48pt" width="64" span="10"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]B[/TD]
[TD="class: xl67, width: 64"]C[/TD]
[TD="class: xl67, width: 64"]D[/TD]
[TD="class: xl67, width: 64"]E[/TD]
[TD="class: xl67, width: 64"]F[/TD]
[TD="class: xl67, width: 64"]G[/TD]
[TD="class: xl67, width: 64"]H[/TD]
[TD="class: xl67, width: 64"]I[/TD]
[TD="class: xl67, width: 64"]J[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]SCORE[/TD]
[TD="class: xl65"]RATING[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl65"]Table[/TD]
[TD="class: xl65"]0.000[/TD]
[TD="class: xl65"]85.000[/TD]
[TD="class: xl65"]88.333[/TD]
[TD="class: xl65"]91.667[/TD]
[TD="class: xl65"]95.000[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]79.000[/TD]
[TD="class: xl65"]7.858[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl65"]Low[/TD]
[TD="class: xl65"]0.000[/TD]
[TD="class: xl65"]4.000[/TD]
[TD="class: xl65"]6.000[/TD]
[TD="class: xl65"]8.000[/TD]
[TD="class: xl65"]10.000[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl65"]High[/TD]
[TD="class: xl65"]3.999[/TD]
[TD="class: xl65"]5.999[/TD]
[TD="class: xl65"]7.999[/TD]
[TD="class: xl65"]9.999[/TD]
[TD="class: xl65"]10.000[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

I'm away from a computer right now so I can't test it, but I can see that you need to convert the VLOOKUPs to HLOOKUPs. I'd also recommend using the shorter formula, but that's up to you.

 

[Speedo]

Thank you, @Eric W
and everyone else who helped with my sheet... SUCCESS!!!

 

[Eric W]

Sweet! Glad it works for you.

I chatted with someone else from Bountiful a while back, he wanted to know if Nielsen's Frozen Custard was still there. It is, maybe I'll drop by later!