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

S

suzie

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?
 
M

MartinW

Hi Suzie,

One way is to use conditional formatting. Select your entire row and set
condition 1 If cell value is less than zero format blue
condition 2 If cell value is greater than zero format red

HTH
Martin
 
S

suzie

MartinW said:
Hi Suzie,

One way is to use conditional formatting. Select your entire row and set
condition 1 If cell value is less than zero format blue
condition 2 If cell value is greater than zero format red

HTH
Martin


Thanks Martin. Now I can clearly differentiate between them, but can't get my head around how I would go about counting the changes (other than manually, which would be very time consuming). Is it possible to do this in Excel?
Click to expand...
cheers,
Suzie
 
P

Peo Sjoblom

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
 
S

suzie

Thank you... that has worked well!

Peo Sjoblom said:
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
Click to expand...
 
Top