Open Side Menu Go to the Top
Register
Excel: Data analysis problem Excel: Data analysis problem

11-06-2008 , 04:41 PM
I have 20K records of historical data of high temp per day (back to 1951) and I want to find the longest consecutive period of below zero days.

Two columns: Date, High temp.

Any ideas?
Excel: Data analysis problem Quote
11-06-2008 , 05:16 PM
sort by date. In 3rd column, add formula (starting in c2 and assuming u have a header row)
=(b2<0)*1
Fill down

In d2
=d1*c2+c2
Fill down.

In any other cell
=max(d:d) gives u the answer
Excel: Data analysis problem Quote
11-06-2008 , 05:41 PM
Quote:
Originally Posted by JammyDodga
sort by date. In 3rd column, add formula (starting in c2 and assuming u have a header row)
=(b2<0)*1
Fill down

In d2
=d1*c2+c2
Fill down.

In any other cell
=max(d:d) gives u the answer
I don't understand. Never seen this in excel. There is no command.

My idea was this.

Column C =If(b2<0,1,0)

Assigning a value of one if negative, 0 if positive.

c2=d2

Then if(c2=1,c2+d2,0)

max (d:d)

Shouldn't this come up with it? Where am I going wrong?
Excel: Data analysis problem Quote
11-06-2008 , 07:08 PM
The IF-version and the boolean version ought to give the same result.

Briefly, a comparison by itself in parentheses gets evaluated to TRUE (1) or FALSE (0). That is, "=(blah<blah)" is the same thing as "if(blah<blah,1,0)".

The second step, similarly, multiplies by something that's either 0 or 1, rather than using an IF statement that returns 0 when false.
Excel: Data analysis problem Quote
11-07-2008 , 05:32 AM
Quote:
Originally Posted by Joelyben
I don't understand. Never seen this in excel. There is no command.

My idea was this.

Column C =If(b2<0,1,0)

Assigning a value of one if negative, 0 if positive.

c2=d2

Then if(c2=1,c2+d2,0)

max (d:d)

Shouldn't this come up with it? Where am I going wrong?
Dude, you suck. It would have taken you 3 seconds to try what I suggested and then you would have seen it worked. Its not rocket science.

In future, i'd suggest mrexcel.com, they probably have more patience than I do.
Excel: Data analysis problem Quote

      
m