Using rank and assigning point values

[Bmr23]

I am calculating results for a sports league where each participant gets a certain number of points each week and then, based on their position these are converted to "league points" for where they finish. There are 16 participants and the top finisher gets 15 points, 2nd gets 14, 3rd 13, etc. etc. all the way down to 16th who gets zero.
I am using the RANK function to find each person's finishing position, but each time I drag that formula to the next cell to copy it, it assumes a different range of cells from which to calculate that cell's position. it looks like it is using a "relative to that cell" logic. Also, I need to find a way to assign 15 points to 1st, 14 to 2nd, etc. etc. I thought of using an "if/then" statement, but it would not work if there were a tie because, let's say 3rd and 4th were tied, they should split the total points for 3rd and 4th. So i have 2 questions:

1) Can I use an absolute range of cells for the ranking to work from?

2) How can i assign "POINT VALUES" to finishing positions, and still account for possible ties.

Thanks very much

 

[barry houdini]

1

Use a formula like

=RANK(A1,$A$1:$A$10)

the $ signs make the range absolute (or you can use a named range)

2

So, if there's a 2-way tie for 1st do they share the points that would have been for 1st and 2nd, i.e. 14.5 each?

 

[barry houdini]

If scoring is as per my assumption then, with original points gained in A1:A16 this formula in B1 copied down should give "league points"

=(RANK(A1,A$1:A$16,1)+COUNTIF(A$1:A$16,"<="&A1))/2-1

 

[Bmr23]

WOW!!! No wonder you are an "MVP" and the name Houdini fits too! That worked perfectly, on both parts. Thank you very much for your super speedy response.

 

[Bmr23]

One more question, turns out I need the scoring to be from 16 to 1, not 15 to 0. Is the /2-1 at the end what I need to get rid of to make it score correctly for "league points"?

 

[barry houdini]

You still need to divide by 2, just get rid of the -1