Counting a run of occurances going backwards

[DavidSCowan]

I need to count backwards the runs of occurances of consectutive Ys (yes's) which are interupted by Ns.

So for example we have:
[TABLE="width: 603"]
<tbody>[TR]
[TD]Months:
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]16
[/TD]
[TD="align: right"]17
[/TD]
[TD="align: right"]18
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]21
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]25
[/TD]
[/TR]
[TR]
[TD]Ys &Ns
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]Backward runs of Ys
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</tbody>[/TABLE]

The end of a run of Ys comes when the next cell is an 'N'. e.g. the run of 4 Ys starting in month 25 comes to an end with the N in monthl 21.

Using Offset and Match I know how to do this going forwards but I can't do it going backwards!

Can anyone help please (I am using Excel 2007)

 

[Marcelo Branco]

Not sure if i understand what you need...
Do you want to put a formula in Z3 (Month 25) and copy to the left?

If so, assuming months (1, 2 ....25) in B1:Z1, maybe

Z3
=IF(Z2="Y",COUNTIF(INDEX($B$2:Y2,IFERROR(LOOKUP(2,1/($B$2:Y2="N"),COLUMN($B$2:Y2)-COLUMN($B$2)+1),1)):Y2,"Y")+1,"")
drag to left

Otherwise, you can put in B3
=IF(B2="Y",N(A3)+1,"")
drag to right

M.

 

[DavidSCowan]

Thank you very much for this. jSorry for the delay in replying I thought I sent one a couple of hours ago.

You understood what I was asking for perfectly (i.e. copying to the left) and the formula works a treat.

Working through your formula could you please explain 1/($B$2:Y2="N") in (LOOKUP(2,1/($B$2:Y2="N"),COLUMN($B$2:Y2)-COLUMN($B$2)+1)? What does the forward slash mean? I have never seen a forward slash used inside a function before (although I have seen it used inside Lookup and not understood it)

Great stuff!

And thanks again.

David

 

[Marcelo Branco]

You are welcome and thanks for the feedback!

Aladin Akyurek provided a wonderful explanation about this kind of use of LOOKUP in (see post #9)

http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html

M.

 

[DavidSCowan]

Hi Marcelo

Just a note to thank you again for your solution yesterday and for directing me to Aladin Akyukek's classic thread - really helpful.

David