Count the occurrences of rainfall over 2 inches within a two day period.

[tdpalmer]

I have a table with date in column A and amount of rainfall in column B. In column C I need to count if there is an occurrence of rainfall over 2 inches in a consecutive two day period. If the rainfall does exceed 2 inches it is counted and the calculation starts over for the subsequent days. [if on Monday it rained .5 inches, on Tuesday 1.65 inches then a count (1) is entered in column C for Tuesday. On Wednesday if it rained .60 inches the calculation recycles and does not use the rainfall on Tuesday because it was previously used in the previous two day total calculation. So Wednesday would not qualify as an occurrence of rainfall over a two day period.

 

[Misca]

Try

=(SUM(B2:B3)>2)*(C2+1)

If you don't want the numbers to add up, replace the (C2+1) with 1

The formula expects that there are no gaps in dates. If you want to make sure there are no gaps in the dates you're going to have to add the date verification into the formula:

=(A3=A2+1)*(SUM(B2:B3)>2)*(C2+1)

 

[MrTeeny]

Are you looking for VBA or a simple formula you can drag down the column?

A simple formula would be

=IF(AND((B1+B2)>2,C1=""),1,"")

Assuming your data starts in B1, your formula would start in C2

 

[tdpalmer]

I do not know VBA so something simple would be great. Will check this out.

 

[tdpalmer]

Thank you for your reply. Will check this out. Thanks.

 

[tdpalmer]

I got this to work.

Now once I identified the occurrence (Over 2 inches during a 2 day period) I need to reset the 2 day period when I go to the next day's rainfall. What is happening is the formula is adding the next day to the previous day (that was part of the 2 day period) and is identifying that day as an occurrence. I want the calculation to reset after it identifies an occurrence.

 

[tdpalmer]

This formula worked to eliminate the counting of another 2 day interval after a 2 day interval

=IF(C17=1,"0",(SUM(B17:B18))>1)*(1)

However need to modify to count an occurrence if the daily rainfall is over 2 inches for the day even if it is directly after a 2 day occurrence.

 

[MrTeeny]

Did you try the formula I posted?

=IF(AND((B1+B2)>2,C1=""),1,"") starting at C2.

The C1="" part meant that it would restart counting a new two day period, can always change C1<>1 if other data can also be in C. Or are you meaning each period is two days regardless and do not overlap consecutively so rainfall of

0.15
0.85
1.5
0.6


would read in column C as

empty
empty
empty
1

rather than

empty
empty
1
empty



1

 

[tdpalmer]

This worked perfectly. Thank you!!!