Can this be done using an array formula ?

[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).

 

[borntorun75]

Hi,

A couple of further points on this question.

1) The formatting of my question went a bit awry.

Column;
A shows the names.
B shows Race 1 details
C shows Race 2 details
D shows Race 3 details
E shows Race 4 details
F shows Total Points.

2) The spreadsheet shows 4 races. Ultimately this would have to be able to
handle more than 4 races
and more than 4 competitors.

Thanks again all.
Mike.

 

[borntorun75]

Superb Richard. That's exactly what I was after.

Not used SUMPRODUCT before. Just the job.

Thanks ever so much.

Mike (also in Cheshire, UK)


message [ snip ]

 

[MattShoreson]

It's not an array, and it's not pretty...

...but it works.

=IF(ISBLANK(B5),0,MAX($B$2:$B$5)-B5+1)+IF(ISBLANK(C5),0,MAX($C$2:$C$5)-C5+1)+IF(ISBLANK(D5),0,MAX($D$2:$D$5)-D5+1)+IF(ISBLANK(E5),0,MAX($E$2:$E$5)-E5+1)