Football form

  • Hi all

    I would like to create a formula that counts in a way the form of the teams. For example, in the attachment, on the second row we see the Liverpool-Norwich match that ended 4-1. Each column H:AA, refers to one of the teams and at each row the goal difference for each of their match is calculated, e.g. in row 2, Liverpool's column has 3 and Norwich's column has -3.After 6 matchdays, we go to row 62 and the match Sheffield United-Liverpool. Sheffield's goal difference -as seen on Sheffield's column- is 0+1-1+0-1+2 =1 and Liverpool's is 3+1+2+3+2+1=12. The formula I am asking for, starting from cell G62, will be calculating the goal difference of the home team's last 6 matches minus the goal difference of the away team's last 6 matches. For the "Sheffield United-Liverpool" example, the sum would be -11.

    Columns H:AA were my idea, thought it might be easier, if anyone can come to a solution from a different way, they are welcome.

    Kind regards,



    • PL 19-20.xlsx

      (74.41 kB, downloaded 102 times, last: )
  • So, don't laugh but for row 62 i got what I wanted with:


    There MUST be an easier and less time-consuming way!!

  • It's usuall to post the solution even if you solved it yourself It may help someone else

    Good point, would do it if someone was interested. Solution is on G:G. It uses data from I:AB, which is an array formula that goes


    If anyone can shorten that even more, feel free to do so



    • L1.xlsx

      (1.14 MB, downloaded 114 times, last: )