Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Templee1
 
Posts: n/a
Default Function to Count Number of Consecutive Rows with a Specific Criteria?


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

  #2   Report Post  
Tom Ogilvy
 
Posts: n/a
Default

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" wrote in
message ...

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



  #3   Report Post  
Sandy Mann
 
Posts: n/a
Default

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

Replace@mailinator with @tiscali.co.uk


"Templee1" wrote in
message ...

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
count function problem nkidd Excel Worksheet Functions 4 July 7th 05 08:55 PM
Count number of row where are data Maileen Excel Discussion (Misc queries) 3 December 28th 04 10:53 AM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM
#VALUE in cell but pop up function box show right number Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 04:15 PM
Whats the function to count the total times a word is displayed Monk Excel Discussion (Misc queries) 3 December 10th 04 11:39 PM


All times are GMT +1. The time now is 07:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"