I have a list of statistics that I want to convert into a number from 1 to 100 according to the value. The higher the number, the closest it needs to be to 100.
For example:
Bobby: 2
Tom: 4
Jenny: 3
Bobby: 5
Now, normally, I would use a MAX formula and divide each number by the MAX, then multiply it by 100 which would give me:
Bobby: 2/5*100 = 40
Tom: 4/5*100 = 80
Jenny: 3/5*100 = 60
Bobby: 5/5*100 = 100
My problem is when Johnny gets into the picture:
Bobby: 2
Tom: 4
Jenny: 3
Bobby: 5
Johnny: 15
Now, with those numbers I get mostly small numbers all packed together and Johnny at 100. I have 600 statistics and I need them to be evenly (or almost evenly) classified from 1 to 100. Meaning, even with the high difference between the highest and lowest numbers, I would need to have about 100 stats at 80 and above, 100 between 60-70 100 between 50-40, and so on.
I believe I will need some sort of exponential formula. Maybe there is an easy solution I haven't think of, but I'm pretty sure it will be a challenge.
Any ideas?
Bookmarks