Rainfall onset and offset count

Thlama

New Member
Joined
Mar 24, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I have a yearly rainfall data from 1st Jan to 31st Jan.
I will like to count the number of days with rainfall of at least 20mm in 3 days.

Example:
Date. mm
1/1/2020 10
2/1/2020 18
3/1/2020 20
4/1/2020 26
5/1/2020 16
6/1/2020 24
7/1/2020 20
8/1/2020 23
9/1/2020 0
10/1/2020 18
11/1/2020 20
12/1/2020 26
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,375
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum.

The A4 formula might require entry with Ctrl+Shift+Enter.

MrExcel posts18.xlsx
AB
3instances of at least 20 rain for at least 3 consecutive days
42
5
6rain minimum20
7for days3
8
9daterain
101/1/202010
111/2/202018
121/3/202020
131/4/202026
141/5/202016
151/6/202024
161/7/202020
171/8/202023
181/9/20200
191/10/202018
201/11/202020
211/12/202026
221/13/20200
231/14/20200
241/15/20200
251/16/20200
261/17/20200
271/18/20200
281/19/20200
291/20/20200
301/21/20200
311/22/202021
321/23/202021
331/24/20200
341/25/20200
351/26/20200
361/27/20200
371/28/202021
381/29/202020
391/30/202020
401/31/202021
Sheet63
Cell Formulas
RangeFormula
A3A3="instances of at least "&B6&" rain for at least "&B7&" consecutive days"
A4A4=SUMPRODUCT(--((FREQUENCY(IF(B10:B40>=B6,ROW(B10:B40)),IF(B10:B40<B6,ROW(B10:B40))))>=B7))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,380
Messages
5,635,919
Members
416,887
Latest member
SheriE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top