Excel- longest run

[Chivo]

Hi all,

I'm having trouble with the terminology never mind the forumla of what I am requiring to do. I basically have a spreadsheet of football scores. One of the columns, named "Result", has one of 3 values in it- W, D or L. What I am trying to do is automate a table that list the longest winning run, losing run, etc. for each time.

My questions is this: If I have the results column consists of W,D,W,W,W,L,D,L,W,W,L what function do I use to find that the longest run of wins was 3?

Thanks

Chivo

 

[SkipVought]

Hi,

You have to GROUP and then get a Count of Group using the PivotTable wizard.

Here's how to group...
[tt]
=IF(D2=D1,E1,E1+1)
[/tt]
assuming that column D has your WIN,LOSS,DRAW data and E1 is empty



Skip,
[sub]
[red][/red]
[/sub]

 

[Chivo]

Hi SkipVought,

Thanks for the reply. I see how that works and have been doing something similar but withour the pivot table. The problem is that with 20 different teams and 12 different types of runs it is getting really messy calculating the lot.

Cheers

Chivo

 

[SkipVought]

No one ever said life was easy! ;-)

Skip,
[sub]
[red][/red]
[/sub]