Calculating Last 6 Results for League

Hello there!

This is my second post on here. My first was a success so I am feeling optimistic about this one.

So here is my question. I have created a spreadsheet on the English Premier League with all the current results and automatically updating tables and stats etc.

What I would now like to do is create a section of my league table to have the results of each team's last 6 games (current form), so it would show win, draw or loss (ideally, green cell for win, yellow for draw and red for loss). I've searched all around the web but haven't found anything that can help me. The closest I've got is being able to calculate the last 6 home or away matches but I can't do that for all matches.
Please see attached and help me out. I have got it set up so that all I need to do is enter the results of each match on the data sheet and everything else updates. If you go to the League Table sheet you will be able to see where I would like to add the "form" section.

If anyone could help that would be great.

Please update your forum profile with the Office version you are using- Windows 10 is your OS and of little use to us. Thanks.

Belinda, I am guessing but I would be pretty confident

The last 6 matches are the last 6 matches the team has played, so in the case of Liverpool the 6 largest row numbers featuring Liverpool.

Brighton Liverpool
Liverpool Wolves
Fulham Liverpool
Liverpool Tottenham
Crystal Palace Liverpool
Liverpool West Brom


The complication is that Liverpool can be home (the first Column) or away and so the results need to be interpreted as a win, if Brighton, Fulham and crystal palace are away wins, but the other 3 are home wins! similarly for losses.

It has also not been stated if the most recent should be first, or last. I have seen tables both ways!, but it should be easy to swap the columns at the end regardless

Originally Posted by davsth

Belinda, I am guessing but I would be pretty confident

The last 6 matches are the last 6 matches the team has played, so in the case of Liverpool the 6 largest row numbers featuring Liverpool.
...

Spot on. Sorry if it's a little confusing. The column thing is exactly what I am struggling with. It would be a lot easier if it was all in 1 column (e.g. Liverpool - Win).

Yes sorry I would like the most recent match first.

Thanks.

You won't need CSE in Office 365.

Originally Posted by Pete_UK

You can use this array* formula in cell X4:

=CHOOSE(MATCH($C4,INDEX(Data!$G$2:$L$400,LARGE(IF((Data!$A$2:$A$400=$C4)+(Data!$B$2:$B$400=$C4),Data!$M$2:$M$400),COLUMNS($X:X)),0),0),"W","D","L","W","D","L")

Pete thank you so much! This worked perfectly. I will break down this formula to see if I can understand it. The conditional formatting for the colours I can do myself.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Please try spill array at X4

=MID(INDEX(Data!$G$1:$L$1,INDEX(MMULT(N(SORT(FILTER(Data!$G$2:$M$400,(Data!$A$2:$A$400=C4)+(Data!$B$2:$B$400=C4)),7,-1)=C4),SEQUENCE(7)),SEQUENCE(,6))),6,1)

Originally Posted by speedychaz94

... I will break down this formula to see if I can understand it ...

I've attached the file which also shows how the formula evolved. I put this part of the formula (which is the array part, so needs CSE in my version of Excel) in AI4:

=LARGE(IF((Data!$A$2:$A$400=$C4)+(Data!$B$2:$B$400=$C4),Data!$M$2:$M$400),COLUMNS($X:X))

This looks to see if the team in column C (Liverpool in this case) exists in either column A or column B of the Data sheet (note the + is equivalent to OR with arrays) and then returns the match number from column M of the Data sheet, and then the LARGE function takes the largest value (i.e. the most recent game for that team). When it is copied across the COLUMNS term changes and returns 2, 3, 4 etc. in successive columns, so the formula returns the 2nd highest match number, then the 3rd highest, and so on.

I noticed that your Data sheet had already worked out who had won/drawn/lost and the name of the team being sought appears only once in columns G to L, so I figured if I can identify that group of cells for the row in question, then I can look for the name of the team and return its position. So, I then expanded the above formula to this (in AI5):

=MATCH($C5,INDEX(Data!$G$2:$L$400,LARGE(IF((Data!$A$2:$A$400=$C5)+(Data!$B$2:$B$400=$C5),Data!$M$2:$M$400),COLUMNS($X:X)),0),0)

The INDEX part (shown in red) picks out the 6 cells in column G to L of the Data sheet for the row given by the LARGE function, and then the MATCH part (in blue) returns the column number where the team name is found, and so this returns a number in the range from 1 to 6. (Obviously, this is now looking at Man United's results).

Finally, I could change those numbers to W D L, and I used the CHOOSE function to do this.

Hope this helps.

Pete

The INDEX part (shown in red) picks out the 6 cells in column G to L of the Data sheet for the row given by the LARGE function, and then the MATCH part (in blue) returns the column number where the team name is found, and so this returns a number in the range from 1 to 6. (Obviously, this is now looking at Man United's results).

Finally, I could change those numbers to H D L, and I used the CHOOSE function to do this.

Hope this helps.

Pete[/QUOTE]

A massive help thank you. Well explained. You clearly have lots of knowledge and experience to have been able to work that out. Maybe one day I will be at your level
Now my next challenge is to be able to figure out how to insert an arrow (up or down) to teams that have moved up or down in the league since the last fixture. A bonus would to be able to have a number next to that arrow with how many positions the team has moved in the league. Don't worry I don't expect you to work this out. I will try it out myself first.

Thanks again!