Total points in Table

[Peter]

EVENT 12 GIRLS 50 Metres BUTTERFLY
A B C D E
Place Name YoB Club Time
1 Swimmer 1 -84 KENN 30.12
2 Swimmer 2 -60 CARN 31.34
3 Swimmer 3 -86 COKN 31.38
4 Swimmer 4 -89 COKN 31.44
5 Swimmer 5 -90 CARN 32.06
6 Swimmer 6 -86 ULVN 32.25


I tried VLOOKUP("Event Name",b1:b27,1,FALSE) which finds the Event I wish to
score, but I can't work out how to score the 1st 6 places. Do I need to use
"Offset" to put the 1st 6 places into a table.

Can someone give me a formula to lookup the above "Event" from a complete
set of results, and score points for the 1st 6 places starting with 6 for
1st, 5 for 2nd etc. This needs to be based on the "Club" name. e.g in the
example above, "COKN" would score 7 pts.

I want to list each Event in a column, then display points scored by each
Club across the top

Club 1 Club 2 Club 3 etc.. etc..
Event 1 8 10 2
Event 2 4 6 8
Event 3 6 4 5
etc..
Total 18 20 15

 

[Frank Kabel]

Hi Peter
the easies way would be a slight re-design of ypur spreadsheet:
- add a event number/event name to each data row
- just list all results in one large table

Now you can use Autofiler or Pivot tables for creating your report. For
pivot tables have a look at
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html