How can I create a League table based on points scored?

  • Thread starter Thread starter NickS
  • Start date Start date
N

NickS

Can anybody help me?

I am trying to create a League Table based on 8 people and their tota
scores (eg. a fantasy football league).

Eg.
Player A = 10 points
Player B = 13 points
Player C = 8 points
Player D = 11 points
etc., etc.

Table should read:
Player B with 13 points
Player D with 11 points
Player A with 10 points
Player C with 8 points
etc., etc.

How do I get Excel to list the players in order of their scores, mos
points = top of the league?
And when I change the weekly score they might change position
depending on their total.

Any help on this will be very helpful and appreciated, THANKS!

Nick
 
Suppose the players are in column A starting with A2 (A1 is a header
named, say, 'Player'). Suppose the current points are in column B,
starting with B2.

Then, create the following named formulas (Insert | Name > Define...)

DataRng =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
PointsRng =OFFSET(DataRng,0,1)
RankedPoints =OFFSET(DataRng,0,2)

In C1 enter the text: Ranked Points
In D1 enter the text: Player by rank

In C2 enter the formula =RANK(PointsRng,PointsRng)+COUNTIF(OFFSET
(PointsRng,0,0,ROW()-ROW($C$2)+1,1),B2)-1

In D2 enter the formula =INDEX(DataRng,MATCH(ROW()-ROW($D$2)+
1,RankedPoints,0))

Copy C2:D2 as far down as needed to cover the data in A:B.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top