Posts by showtimesaints

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

This has been SOLVED.

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

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

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)))

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?

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

[xpost][/xpost]

crossposted: https://www.excelforum.com/exc…-5-games.html#post5527795

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

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!!

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?

[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!