# Posts by showtimesaints

• ## Last 5 games scoring average

I got so excited I forgot to show the answer. Sorry about that! I attached the workbook.

• ## Last 5 games scoring average

This has been SOLVED.

• ## Last 5 games scoring average

Just bumping this up to see if anyone can help, thank you.

• ## Last 5 games scoring average

I've found an alternative on how to calculate goals for, the formula is,

=SUM(IF(A:B=G2,IF(ROW(A:B)>=LARGE(IF(A:B=G2,ROW(A:B)),1),C:D)))

This here gives me the goals for in the last game. But is there anyway to calcuate the goals against in the last game? Im having trouble to finding the opposite.

Thanks

• ## Wins in the Last 5

Good morning thanks for bringing this up. I have attached the workbook with the resolution. The formula is as follows,

For wins,

=SUMPRODUCT((\$B\$2:\$C\$500=\$L2)*(\$H\$2:\$I\$500="W")*(ROW(\$B\$2:\$C\$500)>=AGGREGATE(14,6,ROW(\$B\$2:\$C\$500)/(\$B\$2:\$C\$500=\$L2),5)))

For losses,

=SUMPRODUCT((\$B\$2:\$C\$500=\$L2)*(\$H\$2:\$I\$500="L")*(ROW(\$B\$2:\$C\$500)>=AGGREGATE(14,6,ROW(\$B\$2:\$C\$500)/(\$B\$2:\$C\$500=\$L2),5)))

• ## Last 5 games scoring average

Ok so I've noticed a problem with this formula and was wondering for some help. When I tried to do this for soccer it gave me an error. Because there are many games with 0 goals I was noticing that it was not adding up correctly and I would get an error. I attached another book with the last set of games of the season. I only did Last 1 game as reference so you can see the error. When I did this for Last 5, the teams that had 0 goals for or 0 goals against it was not adding it up correctly.

Any chance to correct this?

• ## Wins in the Last 5

Hello, this has been resolved. A formula was found from another site. Sorry about the confusion.

• ## Wins in the Last 5

[xpost][/xpost]

Good morning, the last post the help was amazing, thank you very much for that!! Was hoping with help for 2 more things, I want to find the following,

-ATS(against the spread) wins and ATS losses in the last 5 games.

-ATS Streak

I have a formula inputed in the workbook for the Texans but i'm not sure if that is correct. As games are played I add the data to the bottom of the columns.

Any help again will be so much. appreciated.

Thanks

• ## Last 5 games scoring average

WOW! that was awesome, thank you very much for this! it worked. the file you sent I will use that and add it to my worksheet. I'm still trying to learn excel and thankfully coming across someone as your self who has helped out has made it easier, thanks!!

• ## Last 5 games scoring average

Thanks for getting back. Perhaps if I break it up and use Home and Away columns then I can break it down. But the formula I am using is

{=AVERAGE(IF(Sheet1!\$A\$2:\$B\$889=I2,IF(ROW(Sheet1!\$A\$2:\$B\$889)>=LARGE(IF(Sheet1!\$A\$2:\$B\$889=I2,ROW(Sheet1!\$A\$2:\$B\$889)),5),Sheet1!\$C\$2:\$D\$889)))}

Where I2 is the team that I am searching for (ie, Angels). But how do I write the formula to find Angels opponents scoring?

• ## Last 5 games scoring average

[xpost][/xpost]

https://www.excelforum.com/exc…t-streak.html#post5538314

Hello, was hoping for help, it would be so greatly appreciated. Im trying to figure out how to run in excel a formula that outputs the average scoring against per team in sports in the teams last 5 games and last 10 games.. For example, I want to see the Rays opponents scoring is in the last 5 and 10 games. I have the formula for scoring FOR but can't seem to figure out how to run scoring AGAINST.

I've attached an excel sheet and its columns K and M that im trying to figure out.

Thank you very much if someone can help!