Sports Result Matrix

[carlahutt]

Hi I am trying to find a formula that can be applied across a matrix scoring sheet.

It is a simple sheet (see attached)
All players will play each other.
The points are added together in column J
But I want to add up the wins in column I
to do this I currently have a unique formula for each line and when I add more players for next weeks tournament I have to write new functions.
eg formula for I3 =IF(E3>D4,1,0)+IF(F3>D5,1,0)+IF(G3>D6,1,0)+IF(H3>D7,1,0)

The example shown has only 5 players. In this case each player will have 4 games to play everyone else

Is there anyway to have a formula that will expand/contract according to the number of players.

Thanks Carla

[windknife]

Try this,

I3

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter, then copied down.

You can also see attachment.
Hope this helps,
WindKnife

[DonkeyOte]

Another variant of Windknife's approach only using INDEX rather than INDIRECT:

I3: =SUM(IF($D3:$H3>TRANSPOSE(INDEX($D$3:$H$7,0,ROWS(I$3:I3))),1))
confirmed with CTRL + SHIFT + ENTER
copied down

When using Array's its generally a good idea to avoid Volatile Functions (eg INDIRECT) given the Array will otherwise recalculate every time XL recalculates (ie smart calculation does not apply) - pending volume of Arrays and volume of data performance will be affected noticeably.

[carlahutt]

Thanks Windknife,
Is there a way to expand my matrix from 5 x 5 to say 8 x 8 and apply that formula. This is the challenge I face as each week there can be a different number of players. For each extra player there is one extra game each.

I was thinking of creating a large matrix eg. 16 x 16 and hiding the cols and rows not used. Is this the only way? see 6x6 attachment. I just expanded your s/sheet

thx Carla

[DonkeyOte]

Carla, if preferred you can of course disregard my point re: Volatility but based on the non-volatile Array you need only alter the range references accordingly, eg:

Please Login or Register  to view this content.

[carlahutt]

Thanks DonkeyOte,
I can see the dimensions of the matrix in your formula and it works brilliantly in the history of games I have recorded

Thanks so much.

One day I'll get into macros and create a variable array for my needs

Thx. Carla