Here's one crack at it ..
Sample file with the construct at:
http://www.savefile.com/files/4535198
Points Ranking Formulas_Cabo_wksht.xls
In Sheet2
--------
Table below is in A1:C17
Placing Points Bonus
1 450 30%
2 300 20%
3 180 12%
4 120 8%
....
15 15 1%
16 15 1%
2 defined names are created via: Insert Name Define
Name Refers To
--------------------
Bonus =Sheet2!$C$2:$C$17
Points =Sheet2!$B$2:$B$17
In Sheet1
-------
Table in cols A to F comprises:
Name-Placing-Points-Bonus-Bonus Points-Total
Play1 1 450 30.0% 24 474.0
Play2 2 240 16.0% 12.8 252.8
Play3 2 240 16.0% 12.8 252.8
Play4 4 120 8.0% 6.4 126.4
etc
where
Name col: Assumed maximum of 16 players
(= # of placings in Sheet1)
Placing col: Placings are manually input and assumed to include possibility
of ties. Tied placings are assumed treated in the same way as RANK(...). For
example, if the placing 2 appears twice, placing 3 would be skipped. And so
on.
Formulas placed in cols C to F:
In C2:
=SUMIF($B$2:$B$17,$B2,Points)/COUNTIF($B$2:$B$17,$B2)
In D2:
=SUMIF($B$2:$B$17,$B2,Bonus)/COUNTIF($B$2:$B$17,$B2)
In E2: =(COUNTA(A:A)-1)*5*D2
In F2: =SUM(C2,E2)
C2:F2 selected and copied down to F17
Note that Points and Bonus % are assumed
pro-rata apportioned in the event of ties
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cabo Wabo Jim" <Cabo Wabo
wrote in message
...
I have a spread sheet in which I enter players names and the place they
finished for a certain event. I would like the sheet to calculate their
points according to where they finish and assign it to them along with a
formula that calculates a bonus point value according to the number of
players in a certain event.
1st place-450 pts + 30% bonus
2nd place-300 pts + 20% bonus
3rd place-180 pts + 12% bonus
4th place-120pts + 8% bonus
5th place-105 pts + 7% bonus
6th place 90 pts + 6% bonus
7th place 75 pts + 5% bonus
8th place 60pts + 4% bonus
9th-16th place 15 pts + 1% bonus
16th place 15 points
Bonus point formula is (# players X 5)x %
Any help would be appreciated