Hi,
I am trying to add another formula to my spreadsheet. This is concerned with where a runner finishes. We give points to runners who win races and who are 'placed' in a race. If they win a race that just goes in the win column and not in the place column.
If there are 10 or less runners in a race we only give points to the winner, there are no places.
If there are up to 20 runners in a race we give points to the winner, and second place gets placed.
If there are up to 30 runners in a race we give points to the winner, second place and third place gets placed.
If there are more than 30 runners in a race we give points to the winner, second place, third place and fourth gets placed.
I've put together the outcome I would like in the table below. Ideally I want to get rid of Column D and just have a formula in Cell L2, where each time I change the name in Cell J1, it would calculate the amount of times a runner placed.
Thank you for any help.
I am trying to add another formula to my spreadsheet. This is concerned with where a runner finishes. We give points to runners who win races and who are 'placed' in a race. If they win a race that just goes in the win column and not in the place column.
If there are 10 or less runners in a race we only give points to the winner, there are no places.
If there are up to 20 runners in a race we give points to the winner, and second place gets placed.
If there are up to 30 runners in a race we give points to the winner, second place and third place gets placed.
If there are more than 30 runners in a race we give points to the winner, second place, third place and fourth gets placed.
I've put together the outcome I would like in the table below. Ideally I want to get rid of Column D and just have a formula in Cell L2, where each time I change the name in Cell J1, it would calculate the amount of times a runner placed.
Thank you for any help.
Positions.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Name | Position | Participants | Placed | Participants | Placed | Name | Jenkins | Won | Placed | ||||
2 | Jenkins | 2 | 4 | N | 10 | 1 | 3 | 6 | ||||||
3 | Jenkins | 4 | 22 | N | 20 | 2 | ||||||||
4 | Jenkins | 1 | 9 | N | 30 | 3 | ||||||||
5 | Jenkins | 2 | 16 | Y | 40 | 4 | ||||||||
6 | Jenkins | 3 | 35 | Y | ||||||||||
7 | Jenkins | 2 | 8 | N | ||||||||||
8 | Jenkins | 1 | 12 | N | ||||||||||
9 | Jenkins | 1 | 7 | N | ||||||||||
10 | Jenkins | 11 | 40 | N | ||||||||||
11 | Jenkins | 3 | 36 | Y | ||||||||||
12 | Jenkins | 4 | 32 | Y | ||||||||||
13 | Jenkins | 2 | 21 | Y | ||||||||||
14 | Jenkins | 2 | 9 | N | ||||||||||
15 | Jenkins | 3 | 27 | Y | ||||||||||
16 | Davis | 3 | 4 | N | ||||||||||
17 | Davis | 7 | 22 | N | ||||||||||
18 | Davis | 2 | 9 | N | ||||||||||
19 | Davis | 3 | 16 | N | ||||||||||
20 | Davis | 2 | 35 | Y | ||||||||||
21 | Davis | 1 | 8 | N | ||||||||||
22 | Davis | 5 | 12 | N | ||||||||||
23 | Davis | 3 | 7 | N | ||||||||||
24 | Davis | 4 | 40 | Y | ||||||||||
25 | Davis | 2 | 36 | Y | ||||||||||
26 | Davis | 8 | 32 | N | ||||||||||
27 | Davis | 4 | 21 | N | ||||||||||
28 | Davis | 1 | 9 | N | ||||||||||
29 | Davis | 2 | 27 | Y | ||||||||||
Sheet1 |