Converting Scores to Percentage

**hypothetical** · 01-03-2014, 02:05 PM

Hi,

I have a score sheet that tracks our quality performance. The scoring system is as follows (we allow up to 1 decimal increments):

1 - Highest
2 - Above Average
3 - Average
4 - Below Average
5 - Lowest

I now need to get the equivalent percentage so they will look something like this:

1 - 100%
2
3 - 85%
4
5 - 70%

I'd like to create a formula wherein I can just input the score and its equivalent percentage would computed automatically. Anyone got any ideas?

**MrShorty** · 01-03-2014, 02:18 PM

Those input percentages appear to be exactly linear, so I would probably derive the equation for that straight line and use that. This might be easiest (since you probably don't actually need to know the equation) using the TREND() function. http://office.microsoft.com/en-us/ex...832.aspx?CTT=1

1) Put the desired "fixed" values in a table. The TREND() function does not like blanks, so you will need to set it up accordingly:

Please Login or Register  to view this content.

2) To calculate the percentage at some "new_x", enter new_x into a cell.
3) To calculate the percentage at that x, enter =TREND(known_y,known_x,new_x) into an adjacent cell. known_y etc are references to the appropriate cell(s) in the spreadsheet.

Note that this only works as long as the "fixed" points form a perfectly straight line. If it doesn't form a straight line, then you will need to further define how you want to calculate the percentages.