Ask Your Question

How to calculate number of days since end of last wet spell?

asked 2021-01-08 13:27:24 +0100

carob gravatar image

updated 2021-01-08 17:10:56 +0100

Where a wet spell (a useful amount of rainfall; breaking a drought) is arbitrarily defined as a period of time not longer than 6 days having a total of 30mm or more rainfall. The data is mm rainfall and the date of the event. Please see attached.C:\fakepath\dates with rainfall.ods

edit retag flag offensive close merge delete


Which data do you have? How it is formated?

Click edit below your question and use the paper clip to upload a sample file (remove all sensible data before).

LeroyG gravatar imageLeroyG ( 2021-01-08 13:49:29 +0100 )edit

Probably by using a helper column with a "running precipitation" record.

How is your data laid out, to base that calculation on? Preferably, edit your question posting and attach a spreadsheet file with your data.

keme gravatar imagekeme ( 2021-01-08 13:51:51 +0100 )edit

@carob, Given 7 consecutive days, each with 5 mm, what day is the end of the wet spell? How many days make a dry spell?

LeroyG gravatar imageLeroyG ( 2021-01-08 22:45:58 +0100 )edit

Given 7 consecutive days, each with 5 mm, what day is the end of the wet spell? The 7th day would be the last day of the wet spell. How many days make a dry spell? That wasn't separately defined so would be the period between wet spells. Thanks for your continued interest. All these ideas enable different ways to visualise the data as "growth periods" which is just for personal interest

carob gravatar imagecarob ( 2021-01-10 19:53:41 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2021-01-10 16:06:19 +0100

LeroyG gravatar image

I share an (in-process) option. I need more info (see my last comment).

  • In C2: =IF(AND(A2<A3-5;SUMIF(A$2:A2;">="&A2-5;B$2:B2)>=30);A2;"--")
  • In D2: =IFERROR(IF(C2>1;C2-VLOOKUP(C2-1;C1:C$2;1);"");"--")

image description

See sample file.

Tested with LibreOffice (x64); OS: Windows 10.0.

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark (Answer markCorrect answer mark) to the left of the answer that solves your question.

If the answer helped you, you can mark the up arrow (Upvote mark) that is on the left (to vote, you need to have karma of at least 5).

edit flag offensive delete link more

answered 2021-01-08 17:54:22 +0100

Lupp gravatar image

I moved your data into the sheet I had prepared. See attachment.
It uses one relevant helper column (Cf. @keme), and I wold strictly dissuade from attempts to do it without.

edit flag offensive delete link more


Just had another look on the data from OQer's file. They don't make sense.
The given task obviously requires data for a contiguous sequence of days.

Lupp gravatar imageLupp ( 2021-01-08 18:00:28 +0100 )edit

Thanks for this solution and for the interest shown by everyone. Although this answer isn't quite what I was looking for it will be an easy matter to insert the days with zero rainfall into the data which I think will then show which periods during the years had a good amount of rainfall

carob gravatar imagecarob ( 2021-01-08 18:15:09 +0100 )edit

To expand the list to one containing the days unbroken is recommendable anyway, but next to indispensable for the evaluation.
Leaving out a day due to 0mm precipitation would cause doubts if not a system failure skipped a day with 123mm.
For the evaluation it would mean the need of a lot of matching like
=SUMPRODUCT($B$2:$B$376;ABS($A$2:$A$376-$A132+2.5)<3) in row 132 (e.g.) for the 6-day-precipitation, always evaluatimg the complete range which surely has more than 6 positions (as the OFFSET range has).
Well, if there aren't lots of additioanl calculations, and you can assure my first counterargument to be obsolete, you might calculate the moving accumulation of 6-day percipitations the second way...
But where would you place the final "number of days since..." Mainly for a long draught this should be relevant, and many of the respective ...(more)

Lupp gravatar imageLupp ( 2021-01-08 21:19:31 +0100 )edit

answered 2021-01-11 18:23:13 +0100

carob gravatar image

Another solution which uses a helper column but without the need to include days with zero rainfall could have the following formula copied down from the top of the helper column =SUMIF($A$2:$A2,">=" & DATE(YEAR($A2),MONTH($A2),DAY($A2)-5),$B$2:$B2) where column A is date and B is rainfall However the data does make more sense as previously stated by including zero rain days

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2021-01-08 13:27:24 +0100

Seen: 50 times

Last updated: Jan 11