Good morning,
I've been working on a table with rain data and I have (another) problem that I haven't been able to solve. I'll explain a bit about the context of my work and then expose the problem I'm having.
The equipment we use record the time when a 2mm rain occur. In order to work with this data, I made groups of 5 minutes rain values using the ROUNDUP function for the time (how many 2mm rain has there been in a 5min interval). This is the best option I've found but it generates a problem when i'm calculating the daily rainfalls. When the day changes (lines 10 and 11) the 5 min rain that's showed at midnight of day 3 actually happened in the 5 last minutes of day 2. So, when I calculate the daily rainfall, the rain that happened in line 11, that belongs to day 2, is actually displayed on day 3. You can see the calculated and real values in columns F and G. Does anyone have any idea on how to solve this problem?
I thank you in advance for all the help!
Mathieu
I've been working on a table with rain data and I have (another) problem that I haven't been able to solve. I'll explain a bit about the context of my work and then expose the problem I'm having.
The equipment we use record the time when a 2mm rain occur. In order to work with this data, I made groups of 5 minutes rain values using the ROUNDUP function for the time (how many 2mm rain has there been in a 5min interval). This is the best option I've found but it generates a problem when i'm calculating the daily rainfalls. When the day changes (lines 10 and 11) the 5 min rain that's showed at midnight of day 3 actually happened in the 5 last minutes of day 2. So, when I calculate the daily rainfall, the rain that happened in line 11, that belongs to day 2, is actually displayed on day 3. You can see the calculated and real values in columns F and G. Does anyone have any idea on how to solve this problem?
I thank you in advance for all the help!
Mathieu
| extrato_modelobasico.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Day | Month | Year | Reference time | Rainfall (mm) | Daily Rainfall (mm) | Real Daily Rainfall (mm) | ||
| 2 | 2 | 1 | 2020 | 2-1-20 23:15 | - | - | |||
| 3 | 2 | 1 | 2020 | 2-1-20 23:20 | - | - | - | ||
| 4 | 2 | 1 | 2020 | 2-1-20 23:25 | 0.2 | 0.2 | 0.2 | ||
| 5 | 2 | 1 | 2020 | 2-1-20 23:30 | 0.4 | 0.6 | 0.6 | ||
| 6 | 2 | 1 | 2020 | 2-1-20 23:35 | 0.4 | 1.0 | 1.0 | ||
| 7 | 2 | 1 | 2020 | 2-1-20 23:40 | 1.0 | 2.0 | 2.0 | ||
| 8 | 2 | 1 | 2020 | 2-1-20 23:45 | 7.0 | 9.0 | 9.0 | ||
| 9 | 2 | 1 | 2020 | 2-1-20 23:50 | 9.6 | 18.6 | 18.6 | ||
| 10 | 2 | 1 | 2020 | 2-1-20 23:55 | 6.2 | 24.8 | 24.8 | ||
| 11 | 3 | 1 | 2020 | 3-1-20 0:00 | 4.4 | 4.4 | 29.2 | ||
| 12 | 3 | 1 | 2020 | 3-1-20 0:05 | 7.0 | 11.4 | 7.0 | ||
| 13 | 3 | 1 | 2020 | 3-1-20 0:10 | 5.6 | 17.0 | 12.6 | ||
| 14 | 3 | 1 | 2020 | 3-1-20 0:15 | 7.4 | 24.4 | 20.0 | ||
| 15 | 3 | 1 | 2020 | 3-1-20 0:20 | 3.8 | 28.2 | 23.8 | ||
| 16 | 3 | 1 | 2020 | 3-1-20 0:25 | 1.4 | 29.6 | 25.2 | ||
| 17 | 3 | 1 | 2020 | 3-1-20 0:30 | 0.6 | 30.2 | 25.8 | ||
Planilha1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F17 | F2 | =SUMIFS(E$2:E2,A$2:A2,A2,B$2:B2,B2) |
| G2:G11 | G2 | =SUM($E$2:E2) |
| G12:G17 | G12 | =SUM($E$12:E12) |
