Counting Rainfall Data

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" <[email protected]> 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
<[email protected]> 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" <[email protected]> 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...