Occurance Counting

[Rusty]

I really need some help for work. I have a column that contains various
numeric values and I want to count the number of times it changes from one
value to another, but there are spaces in between the cells. Heres a simple
example,
8
8
8
15
15
18
17
17
8
3
17
15
So, is there a formula or macro, something that can sum up the number of
times the number changes?
I would really appreciate the help.

 

[pdberger]

Rusty --

Not the world's most elegant solution, but:
A B
1 8
2 8 =IF(A2<>A1,1,0)
3 8
4 15
....
=SUM(B2:Bxxx)

HTH

 

[Rusty]

Thank you. I tried it and it does work but with the empty cells in between
the rows it is not calculating correctly. Is there a way to correct that?

 

[Gord Dibben]

Rusty

What would happen to the sheet if you removed the empty cells?

Select column A and F5>Special>Blanks>OK

Edit>Delete>Shift cells up.

OR Delete>Entire Row.


Gord Dibben MS Excel MVP

 

[Toppers]

Try entered as array formula with Ctrl-Shift-Enter: data is A1 to A12

=SUM(IF(ISNUMBER(A1:A12),IF(A1:A12<>A2:A13,1,0),0))-1

HTH

 

[Ron Coderre]

Try this ARRAY FORMULA:

For a list of numbers, or blanks, in A2:A11.
A2 must be a number

I think this formula returns the count of number value changes. Also, it
allows for the last items in the list to be blank:

=SUM(--(LOOKUP(SMALL(IF(ISNUMBER(A2:INDEX(A2:A11,MATCH(10^99,A2:A11))),ROW(A2:INDEX(A2:A11,MATCH(10^99,A2:A11)))),ROW(A1:INDEX(A1:INDEX(A2:A11,MATCH(10^99,A2:A11)),COUNT(A2:INDEX(A2:A11,MATCH(10^99,A2:A11)))-1))),ROW(A2:INDEX(A2:A11,MATCH(10^99,A2:A11))),A2:INDEX(A2:A11,MATCH(10^99,A2:A11)))<>LOOKUP(SMALL(IF(ISNUMBER(A3:INDEX(A3:A11,MATCH(10^99,A3:A11))),ROW(A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))),ROW(A1:INDEX(A1:INDEX(A3:A11,MATCH(10^99,A3:A11)),COUNT(A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))))),ROW(A3:INDEX(A3:A11,MATCH(10^99,A3:A11))),A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))))

Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: Since text wrap will surely impact the display, there are NO spaces
in that formula.

(BTW....That's one of the ugliest formula I ever wrote)

Hopefully, somebody will cull that down to something more elegant.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Rusty]

Thank you Ron! This one worked perfect!

Try this ARRAY FORMULA:

For a list of numbers, or blanks, in A2:A11.
A2 must be a number

I think this formula returns the count of number value changes. Also, it
allows for the last items in the list to be blank:

=SUM(--(LOOKUP(SMALL(IF(ISNUMBER(A2:INDEX(A2:A11,MATCH(10^99,A2:A11))),ROW(A2:INDEX(A2:A11,MATCH(10^99,A2:A11)))),ROW(A1:INDEX(A1:INDEX(A2:A11,MATCH(10^99,A2:A11)),COUNT(A2:INDEX(A2:A11,MATCH(10^99,A2:A11)))-1))),ROW(A2:INDEX(A2:A11,MATCH(10^99,A2:A11))),A2:INDEX(A2:A11,MATCH(10^99,A2:A11)))<>LOOKUP(SMALL(IF(ISNUMBER(A3:INDEX(A3:A11,MATCH(10^99,A3:A11))),ROW(A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))),ROW(A1:INDEX(A1:INDEX(A3:A11,MATCH(10^99,A3:A11)),COUNT(A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))))),ROW(A3:INDEX(A3:A11,MATCH(10^99,A3:A11))),A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))))

Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: Since text wrap will surely impact the display, there are NO spaces
in that formula.

(BTW....That's one of the ugliest formula I ever wrote)

Hopefully, somebody will cull that down to something more elegant.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

I really need some help for work. I have a column that contains various
numeric values and I want to count the number of times it changes from one
value to another, but there are spaces in between the cells. Heres a simple
example,
8
8
8
15
15
18
17
17
8
3
17
15
So, is there a formula or macro, something that can sum up the number of
times the number changes?
I would really appreciate the help.