Assign points based on two conditions

[Anthony]

I have checked the group's old posts and can not find a suitable
solution to the following problem. I am working on a spreadsheet for
a golf league wherein we play at a different course each week over the
summer. I wish to assign points to the players each week based on
order of finish determined using the RANK function and the number of
participants. For example, in week one, ten golfers played the
course. The person with the lowest score would get 100 points, second
lowest would get 90 points, etc. In week two, only six golfers played
the course. The person with the lowest score would get 60 points,
second would get 50 points, etc. Worksheet is formatted with player
names in cells A18:A30. Their finishes on each course are in cells
D1830 through Q18:Q30. If a player did not play a course, DNP is
listed in the cell otherwise it shows what place they finished.
Thanks so much.

 

[joeu2004]

For example, in week one, ten golfers played the
course.  The person with the lowest score would get
100 points, second lowest would get 90 points, etc.
In week two, only six golfers played the course.  The
person with the lowest score would get 60 points, second
would get 50 points, etc.  Worksheet is formatted with
player names in cells A18:A30.  Their finishes on each
course are in cells D1830 through Q18:Q30.  If a player
did not play a course, DNP is listed in the cell otherwise
it shows what place they finished.

Suppose you want the points for column Q in R18:R30. Put the
following into R18 and copy down:

=IF(ISNUMBER(Q18),RANK(Q18,$Q$18:$Q$30)*10),"")

Substitute 0 for "" (null string) if you that is what you want for
"DNP" players.