B
borntorun75
Hi,
Apologies for the wordy posting, but it's only to give detail to the
problem.
It's quite a simple problem in theory, but it's giving me headaches!
I have a spreadsheet as follows, which shows the position finished in 4
races;
A B C D E
F
------------------------------------------------------------------------------
1| Race 1 Race 2 Race 3 Race 4
Total points
2| John 2 2 - 3
3| Phil - 1 2 4
4| Dave 1 3 - 1
5| Alan 3 - 1 2
6|
7| Competitors 3 3 2 4
In the above, any of the 4 competitors can compete in any of the races.
e.g. in Race 1: John, Dave and Alan competed, but Phil did not.
e.g. in Race 3: Phil and Alan competed. John and Dave did not.
The competitors don't have to enter every race.
Cells B2:E5 show the competitors finishing position.
In row 7, the number of competitors per race is shown (using COUNT)
Depending on the number of competitors (in B7, C7, etc.), the points (row F)
are allocated as follows;
points value = number of competitors - position + 1
So in Race 1, the points would be calculated as;
number of competitors = 3
John's points = 3 (no.of competitors) - 2 (his position) + 1 giving 2 points
Dave's points = 3 (no.of competitors) - 1 (his position) + 1 giving 3 points
Alan's points = 3 (no.of competitors) - 3 (his position) + 1 giving 1 point.
Phil didn't enter = 0 points in this race.
In Race 3, the points would be calculated as;
number of competitors = 2
Phil's points = 2 (no.of competitors) - 2 (his position) + 1 giving 1 point
Alan's points = 2 (no.of competitors) - 1 (his position) + 1 giving 2
points.
John and Dave didn't enter, so they get 0 points in this race.
Taking this points scoring system, I've shown the number of points scored in
brackets alongside each finishing position.
A B C D
E F
--------------------------------------------------------------------------------------
1| Race 1 Race 2 Race 3 Race 4
Total Points
2| John 2(2) 2(2) -
3(2) 6
3| Phil - 1(3) 2(1)
4(1) 5
4| Dave 1(3) 3(1) -
1(4) 8
5| Alan 3(1) - 1(2)
2(3) 6
6|
7| Competitors 3 3 2
4
So, in race 4;
Dave was awarded 4 points because he came 1st.
Alan was awarded 3 points because he came 2nd.
John was awarded 2 points because he came 3rd.
Phil was awarded 1 point because he came 4th.
So, here's my question, trying to calculate column F.
Is there any way in a single (array ?) formula of doing this calculation :-
number of competitors - position + 1 ..... for each race they've entered,
and put the total of those points into column F (expected result has been
shown
in the above mock up)
Note. I think an ISBLANK would need to be used because people who didn't
enter
the race won't be awarded a position.
I've tried to tackle this with an array formula but I'm not quite there.
Maybe it can't
be done with a traditional formula (?). Any thoughts would be appreciated.
Mike (using XL 03).
Apologies for the wordy posting, but it's only to give detail to the
problem.
It's quite a simple problem in theory, but it's giving me headaches!
I have a spreadsheet as follows, which shows the position finished in 4
races;
A B C D E
F
------------------------------------------------------------------------------
1| Race 1 Race 2 Race 3 Race 4
Total points
2| John 2 2 - 3
3| Phil - 1 2 4
4| Dave 1 3 - 1
5| Alan 3 - 1 2
6|
7| Competitors 3 3 2 4
In the above, any of the 4 competitors can compete in any of the races.
e.g. in Race 1: John, Dave and Alan competed, but Phil did not.
e.g. in Race 3: Phil and Alan competed. John and Dave did not.
The competitors don't have to enter every race.
Cells B2:E5 show the competitors finishing position.
In row 7, the number of competitors per race is shown (using COUNT)
Depending on the number of competitors (in B7, C7, etc.), the points (row F)
are allocated as follows;
points value = number of competitors - position + 1
So in Race 1, the points would be calculated as;
number of competitors = 3
John's points = 3 (no.of competitors) - 2 (his position) + 1 giving 2 points
Dave's points = 3 (no.of competitors) - 1 (his position) + 1 giving 3 points
Alan's points = 3 (no.of competitors) - 3 (his position) + 1 giving 1 point.
Phil didn't enter = 0 points in this race.
In Race 3, the points would be calculated as;
number of competitors = 2
Phil's points = 2 (no.of competitors) - 2 (his position) + 1 giving 1 point
Alan's points = 2 (no.of competitors) - 1 (his position) + 1 giving 2
points.
John and Dave didn't enter, so they get 0 points in this race.
Taking this points scoring system, I've shown the number of points scored in
brackets alongside each finishing position.
A B C D
E F
--------------------------------------------------------------------------------------
1| Race 1 Race 2 Race 3 Race 4
Total Points
2| John 2(2) 2(2) -
3(2) 6
3| Phil - 1(3) 2(1)
4(1) 5
4| Dave 1(3) 3(1) -
1(4) 8
5| Alan 3(1) - 1(2)
2(3) 6
6|
7| Competitors 3 3 2
4
So, in race 4;
Dave was awarded 4 points because he came 1st.
Alan was awarded 3 points because he came 2nd.
John was awarded 2 points because he came 3rd.
Phil was awarded 1 point because he came 4th.
So, here's my question, trying to calculate column F.
Is there any way in a single (array ?) formula of doing this calculation :-
number of competitors - position + 1 ..... for each race they've entered,
and put the total of those points into column F (expected result has been
shown
in the above mock up)
Note. I think an ISBLANK would need to be used because people who didn't
enter
the race won't be awarded a position.
I've tried to tackle this with an array formula but I'm not quite there.
Maybe it can't
be done with a traditional formula (?). Any thoughts would be appreciated.
Mike (using XL 03).