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
It's OK, I solved it.
Thanks anyway.
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!!
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,
