counting occurances

[cwhite]

I'm new to excel so please bare with me. I have used lotus in the past and I'm having a hard time converting over. My question is this.

0
2
3
4
1
5
6
4
1
ect ....

In the above col I would like to use a formula that would count the number of times a number skipped or not met then when it is found copy the results to a different column and continue till it finds a blank cell at the end of the column.

such as .. #1 would be skipped 4 times then 3 times. I would like to copy the results to a different column in the order that it happens till a blank cell is found.

I hope this possible. I know it is in lotus but for the life of me I can't get excel to do it. Thank You in advance.

Chris White

[Aladin Akyurek]

Chris,

I don't think I understand the question.

What formula did/would you use in Lotus?

And, What are the expected results for the sample that you provided for 1, 2, 3,...n?

Aladin

[cwhite]

On 2002-05-06 15:23, Aladin Akyurek wrote:
Chris,

I don't think I understand the question.

What formula did/would you use in Lotus?

And, What are the expected results for the sample that you provided for 1, 2, 3,...n?

Aladin

[cwhite]

On 2002-05-06 15:23, Aladin Akyurek wrote:
Chris,

I don't think I understand the question.

What formula did/would you use in Lotus?

And, What are the expected results for the sample that you provided for 1, 2, 3,...n?

Aladin

I used {IF @CELLPOINTER("CONTENTS")=1}{R 5}~1~{L 5}{D 1}{BRANCH 1ST}
This loop would continue till it found a blank cell. The branch was used for a counter to keep track the # of times (in this case #1) was skipped. When it did find the #1 it would then zero out the counter and keep repeating till it found a blank cell

Cj

[Brian]

I don't fully understand the question either. You can obviously write a robust program in VBA.

As far as formulas go, I am not aware of any method to do loops per se.

Take a look at array formulas, sumif, countif, the various lookup funcitons, and the conditional sum wizard add-in.

With array formulas or countif, you could count how many times each number occurs in the list.

You can break up the sequential strings with simply by placing an if statement in the next column B2 = If (A2 - A1 = 1, 1, 0) ie. 1 = sequence ok' 0 = Break In Sequence.

For further help, you'll need to give both the input and output desired:

ex.
COLA = INPUT
1
2
3
7
8
9
1
2

COLB = OUTPUT? EASY:
#of 1s = 2
#of 2s = 2

... but then I dont's understand what or where you copy if the sequence breaks.

COL C = OUTPUT?
1
1,2
1,2,3
7
7,8
7,8,9
1
1,2

EASY: (Col C2 = IF (B2=1, C2 = C1 & "," & C2, C2 = C1) use the if statement and concatenate each additional row until "0" is reached, then reset.)


COL D OUTPUT:


1,2,3


7,8,9

1,2

EASY: D2 = IF (B3 = 0, C2, "")

COL E OUTPUT:
1,2,3
7,8,9
1,2

Use the array formula to create a list of unique items in the list (avialable in other posts and http://www.j-walk.com/ss or MrExcel)

(The above can be compressed into less formulas/columns.)

Hope that give some direction, though I 'm not sure that it was the answer to your problem.


Regards,

Brian