Count Changes In An Array Of Data

[peterhinton]

so I have some data that records grades of a product in B2:B54

I want to count the number of times the grade has changed,
not the number of different grade it has been (unique values)

example may look like:

A A A A B B B B C C C B B B B C C C......and so on
(range of grades = A-F)

This would equal to 4 changes,

any ideas how to construct such a formula ?

 

[hurgadion]

Hi,
try maybe this (array formula):

=SUM(IF((B3:B55<>"")*(B2:B54<>B3:B55);1;0))

Best regards

 

[steve the fish]

Maybe:

=SUMPRODUCT(--($B$2:$B$20<>OFFSET($B$2:$B$20,1,0)),--(OFFSET($B$2:$B$20,1,0)<>""))

 

[steve the fish]

This caters for any value in B21

=SUMPRODUCT(--($B$2:$B$20<>OFFSET($B$2:$B$20,1,0)))-($B$20<>$B$21)

 

[mikerickson]

Perhaps

=SUMPRODUCT(--(B2:B53<>B3:B54))

 

[peterhinton]

Thank You Sir Fish, works perfect,

Thanks for the effort hurgadion, although I could not get this to work, even when changing ; to ,

 

[hurgadion]

OK,
I'm sorry... I have to remember about this... I have Local Formulas with ","... My formula works if B54 is empty... My Solution is similar to mikerickson One...