Resolved >>> Number of consecutive first values

Hello owainl,

I presume you want to look at both home and away matches together, your formula is only looking at home games?

Starting with the second part first.....

I think you'd have difficulty doing this with regular formulas because you need a formula that will concatenate a range, I think. You could try downloading morefunc add-in and using MCONCAT function like this

=LEFT(MCONCAT(IF(B2:B13="ars",C2:C13,"")&IF(E2:E13="ars",F2:F13,"")),5)

confirmed with CTRL+SHIFT+ENTER

For Arsenal this will give WLWD because there are only 4 games shown

For the first part I presume the result for Tottenham would be L1 and for Man Utd W4?

OK, I'm at work right now so I'll try Morefunc when I get home - many thanks nonetheless. I would actually like a breakdown of home and away results (as well as the overall value that you've kindly provided). I can see how this would be done with the MCONCAT function but sticking to my primary concern (i.e. winning/drawing/losing streaks), how would I do this?

And yes, you're correct in presuming the results for Tottenham and Man U.

Thanks for your help - much appreciated.

OK,

I'm sure you can do the first part with "native" excel formulas but trying to take into account all possibilities, team has no home games, or no away games etc. will make it a rather involved formula.

If morefunc works for you I'd suggest this sort of setup to answer both your queries. In H2 use this formula to give all results for "ARS"

=MCONCAT(IF(B2:B13="ars",C2:C13,"")&IF(E2:E13 ="ars",F2:F13,""))

confirmed with CTRL+SHIFT+ENTER

If you only want to show the last 5 results then in H3

=LEFT(H2,5)

Now to get the latest "run" of results, using INTVECTOR function also from morefunc, in H4

=LEFT(H2)&MATCH(TRUE,LEFT(H2)<>MID(H2&"x",INTVECTOR(LEN(H2),1)+1,1),0)

also confirmed with CTRL+SHIFT+ENTER

If you don't want to see H2 custom format that cell as ;;;

Note: I think you have an error in your data. You show one result as Tottenham 3 Fulham 0. As a long-standing follower of the "mighty" Spurs I can assure you that such a result, clean sheet, winning by 3, is impossible.

All I can say is "Bravo!" Does exactly what I wanted - I'll be sure to check out the workings of the formula when I'm sober.

ANd of course, goodbye Mr Jol! 2-1 Getafe, well I never...