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.

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" wrote:

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.

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?

"pdberger" wrote:

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" wrote:

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.

Rusty

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

Select column A and F5SpecialBlanksOK

EditDeleteShift cells up.

OR DeleteEntire Row.


Gord Dibben MS Excel MVP

On Sun, 9 Jul 2006 08:43:01 -0700, Rusty
wrote:

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?

"pdberger" wrote:

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" wrote:

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.

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

"Rusty" wrote:

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.

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:A 11)))),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(I F(ISNUMBER(A3:INDEX(A3:A11,MATCH(10^99,A3:A11))),R OW(A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))),ROW(A1:I NDEX(A1:INDEX(A3:A11,MATCH(10^99,A3:A11)),COUNT(A3 :INDEX(A3:A11,MATCH(10^99,A3:A11)))))),ROW(A3:INDE X(A3:A11,MATCH(10^99,A3:A11))),A3:INDEX(A3:A11,MAT CH(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" wrote:

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.

Thank you Ron! This one worked perfect!

"Ron Coderre" wrote:

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:A 11)))),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(I F(ISNUMBER(A3:INDEX(A3:A11,MATCH(10^99,A3:A11))),R OW(A3:INDEX(A3:A11,MATCH(10^99,A3:A11)))),ROW(A1:I NDEX(A1:INDEX(A3:A11,MATCH(10^99,A3:A11)),COUNT(A3 :INDEX(A3:A11,MATCH(10^99,A3:A11)))))),ROW(A3:INDE X(A3:A11,MATCH(10^99,A3:A11))),A3:INDEX(A3:A11,MAT CH(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" wrote:

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.