CODEWITHSUNDEEP
excelrangeaverage
Jamie Warren
Jamie Warren

Reputation: 95

How to spread a narrow range of values into a wider "score range"?

I have generated a series of percentages to summarize the performance of various counties across the country based on a few metrics. I'd like to use these "County Percentage" values to give each county a "Final Score" (1-100). However, the range of these values is only ~25, and the max/min is 53/78 respectively.

How can I give each row a score respective to where it falls within the global range? For instance, a County Percentage of 54 will score low on the 1-100 scale, while 77 would score higher.

I originally used: =ABS((INT(CEILING.MATH(AB3, MAX(AB3:AB1525)/1000)*1000/MAX(AB3:AB1525))/10)) but this fails to spread the scores across the entire range.

County Percentage Final Score
72.00 -
67.73 -
58.11 -
74.56 -
66.82 -
... -

I appreciate any feedback.

Upvotes: 1

Views: 80

Answers (1)

Michal
Michal

Reputation: 5883

There is a number of ways of doing it, but the simplest one would be something like:

 =1 + (99 * (A1 - MIN($A$1:$A$5)) / (MAX($A$1:$A$5) - MIN($A$1:$A$5)))

enter image description here

Upvotes: 1

Related Questions