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!

    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?

    Try,


    1] "RF Last 5 Games", in cell J2 CSE formula copied down :

    =AVERAGE(INDEX(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0)),ROW($A$1:$A$5))))))


    2] "RA Last 5 Games", in cell K2 CSE formula copied down :

    =AVERAGE(INDEX(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0)),ROW($A$1:$A$5))))))


    3] "RF Last 10 Games", in cell L2 CSE formula copied down :

    =AVERAGE(INDEX(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0)),ROW($A$1:$A$10))))))


    4] "RA Last10 Games", in cell M2 CSE formula copied down :

    =AVERAGE(INDEX(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0)),ROW($A$1:$A$10))))))



    Attached file :


    Average last 5 and 10 scoring.xlsx

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

    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?

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login