Count Last10 game results such that the team is the same

[nicolaivinther]

Hi,

I am interested in finding a way to calculate the last 10 results from all games a team is involved in. In the attached sheet I has taken Florida as an example, but the team will differ from game to game.
The example I made is showing the last 10 results from Florida Panthers before 26th october. I want this data for each match for each team.
So one cell should just count the last 10 "Over" and the other cell should count the last 10 "Under" for the away team in the given game.
Be aware that some game is neither Over or Under. They should still be counted in the last 10, but just as a zero.
The last 10 games don't have to be as an away team, both home and away games should be counted in the last 10.

I want the data for the away team but also the home team. However, if I see a way to do it for the Away team, I think i can figure out the home team as well

I hope it makes sense. Take a look at my attached sheet below
If it's impossible to do in one cell, it will okay to use more than one cell.

Thank you!

ExcelForum.xlsx

Nicolai

[Rioran]

Nicolay, good time of day.

Would you mind to use a VBA code? Example is in the attachment. Just change team name with a droplist and statistics will be shown automatically. Works properly when your data sorted.

But I'm not quite sure that I understanded you right, because in your example you've highlighted 11 games and as result you entered 3 against 6... So now my code just skips zero games. Also I've added "Non zero games observed" statistics just for case when your data is not enough to represent 10 games.

[nicolaivinther]

Hi,

I am from Denmark so it's 10 am here
Your sheet is smart, but it wasn't quite what I am seeking for. If i could avoid VBA it would be appreciated.

I have 9000 rows, so I just need the data to be pasted out for each game.

To you Q. The 11th game i marked is the one that should have show the data for the past 10 games So in Floridas 11th games, the Over and Under section should count the past 10 games with Florida.

I need all games to do that, and I have 9000 in total.

[Rioran]

Nicolai,

I think I know now what must be done. Formula solution for this task will require too much time from me, sorry. But may be something like User Defined Function will do? In attached file you will find VBA code formula. First argument - is a team name we are seeking for and the second one - is a column, from which we shall agregate results of last 10 games.

Best wishes,
Roman

[nicolaivinther]

THank you.
I will wait a bit to see if anyone else has other ways to do it.
If not, I will try to go deeper in to your way. Thanks a lot for your help.

[nicolaivinther]

I found a way to do it myself.
Sorry for all the trouble.
Thank you Rioran for your help.

[canapone]

Hi,

a possible approach using an helper column


In H3 to be copied along data (H3:H129)


=(COUNTIF(B3:$C$1000,$I$2)<=$L$2)*O(B3=$I$2,C3=$I$2)

In J2 (correct answer should be 2 for Florida)

=SUMPRODUCT(ISNUMBER(D3:D129)*H3:H129)

In K2

=SUMPRODUCT(ISNUMBER(E3:E129)*H3:H129)


Hope it helps

In the attachment I using "white text" to hide helper formulae

[daddylonglegs]

Hello Nicolai,

Without helper columns, you can use this "array formula" in F123

=SUM(IF(($B$3:$B122=$B123)+($C$3:$C122=$B123),IF($A$3:$A122>=LARGE(IF(($B$3:$B122=$B123)+($C$3:$C122=$B123),$A$3:$A122),10),D$3:D122)))

confirm with CTRL+SHIFT+ENTER and drag to G123

You can also drag the formula up or down the column.....but note that you'll get errors if there are not 10 previous results for that team....if you want all previous results considered in that case then change to the following version:

=SUM(IF(($B$3:$B122=$B123)+($C$3:$C122=$B123),IF($A$3:$A122>=IFERROR(LARGE(IF(($B$3:$B122=$B123)+($C$3:$C122=$B123),$A$3:$A122),10),0),D$3:D122)))

Note: I'm assuming that teams won't play twice on the same day, is that right?