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 entered as an array formula with Ctrl-Shift-Enter

List is from A1 to A12

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

With your data I got an answer of 7 ... is this correct?

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.

Please only post to one NG. Thank you!

"Toppers" wrote:

Try this entered as an array formula with Ctrl-Shift-Enter

List is from A1 to A12

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

With your data I got an answer of 7 ... is this correct?

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.

=SUMPRODUCT(--((A2:A12)-(A1:A11)<0))-1

"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.

Sorry about the multiple posts. How do I get this array to work when there
are spaces in between the rows. Example,
8

9

10

10

11

2


"Toppers" wrote:

Please only post to one NG. Thank you!

"Toppers" wrote:

Try this entered as an array formula with Ctrl-Shift-Enter

List is from A1 to A12

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

With your data I got an answer of 7 ... is this correct?

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.

=SUMPRODUCT(--((A3:A13)-(A1:A11)<0))-1
Works if there are no zeroes.

"Rusty" wrote:

Sorry about the multiple posts. How do I get this array to work when there
are spaces in between the rows. Example,
8

9

10

10

11

2


"Toppers" wrote:

Please only post to one NG. Thank you!

"Toppers" wrote:

Try this entered as an array formula with Ctrl-Shift-Enter

List is from A1 to A12

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

With your data I got an answer of 7 ... is this correct?

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.