Counting Games in Rows

[rbpd5015]

What I am trying to do is find the AVERAGE length it took to get a victory per opponent.

Fox example
NYY Vs TOR
played 4-1-08 through 4-3-08
it took the Yankees 1 game to gain a victory
NYY Vs TB
played 4-5-08 through 4-7-08
it took the Yankees 2 games to gain a victory

so the average for the first two series was 1.5

Anybody got a formula to do this?

Matt

NEW YORK YANKEES VS ...

COLUMN A, B, C, D
04/01/2008 TOR W 3-2
04/02/2008 TOR L 2-5
04/03/2008 TOR W 3-2
04/05/2008 TB L 3-6
04/06/2008 TB W 2-0
04/07/2008 TB W 6-1
04/08/2008 @ KC L 2-5
04/09/2008 @ KC L 0-4
04/10/2008 @ KC W 6-1
04/11/2008 @ BOS W 4-1
04/12/2008 @ BOS L 3-4
04/13/2008 @ BOS L 5-8

 

[facethegod]

With your current layout try....


=SUM((MATCH(B1:B12&"W",B1:B12&C1:C12,0)-ROW(B1:B12)+ROW(B1))*(COUNTIF(OFFSET(B1:B12,,,ROW(B1:B12)-ROW(B1)+1,1),B1:B12)=1)/SUM(1/COUNTIF(B1:B12,B1:B12)))

HTH

 

[rbpd5015]

HTH,

I get #Name!

???

 

[facethegod]

rbpd5015,

I forgot to mention that my formula is an array formula, so it needs to be entered with control+shift+enter, not just enter.

HTH,

I get #Name!

???

Make sure you copy the formula exactly as i have posted it. You can update the ranges to suit your exact ranges. I have copied the formula with your data layout and i'm not getting any errors.

 

[DonkeyOte]

x-posted at ExcelForum.com http://www.excelforum.com/excel-general/676757-counting-games-in-rows.html

 

[rbpd5015]

Hey dOnkey,

Not getting a responses there so came here.


Matt