# Good Excel Challenge for clever mathematician

1. ## Good Excel Challenge for clever mathematician

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?  Register To Reply

2. ## Re: Good Excel Challenge for clever mathematician

You could use the Rank function.  Register To Reply

3. ## Re: Good Excel Challenge for clever mathematician

Yes, that could do.

Ideally, I would like to be able to spread the result exponentionnaly. Like being able to have 5 results between 90 and 95, 15 results between 85 and 90, 25 results between 80 and 90...

I used to make the formula myself, creating a graph with the numbers 5, 15, 25 which I would pair up with the 5th, 15th and 25th value of my stats. I then extracted the formula from the graph, but it is a very long process and I have to do it again for each group of stats. That's why I think it's complicated to automate this  Register To Reply

4. ## Re: Good Excel Challenge for clever mathematician

a1 = Bobby , b1 = 2 etc  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 