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]

I do not see an edit, but the formula looks like thins

=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)))))

 

[Speedo]

Sorry, new guy... it is cutting of the full formula.

 

[Eric W]

Welcome to the forum.

First, the forum is probably cutting off your formula since it has a < or > in it. Typically, the forum interprets that as an HTML tag (formatting information), and doesn't treat it as a real "less than" sign. To get around that, put a space before and after each < or > in your formula. An alternate method is to turn off HTML code when you create the thread.

Next, if you want to post a sample sheet, try using one of the HTML creation tools (see the link in my signature). Or you can post the workbook to a public file sharing site and post the link. However, some people can't or won't download files from the internet for various reasons, so that limits your audience. And some people (like me) don't have Facebook, so that's even less desirable.

Finally, from what I see of your formula, it's not formatted properly, so I'm not surprised it doesn't give you the answer you want. If you show the rest of it, we can look at it.

Good luck!

 

[dreid1011]

Full formula:

=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)))))

?

 

[Speedo]

Thank you for being kind to this rookie on a forum.

Trying to show picture:

 

[Eric W]

@dreid1011 , thanks for digging out the formula.
@Speedo, I still can't see the picture, but based on the formula, I have a few thoughts. First, multiplying by 100 and dividing by 100 cancels out, so your equations can be simplified. Second, when you have a tiered function like this, it's usually better to use a MATCH or LOOKUP function to separate the tiers, instead of a long IF(AND ... formula. For example:

	G	H	I	J	K	L	M	N
0.1198

<tbody>
[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5.326667[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0.1596[/TD]

[TD="align: right"]0.106533[/TD]
[TD="align: right"]0.0999[/TD]
[TD="align: right"]#DIV/0![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5.326667[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G10[/TH]
[TD="align: left"]=3.99/H9[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H10[/TH]
[TD="align: left"]=5.99/I9[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I10[/TH]
[TD="align: left"]=7.99/J9[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J10[/TH]
[TD="align: left"]=9.99/K9[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K10[/TH]
[TD="align: left"]=1/0[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N9[/TH]
[TD="align: left"]=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)))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N10[/TH]
[TD="align: left"]=IFERROR(IF(M9="","",M9*HLOOKUP(M9,G9:K10,2)),10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Your formula is in N9. I added the values in G9 and the formulas in G10:K10, which enormously simplifies the formula in N10, which generates the same results as N9. Since I couldn't see the picture, I just put test values in H9:K9.

Finally, it might be easier to explain what you want to accomplish. Based on some testing, this formula generally rises as the value in M9 rises, but it dips at each of the tiers and starts to rise again. Is that what you want?

 

[Speedo]

@Eric W I noticed the *100/100 a while back, but when I try to remove the redundancy, I get other errors.

The best way that I can explain what I'm hoping to accomplish... Suppose you have a goal to solve peoples Excel problems of 100% success. If you achieve 100% success you would consider that "Best" would score a 10.000. Also, you may consider what a minimum standard of success before you (or a supervisor) would consider inadequate performance, say < 90%... So in my sheet "BDC Standards," G9 would be performance that is non satisfactory and considered a Caution. H9 would be that minimum standard, and K9 mean Best or world class.... So our example for a Microsoft Tech of 80% minimum standard that would be used in H9, anything lower than 40% would score in N9 and score (on a scale from 1 - 10) something in between 0.000 - 4.000. While anything 100% would achieve a score of 10. So the formula that I am hoping to solve here in N9, is to score someones performance on a scale from 0 - 10. M9 (actual) = 90.000 should score 4.000, NOT 5.776.

Screen shots continue to be a mystery to me, so here is a copy/paste so anyone can see the way I'm trying to show the information...

[TABLE="width: 1118"]
<colgroup><col><col><col><col><col><col span="5"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] G
[/TD]
[TD] H
[/TD]
[TD] I
[/TD]
[TD] J
[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD] M
[/TD]
[TD] N
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]July-2018
[/TD]
[TD]0 - 3.99[/TD]
[TD]4-5.99
[/TD]
[TD]6-7.99
[/TD]
[TD]8-9.99
[/TD]
[TD] 10
[/TD]
[TD][/TD]
[TD]Actual[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Achieve[/TD]
[TD]100.00[/TD]
[TD]%[/TD]
[TD]Responded to Goal #2 (0.13 Hrs)
[/TD]
[TD] <90.000
[/TD]
[TD]90.000[/TD]
[TD]93.333[/TD]
[TD]96.667[/TD]
[TD]100.000[/TD]
[TD][/TD]
[TD]90.000[/TD]
[TD]5.776[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Again, sincerely appreciate the time you guys are giving to me!

 

[Speedo]

@Eric W I noticed the *100/100 a while back, but when I try to remove the redundancy, I get other errors.

The best way that I can explain what I'm hoping to accomplish... Suppose you have a goal to solve peoples Excel problems of 100% success. If you achieve 100% success you would consider that "Best" would score a 10.000. Also, you may consider what a minimum standard of success before you (or a supervisor) would consider inadequate performance, say < 90%... So in my sheet "BDC Standards," G9 would be performance that is non satisfactory and considered a Caution. H9 would be that minimum standard, and K9 mean Best or world class.... So our example for a Microsoft Tech of 90% minimum standard that would be used in H9, anything lower than 40% would score in N9 and score (on a scale from 1 - 10) something in between 0.000 - 4.000. While anything 100% would achieve a score of 10. So the formula that I am hoping to solve here in N9, is to score someones performance on a scale from 0 - 10. M9 (actual) = 90.000 should score 4.000, NOT 5.776.

Screen shots continue to be a mystery to me, so here is a copy/paste so anyone can see the way I'm trying to show the information...

[TABLE="width: 1118"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] G [/TD]
[TD] H [/TD]
[TD] I[/TD]
[TD] J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD] M[/TD]
[TD] N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]July-2018[/TD]
[TD]0 - 3.99[/TD]
[TD]4-5.99[/TD]
[TD]6-7.99[/TD]
[TD]8-9.99[/TD]
[TD] 10[/TD]
[TD][/TD]
[TD]Actual[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Achieve[/TD]
[TD]100.00[/TD]
[TD]%[/TD]
[TD]Responded to Goal #2 (0.13 Hrs)[/TD]
[TD] <90.000[/TD]
[TD]90.000[/TD]
[TD]93.333[/TD]
[TD]96.667[/TD]
[TD]100.000[/TD]
[TD][/TD]
[TD]90.000[/TD]
[TD]5.776[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Again, sincerely appreciate the time you guys are giving to me!

I changed the quote because i used 80% , but should have been 90%

 

[Eric W]

Probably easiest to build a table, and have the formula scale each tier based on the table:

Excel 2012

	M	N	O	P	Q	R
Table	Low	High
Score	Rating

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.99[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5.99[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]93.333[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7.99[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]96.667[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9.99[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N9[/TH]
[TD="align: left"]=(M9-LOOKUP(M9,$P$2:$P$6))/(INDEX($P$3:$P$7,MATCH(M9,$P$2:$P$6))-LOOKUP(M9,$P$2:$P$6))*(VLOOKUP(M9,$P$2:$R$6,3)-VLOOKUP(M9,$P$2:$R$6,2))+VLOOKUP(M9,$P$2:$R$6,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



You can simplify the formula a bit if you add another column to the table:

Excel 2012

	M	N	O	P	Q	R	S
Table	Low	High	Scaling factor
Score	Rating

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.99[/TD]
[TD="align: right"]0.044333[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5.99[/TD]
[TD="align: right"]0.59706[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]93.333[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7.99[/TD]
[TD="align: right"]0.596881[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]96.667[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9.99[/TD]
[TD="align: right"]0.59706[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]3.901333[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S2[/TH]
[TD="align: left"]=(R2-Q2)/(P3-P2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S3[/TH]
[TD="align: left"]=(R3-Q3)/(P4-P3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S4[/TH]
[TD="align: left"]=(R4-Q4)/(P5-P4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S5[/TH]
[TD="align: left"]=(R5-Q5)/(P6-P5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S6[/TH]
[TD="align: left"]=(R6-Q6)/(P7-P6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N9[/TH]
[TD="align: left"]=(M9-LOOKUP(M9,$P$2:$P$6))*VLOOKUP(M9,$P$2:$S$6,4)+VLOOKUP(M9,$P$2:$R$6,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]