Attempting to total fixtures between teams into a matrix

**mrvp** · 11-04-2012, 04:19 PM

Hi,

I'm attempting to do some linear equation related things with a matrix into my usual subject - sports. In short what I am trying to do on the spreadsheet that I have attached, on the Matrix Idea worksheet I am trying to get the negative of the sum of the number of fixtures between two teams. Multiplying the result by -1, isn't difficult (at least this is making sense to me right now - whether it will actually work or not is another matter) - but what I am struggling to do is sum the total number of fixtures.

I've made a start in E11 for the sum of the number of fixtures between Arizona and Atlanta (incidentally the diagonal I will need to get to sum to a total of 4 - and the Washington line row 41 has to equal 1).

I know it's possible to have multiple criteria on an IF - I'm thinking this needs to be an array formula as well - I'm just unsure as to quite where to start.

The formula in E11 - the true criteria would be what I would want my second criteria to be (I know I would then need to flip this for if Arizona was at home and Atlanta was away - if this makes sense).

I think I understand the process that I'm trying to implement, but I just need a nudge in the right direction.

Thanks in advance for any advice.

NFL 2012_2013 Matrix Concept RevA.xls

**mrvp** · 11-05-2012, 04:26 PM

Hi,

I've figured a COUNTIFS would work for this (with one allowing for home and away with home being off of the vertical column and away off of the horizontal - and then vice versa for allowing it for the other way round), however joy of joys - Excel 2004 has a COUNTIF but not a COUNTIFS.

Is there a way round this?

I have done the E column of the matrix with four COUNTIF formulas to cover the constraints I need (the first and second formulas would make up the first COUNTIFS, which I would need to add to the third and fourth making up the second COUNTIFS).

I've also re-read the book that I am working from and I have realised that the diagonal is the total number of games played by Team i (which I am assuming are the teams horizontally - in the case of Column E, Arizona) so I have changed the formula for that diagonal cell as well (which I have highlighted green - all other cells tweaked are highlighted yellow).

Basically any advice is appreciated as I think I have figured the solution to my problem but I don't know if I can use it!,

Thanks,

NFL 2012_2013 Matrix Concept RevB.xls

**mrvp** · 11-06-2012, 07:02 PM

Hi,

I essentially went and solved my own problem by using:

{=SUM(IF(Scores!$B$48:$B$151=D10,IF(Scores!$D$48:$D$151=$F$9,1,0)))+SUM(IF(Scores!$B$48:$B$151=$F$9,IF(Scores!$D$48:$D$151=D10,1,0)))}

to substitute for not being able to use a COUNTIFS and switching D10 and $F$9 for the relevant team cell and copying down (which was a pain as I took about 45 mins with the TV on). In short is there a more elegant way of dealing with this (or can I adjust the ranges to save me doing a find and replace on 151 every time as I add more results to the Scores worksheet). I ended up doing a find and replace on my ratings worksheet (Matrix Final) - I added a third worksheet that I copied the data to and then realised I hadn't multiplied by -1.

Also was there a more elegant way of doing the top left to bottom right diagonal rather than manually adjusting formula such as:

=SUM(E14:H14)+SUM(J14:AJ14)

every time?

I have attached a copy of the worksheet so it is clear what I was trying to do with Matrix Working being my working out worksheet and Matrix Final being my ratings worksheet (ratings were after 7 weeks of the current NFL season). In short I bought a maths book, couldn't understand the process but had an idea of how I could fit it into a spreadsheet.

NFL 2012_2013 Matrix Concept RevC.xls