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

Keith Rathband wrote:
>
> 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.

--

Dave Peterson

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?


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> Keith Rathband wrote:
> >
> > 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.
>
> --
>
> Dave Peterson

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)



Keith Rathband wrote:
>
> 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?
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > Keith Rathband wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
>
>
> Keith Rathband wrote:
> >
> > 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?
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
> > > Keith Rathband wrote:
> > > >
> > > > 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.
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

Sorted it now - thanks.

"Keith Rathband" <(E-Mail Removed)> wrote in message
news:467157f0$0$90275$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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)
> >
> >
> >
> > Keith Rathband wrote:
> > >
> > > 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?
> > >
> > > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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
> > > >
> > > > Keith Rathband wrote:
> > > > >
> > > > > 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.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
>
>

> 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