Function to Count Number of Consecutive Rows with a Specific Criteria?

[Templee1]

I am an undergraduate student doing climatological research and am using an Excel spreadsheet to compile daily precipitation data for a 60-year period for a specific weather station. I would like to use a formula that would count the number of consecutive days in which the amount of precipitation was equal to 0 so that I could construct a histogram showing the frequency of short and long term drought.

Do you have any suggestions?

Thank you.

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Tom Ogilvy]

even for 60 years, counting at the 1 day resolution wouldn't seem to be very
meaningful (a bunch of 1 occurance columns). Seems like counting some
grouping like consecutive 30-day months or weeks with some rounding rule
would be more meaningful?

--
Regards,
Tom Ogilvy

"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>

[Sandy Mann]

Templee1,

There may be ways of doing it in one formula but a simple way would be to
use a hidden column.

With the data starting in A4, enter the formula =IF(A5=0,B4+1,0) in B5 and
copy down. (If the 60 years' data is already in column A then re-select B5
and go to the bottom left-hand corner where the small black box called the
"fill handle" is and, when the cursor turns into cross-hairs then double
left-click and XL will copy the formula down the column for you.)

Now in C4 enter the formula =IF(AND(B4=0,B5=0),0,IF(AND(B4<>0,B5=0),B4,0))
and copy down. You should be presented with a column of zeros except the
last day of a drought which will have a total of the days of the drought '

If you wish column B can be hidden.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Templee1" <[email protected]> wrote in
message news:[email protected]...
>
> I am an undergraduate student doing climatological research and am using
> an Excel spreadsheet to compile daily precipitation data for a 60-year
> period for a specific weather station. I would like to use a formula
> that would count the number of consecutive days in which the amount of
> precipitation was equal to 0 so that I could construct a histogram
> showing the frequency of short and long term drought.
>
> Do you have any suggestions?
>
> Thank you.
>
>
> --
> Templee1
> ------------------------------------------------------------------------
> Templee1's Profile:
http://www.excelforum.com/member.php...o&userid=25083
> View this thread: http://www.excelforum.com/showthread...hreadid=385967
>