Help Creating an Excel Formula

Factual Questions
1

I need to create a weighted scoring system, and I can’t figure out how to make it work.

Basically, entrants will have a maximum amount of time to complete the course, but their scores would be relative to the amount of time it took the 1st place entrant to finish. I would also like to weight the formula to recognize that it’s harder for 1st place to beat 2nd place than it is for 9th place to beat 10th place.

Here’s what I imagine the results would look like with numbers pulled from thin air.

Maximum time allowed: 120 minutes
Maximum points awarded: 100

1st - 60 minutes - 100 points
2nd - 65 minutes - 95 points
3rd - 90 minutes - 40 points
4th - 115 minutes - 5 points
5th - 120 minutes - 0 points

Thanks for the help.

2

Well, the linear formula is:

(1-(N-F)/(L-F))*100

where N=entrants score
F = best time
L = worst time

e.g. your second guy would work out to:

(1-(65-60)/(120-60))*100 = 91.67

3

Thanks. The weighted formula would be (1-((N-F)/(L-F)^.75))*100, right?

4

Yes, actually, (1-((N-F)/(L-F))^.75)*100 for clarity

5

Stewie, is that you?