[Rank] Points Allocation

[lavenlaar]

Last month I had some great advice from here, and it worked. So I am back again to ask assistance.

Original plan was to just use the ranking formula 1-50 racers. But this doesn't work too well if less than 50.

I'd like add a points system using;
1st place – 30 points
2nd place – 27 points
3rd place – 25 points
4th place – 23 points
5th place – 21 points
6th place – 20 points
7th place – 19 points
8th place – 18 points
9th place – 17 points
10th place – 16 points
11th place – 15 points
12th place – 14 points
13th place – 13 points
14th place – 12 points
15th place – 11 points
16th place – 10 points
17th place – 9 points
18th place – 8 points
19th place – 7 points
20th place – 6 points
21st place – 5 points
22nd place – 4 points
23rd place – 3 points
24th place – 2 points
25th place – 1 point
26>50 - 1 point

My ranking is in cells B18>AY18 =RANK(B17,$B$17:$AY$17,0) and would like to use B19>AY19 to show points.
Could anyone assist me?

Also if less than 50 racers i get the #DIV/0!

Thanks
Brendan

[MarvinP]

Hi Brendan,

I think you need to learn to use a VLookup Table. See the attached where I've built a sample of what I mean. I hope your 2008 version of Excel can do RandBetween and see how it works.

[lavenlaar]

thank you MarvinP
Mac08 version does support RandBetween (at least the formula is there)
So... the VLOOKUP is just calling that table obviously. DONE and works ...

Now the RandBetween I don't understand on the example

[MarvinP]

I use RandBetween to simply build some random data. It has nothing to do with your Ranks or VLookup.

You could remove the RandBetween and simply deal with values in Column A and get my whole answer...

Sorry for confusing you with some "Random" function.