Counting sequences

**holp** · 04-11-2013, 12:30 AM

Hi all

You may advice how to solve the following question:

I have several columns that contain sequences of numbers (marks). These numbers consist of 1, 2 and 3; each sequence always starts with 1, whereas the rest is not fixed. In between sequences there can occasionally occur 0, but not necessarily.
I now would want to have for each of this column an additional column that counts these numbers as follows: each sequence (always starting with 1) should be counted as one `trial` continuously.

I attached an example with an INPUT and an OUTPUT file. This illustrated how the results should look like.

Many thanks in advance,
Lisa

**FDibbins** · 04-11-2013, 12:38 AM

HI, I dont see your logic there

Could you explain how you arrive at your answers?

**rylo** · 04-11-2013, 12:42 AM

Hi

C2: =IF(A2=1,1,0)
D2: =IF(B2=1,1,0)
C3: =IF(A3=0,0,IF(AND(A3=1,A3<>A2),MAX($C$2:C2)+1,C2))
D3: =IF(B3=0,0,IF(AND(B3=1,B3<>B2),MAX($D$2:D2)+1,D2))

Copy from C3:C3 down as required.

HTH

rylo

**AndyLitch** · 04-11-2013, 12:54 AM

Example_Trial.xlsx

Here you go.. Columns E:F calculate the series number using:-
=IF(A2=0,0,IF(AND(A2=1,A1<>1),1+MAX(OFFSET(E$1,1,0,COUNT($A1:$A$2))),E1))

@FDibbins The way i see it. column C numbers each new col a series sequentially.. D does same for col b

**holp** · 04-11-2013, 01:07 AM

Many thanks for all the SUPER-FAST replays! -)

thank you very much!
lisa

**AndyLitch** · 04-11-2013, 01:09 AM

You're welcome..
Check it's what you're looking for and if so, could you please close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

And if you're happy with my work please select the * bottom left.