Hi there,
I'm trying to come up with a formula that will allow me to compare the race results of teammates in a certain sport.
What I need is a way of finding out the sums of a) both teammates' results and b) only one teammate's results in races that they both finished.
This is quite hard to explain, so I will give a condensed example:
<tbody>
</tbody>
So I want two formulae that will look through my table and find out which races were finished by both teammates (e.g. Ferrari), and then give me:
Any non-numerical values (DNS, blanks, Ret, etc.) indicate that the driver did not finish a race.
(For those unfamiliar with the sport in question, a maximum of two drivers per team compete in any given race. However, it may be the case (as with McLaren) that more than two drivers represent the team over the course of several races, e.g. because one of the main two drivers is injured and has to be replaced with a reserve driver for the same team. So although you will only ever be looking at two rows per team for each race, the exact references of those rows may vary. For that reason, and in order to add in and move around drivers/teams easily, I want the formula to be capable of identifying which rows it needs to look at for each race.)
e.g. For Vettel, who drives for Ferrari, first we must identify that the only race finished by both Ferrari drivers (whoever they were at the time) is China. The first formula should give the sum of both Ferrari drivers' results in China: 2+5 = 7. The second formula should show that the sum of Vettel's results in China is 2.
For Ricciardo, the races finished by both drivers were Bahrain and China. The first formula should give 4+4+7+3=18 and the second should give 4+4=8.
The closest I have been able to get to this is a formula that calculates the sum of all the team results in races finished by the individual driver, and then deducts the individual driver's results - thereby giving the sum of the driver's teammate's results in all races finished by them both. I have done this using: SUMPRODUCT($E$3:$G$9,($B$3:$B$9=B3)*ISNUMBER($E$3:$G$9)*ISNUMBER(E3:G3))-SUM(E3:G3)
where: the formula relates to the individual to whom row 3 relates
B3:B9 is the range containing team names
E3:G9 is the range containing all drivers' race results
E3:G3 is the range containing only the race results for the individual named in row 3.
This is a bit of a bodge and I can't think of a way of applying the same logic used here to a similar formula that gives me the results I want. I'm sure that the right formula will be either an array formula or a SUMPRODUCT of some kind, but for the life of me I can't figure out how to get there.
(The plan is to use these numbers to calculate certain comparative statistics, such as the mean difference in finishing positions between driver X and his teammate across all races in which both driver X and his teammate finished.)
Hopefully I've explained the issue and required outcome properly. Any ideas?
I'm trying to come up with a formula that will allow me to compare the race results of teammates in a certain sport.
What I need is a way of finding out the sums of a) both teammates' results and b) only one teammate's results in races that they both finished.
This is quite hard to explain, so I will give a condensed example:
AUS | BHR | CHN | ||
Red Bull | Ricciardo | 4 | 4 | 4 |
Ferrari | Vettel | 3 | DNS | 2 |
Ferrari | Räikkönen | Ret | 2 | 5 |
Red Bull | Kvyat | DNS | 7 | 3 |
McLaren | Vandoorne | 10 | ||
McLaren | Alonso | Ret | 12 | |
McLaren | Button | 14 | Ret | 13 |
<tbody>
</tbody>
So I want two formulae that will look through my table and find out which races were finished by both teammates (e.g. Ferrari), and then give me:
- across all of those races, the sum of the two teammates' combined results
- across all of those races, the sum of the individual's results
Any non-numerical values (DNS, blanks, Ret, etc.) indicate that the driver did not finish a race.
(For those unfamiliar with the sport in question, a maximum of two drivers per team compete in any given race. However, it may be the case (as with McLaren) that more than two drivers represent the team over the course of several races, e.g. because one of the main two drivers is injured and has to be replaced with a reserve driver for the same team. So although you will only ever be looking at two rows per team for each race, the exact references of those rows may vary. For that reason, and in order to add in and move around drivers/teams easily, I want the formula to be capable of identifying which rows it needs to look at for each race.)
e.g. For Vettel, who drives for Ferrari, first we must identify that the only race finished by both Ferrari drivers (whoever they were at the time) is China. The first formula should give the sum of both Ferrari drivers' results in China: 2+5 = 7. The second formula should show that the sum of Vettel's results in China is 2.
For Ricciardo, the races finished by both drivers were Bahrain and China. The first formula should give 4+4+7+3=18 and the second should give 4+4=8.
The closest I have been able to get to this is a formula that calculates the sum of all the team results in races finished by the individual driver, and then deducts the individual driver's results - thereby giving the sum of the driver's teammate's results in all races finished by them both. I have done this using: SUMPRODUCT($E$3:$G$9,($B$3:$B$9=B3)*ISNUMBER($E$3:$G$9)*ISNUMBER(E3:G3))-SUM(E3:G3)
where: the formula relates to the individual to whom row 3 relates
B3:B9 is the range containing team names
E3:G9 is the range containing all drivers' race results
E3:G3 is the range containing only the race results for the individual named in row 3.
This is a bit of a bodge and I can't think of a way of applying the same logic used here to a similar formula that gives me the results I want. I'm sure that the right formula will be either an array formula or a SUMPRODUCT of some kind, but for the life of me I can't figure out how to get there.
(The plan is to use these numbers to calculate certain comparative statistics, such as the mean difference in finishing positions between driver X and his teammate across all races in which both driver X and his teammate finished.)
Hopefully I've explained the issue and required outcome properly. Any ideas?