Count change in state

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith Rathband - 14 Jun 2007 15:00 GMT

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.

Reply to this Message

Dave Peterson - 14 Jun 2007 15:07 GMT

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

> I have a load of values in a spreadsheet which are either true or false.
>
[quoted text clipped - 16 lines]
>
> Thanks in advance.

Signature

Dave Peterson

Reply to this Message

Keith Rathband - 14 Jun 2007 15:37 GMT

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?

> I count 2 in your test data.
>
[quoted text clipped - 33 lines]
> >
> > Thanks in advance.

Reply to this Message

Dave Peterson - 14 Jun 2007 15:40 GMT

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)

> Thanks,
>
[quoted text clipped - 49 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Keith Rathband - 14 Jun 2007 16:00 GMT

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 ?

> Are you just trying to count the first cell in the range if it's true?
>
[quoted text clipped - 53 lines]
> > >
> > > Dave Peterson

Reply to this Message

Keith Rathband - 14 Jun 2007 16:40 GMT

Sorted it now - thanks.

> The file i have is the output of a BMS system that logs an input and outputs
> to a CSV file.
[quoted text clipped - 66 lines]
> > > >
> > > > Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 14 Jun 2007 17:07 GMT

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

Does this formula do that?

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

Rick

Reply to this Message