Badly Inefficient formula

[GrandPaw]

I have column "A" which indicates whether the game played was won or lost (Data is "W" or "L")(162 Rows). Column "B" indicates whether the team "Shut-Out" their opponent (Data is "Y" or "N") (Same 162 Rows).
I am trying to write a formula that calculates if the game was a "Shut-out" (Y in column "B") How many games did it take after the "Shut-Out" to get the next win. Example: Had a "Shut-Out" on Game #3 (Row 3), Lost the following game on row 4 and the following game on row 5, but won the next game on row 6. I am looking to get a 3 indicating how many games they had to play to get a win in row 3, where the actual "Shut-Out occured"

Here is what I wrote, but it is very unweildy and inefficient:
=IF(B3="N"," ",IF($A4="W",1,IF($A5="W",2,IF($A6="W",3,IF($A7="W",4,IF($A8="W",5,IF($A9="W",6,IF($A10="W",7,IF($A11="W",8,IF($A12="W",9,"9+ or EOS"))))))))))

Since there are 30 teams with 162 games each, my formula has made the workbook huge. I am sure there is a much better way to do this.

Thanks for your help

GrandPaw

 

[gardnertoo]

I've got two formula, depending on one question: are you trying to see how long it takes to recover from SUFFERING a shutout, or do you not care which side of the shutout your team was on?

Cell C3 formula for games to next win after participating in a shutout, on either the winning or losing side:

=IF(B3="N","",IF(COUNTIF(A4:A$165,"W")=0,"EOS",MATCH("W",A4:A$165,0)))

Cell C3 formula for games to next win only if we were the losers in the shutout:

=IF(OR(A3="W",B3="N"),"",IF(COUNTIF(A4:A$165,"W")=0,"EOS",MATCH("W",A4:A$165,0)))

Copy the formula down column C