# Counting Games in Rows

#### rbpd5015

##### New Member
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

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

=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

HTH,

I get #Name!

???

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.

Hey dOnkey,

Not getting a responses there so came here.

Matt

Replies
4
Views
193
Replies
1
Views
679
Replies
2
Views
323
Replies
1
Views
380
Replies
6
Views
291

1,196,248
Messages
6,014,223
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back