Calculating points from 0-10 for achieved time

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 tea开发者_Python百科m with the highest value and 9-1 points for the values in between (evenly distributed).

I can't think of any formula to do this.

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.