计算事件发生后的天数

I am working with a weather data set. I am particularly interested in two columns which are cumulative precipitation and a date. My question is a simple one, though I am struggling to figure out the solution. Essentially I am wanting to determine days since precipitation. An example of the data is as follows:

我正在处理一个天气数据集。我特别感兴趣的是两个列，一个是累积降水，另一个是日期。我的问题很简单，尽管我正在努力寻找解决方案。本质上，我想要确定降水的天数。数据示例如下:

WEATHER DATA

天气数据

Pr  Date
40  8/8/2013
40  8/8/2013
40  8/9/2013
40  8/9/2013
41  8/10/2013
41  8/10/2013

In this example, if I know the last day it rained was 8/7, then 8/8 would have a value of 1 (days since precipitation), 8/9 would be 2, and 8/10 would go back to 0. I have multiple dates because of hourly recordings (I trimmed it down for this post). I've been trying to figure it out with conditional if|then statements, but I'm thinking VBA may be more appropriate here. Any help or insight would be greatly appreciated.

在这个例子中，如果我知道最后一天下雨的时间是8/7，那么8/8的值将是1(自降水以来的天数)，8/9将是2,8/10将回到0。我有多个日期，因为每小时的录音(我为这篇文章削减了它)。我一直在尝试用条件if|然后语句来计算它，但我认为VBA在这里可能更合适。任何帮助或见解都将不胜感激。

1 个解决方案

#1

Assuming cell C2 to be equal 1 (or start where you wish by adjusting the C2 value), the formula below works in the example you provided. Type in C3:

假设单元C2 = 1(或者通过调整C2的值开始您希望的位置)，下面的公式在您提供的示例中工作。在C3型:

=IF(A3<>A2,0,IF(B3=B2,C2,1+C2))

Drag the formula down. Explanation:

阻力公式。解释:

If precipitation from time i+next is different from i it comes back to zero --> there was rain.

如果i+next的降水与i不同它会回到0 >那里有雨。

If time i+next is equal i, then it compares the date d+next with d.

如果时间i+next等于i，那么它会比较日期d+next和d。

If they are equal hold the number of days without rain from previous cell.
如果它们相等，则保持先前单元中没有雨的天数。
If they are not, add 1 day* to the value inside previous cell.
如果不是，则向前一个单元格中的值添加1天*。

*I'm assuming you have consecutive days from the following sentence:

*我假设你有连续的日子从下面的句子:

I have multiple dates because of hourly recordings

因为每小时的录音，我有很多约会

#1