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:

Months:	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
Ys &Ns	Y	Y	Y	Y	Y	Y	N	Y	N	N	Y	Y	Y	Y	Y	Y	Y	Y	N	N	N	Y	Y	Y	Y
Backward runs of Ys	1	2	3	4	5	6		1			1	2	3	4	5	6	7	8				1	2	3	4

<tbody>
</tbody>

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