Counting consecutive events (not ordered)

**timrishi** · 01-05-2013, 05:33 PM

Hi

I am trying to count the number of times a team has scored consecutive occasions, but struggling to generate a formula as the data is not ordered. On the spreadsheet attached, Cell H28 would be looking for the number of consecutive times "Manchester United" appeared in the cells above B28 in instances where D28 is =1.

Would be extremely grateful if anyone had any suggestions?

Thanks.

PL results.xlsx

**olasa** · 01-05-2013, 06:47 PM

Is this right?
//Ola

Cell H382: =SUM(($B$2:$B382=$B382)*($A$2:$A382>MAX(IF(($B$2:$B382=$B382)*($D$2:$D382=0);$A$2:$A382))))
--> 5
Confirm the typed formula with Ctrl+Shift+Enter

**timrishi** · 01-05-2013, 08:18 PM

Hi Ola

Thanks for your reply. Unfortunetly there is an error in the code, it appears to flag the following as the source of the error.

=SUM(($B$2:$B382=$B382)*($A$2:$A382>MAX(IF(($B$2:$B382=$B382)*($D$2:$D382=0);$A$2:$A382))))

**FDibbins** · 01-05-2013, 08:19 PM

try and replace ; with , it's probably a regional thing

**dredwolf** · 01-05-2013, 08:20 PM

change the semicolon to a comma and try that

**timrishi** · 01-05-2013, 08:25 PM

Awesome, thanks very much.

**olasa** · 01-05-2013, 08:34 PM

Happy to hear.
Please mark the problem as solved (see footnote)
//Ola

**timrishi** · 01-06-2013, 05:57 AM

Hi Ola.

The formula works perfectly for the first column, but I’m having trouble understanding the logic and thus extending to the other three. Would you be able to help?

Column I is doing the same as column H but it is looking for the consecutive games the home team (when the team in column B appears in column E) has scored in away from home (column G).

Column J is looking at how many games the home team (column B) has conceded in at home (column G for the same row entry as column B).

Column K is looking at how many games the home team (when team in column B appears in column E) has conceded when they are away from home (Column D for the same row).

If you be amazing if you were able to give some further help on this.

Thanks,
Tim.