I know this has probably been discussed many times...but I need some
help counting rainfall data.

My spreadsheet consists of 50,000+ days worth of rainfall data and I
need to know the maximum number of consecutive days with no rainfall OR
trace rainfall.

For example...my data looks like this from left to right...

0.00 0.00 T 0.15 T T 0.00 0.00 0.27

In this case it would be 4 (T,T,0,0)
Can anyone help?? Thanks in advance...

Let's say you can change your layout so that data is in a single column, in
cells A2:A50000.

In B2 put this formula

=IF(OR(A2="T",A2=0,1,0)

In B3 put this formula

=IF(OR(A3="T",A3=0,B2+1,0)

and copy it down.

Then =MAX(B2:B50000) will give you the number you want.


On 17 Jan 2005 15:23:52 -0800, "TightIsobars" wrote:

I know this has probably been discussed many times...but I need some
help counting rainfall data.

My spreadsheet consists of 50,000+ days worth of rainfall data and I
need to know the maximum number of consecutive days with no rainfall OR
trace rainfall.

For example...my data looks like this from left to right...

0.00 0.00 T 0.15 T T 0.00 0.00 0.27

In this case it would be 4 (T,T,0,0)
Can anyone help?? Thanks in advance...

Sorry, I missed the closing parentheses in the formulas. They should be

=IF(OR(A2="T",A2=0),1,0)

and

=IF(OR(A3="T",A3=0),B2+1,0)

On Mon, 17 Jan 2005 17:37:46 -0600, Myrna Larson
wrote:

Let's say you can change your layout so that data is in a single column, in
cells A2:A50000.

In B2 put this formula

=IF(OR(A2="T",A2=0,1,0)

In B3 put this formula

=IF(OR(A3="T",A3=0,B2+1,0)

and copy it down.

Then =MAX(B2:B50000) will give you the number you want.


On 17 Jan 2005 15:23:52 -0800, "TightIsobars" wrote:

I know this has probably been discussed many times...but I need some
help counting rainfall data.

My spreadsheet consists of 50,000+ days worth of rainfall data and I
need to know the maximum number of consecutive days with no rainfall OR
trace rainfall.

For example...my data looks like this from left to right...

0.00 0.00 T 0.15 T T 0.00 0.00 0.27

In this case it would be 4 (T,T,0,0)
Can anyone help?? Thanks in advance...