Re: How do I count crossing zero in a column of integers?

Giganews Newsgroups
Subject: Re: How do I count crossing zero in a column of integers?
Posted by:  Peo Sjoblom (peo.sjobl…@nw^^excelsolutions.com)
Date: Tue, 30 May 2006

If you values start in A1 going down A25 you can put this in B2 and copy
down along

=AND(A1>0,A2<0)

will return TRUE when a value changes from >0 to <0

to count it

=SUMPRODUCT(--(A1:A24>0),--(A2:A25<0))

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging."  Lord Healey

"suzie" <suz…@discussions.microsoft.com> wrote in message
news:08FECD31-EDC8-46F4-83FB-A41F8B8C96…@microsoft.com...
>I have a very long column of data which moves from positive to negative. I
> want to count the occasions where it crosses zero, but am having trouble
> writing a formula for this condition. Is there a way I can automatically
> compare consecutive cells in the column to identify whether it has changed
> from negative to positive?

Replies

In response to

How do I count crossing zero in a column of integers? posted by Suzie on Tue, 30 May 2006