+ Reply to Thread
Results 1 to 4 of 4

Good Excel Challenge for clever mathematician

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Quebec, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Cool 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?

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Good Excel Challenge for clever mathematician

    You could use the Rank function.
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    04-09-2013
    Location
    Quebec, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    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

  4. #4
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Good Excel Challenge for clever mathematician

    What about this arrayu formula =RANK(B1,$B$1:$B$5,1)/MAX(RANK($B$1:$B$5,$B$1:$B$5,1))

    a1 = Bobby , b1 = 2 etc
    Appreciate the help? CLICK *

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1