Getting the sum of previous boolean entries until the last true entry

[crimar]

Hi,

I have a spreadsheet where I use VBA to achieve an outcome, but want to remove the VBA if possible. There is one function I am having problems with. I have two columns, the first, column A contains dates. The second contains either a 'y' or an 'n' indicating whether an event took place or not. I want to be able to get the sum of previous 'n' rows from the current row, up until the previous 'y'.

Say I have this

1/1/2010 y

2/1/2010 n

3/1/2010 n

4/1/2010 y

5/1/2010 n

6/1/2010 n

7/1/2010 n

8/1/2010 y


Then I want to get a third column which would show something like this:


1/1/2010 y 0

2/1/2010 n 1

3/1/2010 n 2

4/1/2010 y 0

5/1/2010 n 1

6/1/2010 n 2

7/1/2010 n 3

8/1/2010 y 0


Is this possible? I have played around with a lot of possibilities but can't figure this one out. Any help is much appreciated,

Thanks

 

[Hui]

Crimar:


Assuming your data is in A1:B8

C1: =IF(B1="y",0,1)

C2: =IF(B2="y",0,C1+1)

Copy C2 down

 

[crimar]

Thanks Hui, that was so simple. I think I got locked into looking at complex functions I didn't even suspect the result could be so simple!

 

[keymaster]

@Hui.. wow that is truly beautiful and simple solution for a seemingly complex problem like this Hats off...

 

[Hui]

Thanx Chandoo

I actually like

C2: =(C1+1)*(B2="n")