Count change in state

  • Thread starter Thread starter Keith Rathband
  • Start date Start date
K

Keith Rathband

I have a load of values in a spreadsheet which are either true or false.

I need to count how many times it changes from false to true, bearing in
mind several true values may follow each other - before returning to false.

ie

TRUE
FALSE
FALSE
TRUE
TRUE
TRUE
FALSE
FALSE
TRUE


the result would be 3 - ie it changed from FALSE to TRUE 3 times.

Thanks in advance.
 
I count 2 in your test data.

And I counted 2 using this formula (with your data in A1:A9:

=SUMPRODUCT(--(A1:A8=FALSE),--(A2:A9=TRUE))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Thanks,

mmm - your correct it is 2.

Perhaps i could try a different way..

How about counting how many cells are = true, but ignore cells in which the
cell above is also = true, is this possible?
 
Are you just trying to count the first cell in the range if it's true?

=SUMPRODUCT(--(A1:A8=FALSE),--(A2:A9=TRUE))+(A1=TRUE)
 
The file i have is the output of a BMS system that logs an input and outputs
to a CSV file.

All i need to be able to do is work out how many time in input changed from
false to true.

My file contains 3000 values - i cant seem to get that formula to work on
anything but my small example ?
 
Sorted it now - thanks.

Keith Rathband said:
The file i have is the output of a BMS system that logs an input and outputs
to a CSV file.

All i need to be able to do is work out how many time in input changed from
false to true.

My file contains 3000 values - i cant seem to get that formula to work on
anything but my small example ?

Dave Peterson said:
Are you just trying to count the first cell in the range if it's true?

=SUMPRODUCT(--(A1:A8=FALSE),--(A2:A9=TRUE))+(A1=TRUE)
Click to expand...
which
the
Click to expand...
trues
Click to expand...
 
All i need to be able to do is work out how many time in input
changed from false to true.
Click to expand...

Does this formula do that?

=SUMPRODUCT(--(A2:A99<>A1:A98),--(A2:A99=TRUE))

Rick
 
Back
Top