Lookup

[Peter]

I wish to be able to scan the 1st 6 places of a result, and calculate points
awarded to each club based on their finish order with points scored 6 for
1st, 5 for 2nd,4 for 3rd etc...

A1= finish pos 1 (points scored = 6)

etc..

A6=finish pos 6 (points scored = 1)

b1 = swimmer 1

etc..

b6 = swimmer 6

c1= team 1

etc..

c6 = team 6

team1 finish 1st,3rd & 6th pts =11
team2 finish 2nd & 4th pts = 8
team3 finish 5th pts = 2

I will then sum all pts scored in a seperate sheet if you can give me
formula for above

thanks

Peter

 

[Paul Falla]

Dear Peter

If you are confidant in using lookups, I would suggest
adding an extra column to the data you are looking up
from. This column would contain the ranking of the team.
Excel has a built in function called Rank which you can
use for this purpose. You can then use 6 lookups (one for
each placing) to retrieve the relevant teams.

Hope this helps

Paul Falla

 

[Peter]

I'd though of that, but that means me manually editing the results. Is the
following possible:

1. Lookup the Event name in a sheet
2. Lookup an individual age group with in an event
3. Sum the occurences of teams in the 1st 6 places

e.g

Event 1 Girls 9/Over 50m Freestyle

09 YEARS AGE GROUP
1. Jenny Team1
2. Penny Team 2
3. Jill Team 1
4. Val Team 3
5. Julie Team 4
6. Fiona Team 1

10 YEARS AGE GROUP

1. Jenny Team1
2. Penny Team 2
3. Jill Team 3
4. Val Team 4
5. Julie Team 5
6. Fiona Team 6

I will have a main sheet with the Event Number & designated age groups Team
Names to give me the data to look for, but I would like to scan the results
automatically for these designated points. Allowing for equal placings as
well, e.g equal 1st place get 6 each etc.

Hope you can help

Peter