Help Creating a Formula in Excel

Factual Questions
1

First of all, thank you to everyone who’s already helped me with the formulas I’ve been creating. Everyone around the office thinks I’m a wiz, and I’ll admit, I haven’t confessed that I am only smart enough to know where to find the right people to ask.

I’m creating formulas to award points to entrants for the performance of devices they’re submitting in order to win a lucrative contract. One of the categories is Ease of Use, which, being a subjective category, will rank each entrant relative to the others, e.g: 1st place, 2nd place, etc.

Out of a possible 10 points, first place will receive 10, and last place will receive 0. However, I want to weight the results so difference between 1st and 2nd is less than between 2nd and 3rd. For example, assuming there were 10 entrants (and we don’t know how many there will be), 1st place would get 10 points, 2nd place 9.5 points, 3rd place 8.25 points, 4th place 7.85 points, and so on*.

How do I create this formula in Excel?

*These numbers are pulled from the air. I don’t want to create a lookup table.

2

There are any number of ways to do it satisfying the property you propose (all of which seem kind of arbitrary, even capricious; it’s not as though the resulting number means much anything significant)… Anyway, one way would be to award Nth place out of T total places 10 * [1 - ((N-1)/(T-1))^a] many points, where a is some constant > 1. Higher a results in a steeper increase in point difference.

Even steeper than all of those [probably too steep, but it’s all arbitrary] would be replacing ((N-1)/(T-1))^a with (b^((N-1)/(T-1)) - 1)/(b-1), where b > 1. Again, higher b result in a steeper increase in point difference.

3

Thanks you. This is perfect. Two questions:

  1. How would I reverse it, so that the difference between 1st and 2nd was greater than between 2nd &3rd?

  2. What purpose do the two -1s serve? Playing with the numbers, they seem to cancel each other out.

Again, thank you.

4

  1. You could use a positive a < 1 instead of > 1. Alternatively, you could just replace the “N-1” with “T-N” (essentially flipping the input ranking before feeding it to the old formula)

  2. They shouldn’t cancel out, any more than 1/2 = 2/3. The reason for the “N-1” at the top was simply to let the lowest numbered place be treated as 0, rather than as 1. The reason for the “T-1” at the bottom is to correspond to that, so that the highest numbered place gets sent to 1 after division.

I’m sure there are myriad ways to shuffle this formula around into other things; it was just one quick thing whipped up.

5

Beautiful. Thanks.