Trying to create a Ranking System

[jvaldes88]

Hi, i am trying to create a ranking system base on points. I have 3 columns (Name, #Attempts, Points). My purpose is to grant a % of the #Attempts as points, the trick is this: I want to grant more points to who have LESS attempts. I tried to use % but of course, it isn't the same 1% of 5 (0.05) as 500 (5).

Any ideas?

Example:

Name #Attempts Points
John 5 99
Doe 30 62
Dann 100 20

[hoyasaxa215]

You would need to provide more information about how exactly you want to calculate the ratio of attempts to points awarded. Granting "more" points to people who have "less" attempts isn't something that can be translated into a formula. As an easy fix, why not just divide the number of attempts into a much higher number and use the quotient as a multiplier for the points calculation?

For example
John 200/5 = 40 points
Doe 200/30 = 6.7 points
Dann 200/100 = 2 points

[kev_]

How are you calculating the points?
You could use a lookup table

LookupTable.jpg

With points table on sheet named "PointsLookUp" (column A = attempts, column B = points)
formula in B2 copy down

Please Login or Register  to view this content.

Results.jpg