# 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,

## Files

• So, don't laugh but for row 62 i got what I wanted with:

=SUMPRODUCT(HLOOKUP(\$B62;\$H\$1:\$AA61;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61}))-SUMPRODUCT(HLOOKUP(\$C62;\$H\$1:\$AA61;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61}))

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

• It's OK, I solved it.

Thanks anyway.

• 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

=SUM(INDEX(AC\$3:AC52;LARGE(IF(AC\$3:AC52<>"";ROW(AC\$3:AC52)-ROW(AC\$3)+1);5)):AC52)

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

Thanks

## Participate now!

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