I have a Google Spreadsheet with a value (in seconds) for a lot of teams. I want to calculate points for each team from 0-10: 10 points for the team with the lowest value, 0 points for the team with the highest value and 9-1 points for the values in between (evenly distributed).
I honestly can't think of any formular to do this...and I have tried for about an hour :(
Could some please give me a hint?
After a little break I figured out a way to do it:
ROUNDUP(10-((A1-MIN($A$1:$A$30))/((MAX($A$1:$A$30)-MIN($A$1:$A$30))/10)))
Works for me
This is a fairly straightforward binning problem.
Assuming you have a range with the teams and seconds in columns, and the seconds column is B1:B30, the first thing you do is sort the range on seconds descending.
Then you calculate the size of the bin. There are two possibilities: one is that by "evenly distributed" you mean each bin (score) represents the same time interval, the other is that each bin holds the same number of teams.
For the time version, make a cell named "binsec" with the formula =(B1-B29)/9
Then highlight C1:C30 and enter the array formula
=CEILING(($B$1-B:B)/binsec,1) [Ctrl-Shift-Enter]
For the number of teams version make "binpop" =28/9. Highlight C1:C30 and enter the array formula
=CEILING((ROW()-1)/binpop,1)
Both of these test well with Excel 2007.