Score line during soccer game

[lubinho]

Hi all,

i m trying to get the different scores during a soccer game based on goal times, but im having trouble to get the if conditions working all together

i attached the excel file with some manually values entered, but i need help to implement the formulas to extract the score lines during the game.


Can someone help me with the formulas on excel?

Regards

Anotação 2019-03-01 001944.jpg

[kersplash]

Try this in....
Q3;

Please Login or Register  to view this content.

R3;

Please Login or Register  to view this content.

S3;

Please Login or Register  to view this content.

T3;

Please Login or Register  to view this content.

Copy them down and then modify the formulas as needed across.

[lubinho]

Hi,

thanks for your quick reply

i tried as you suggested and it works for the first column, home score, but for the away result it doesn't work. I try to fix it but without success.
I understand the overall concept of the formula but didn't get the 7 and 8 value on it
Can you point me some clue on this?

Regards

3.jpg

[kersplash]

The match will return the column index location in the range C:P. So if it it matches in from 1 to 7 it is a home goal. If it matches in 8 to 14 it is an away goal. Hence the <8 for home goal and >7 for away goal.

I'm not sure why it's not working since I did test it out.

[Bo_Ry]

Please try at Q3 drag across and down

=IF(INT((COLUMNS($Q3:Q3)+1)/2)>COUNT($C3:$P3),"",COUNTIF(IF(Q$2="home",$C3:$I3,$J3:$P3),"<="&SMALL($C3:$P3,INT((COLUMNS($Q3:Q3)+1)/2))))

[lubinho]

Hi,

thanks for your help and explanation

[lubinho]

Please try at Q3 drag across and down

=IF(INT((COLUMNS($Q3:Q3)+1)/2)>COUNT($C3:$P3),"",COUNTIF(IF(Q$2="home",$C3:$I3,$J3:$P3),"<="&SMALL($C3:$P3,INT((COLUMNS($Q3:Q3)+1)/2))))

thanks for your help

is it possible to add the 0-0 score when it happen on a game? i tried to modify the formula but without sucess.

[Bo_Ry]

Please try at Q3

=IF((COUNT($C3:$P3)=0)*(COLUMNS($Q3:Q3)<3),0,IF(INT((COLUMNS($Q3:Q3)+1)/2)>COUNT($C3:$P3),"",COUNTIF(IF(Q$2="home",$C3:$I3,$J3:$P3),"<="&SMALL($C3:$P3,INT((COLUMNS($Q3:Q3)+1)/2)))))

[lubinho]

Please try at Q3

=IF((COUNT($C3:$P3)=0)*(COLUMNS($Q3:Q3)<3),0,IF(INT((COLUMNS($Q3:Q3)+1)/2)>COUNT($C3:$P3),"",COUNTIF(IF(Q$2="home",$C3:$I3,$J3:$P3),"<="&SMALL($C3:$P3,INT((COLUMNS($Q3:Q3)+1)/2)))))

Hi, thanks for your help. It works perfect