LRATOZ
Board Regular
- Joined
- Aug 17, 2014
- Messages
- 59
- Office Version
- 2016
- Platform
- Windows
Howdy,
I have been recording rainfall data since 15/11/2015. I used to have mutiple sheets for each year but I've learned that it makes more sense to have one table in one data sheet that has all the raw data in it.
So, I've got running data since 15/11/2015.
I was able to write my formulas for the monthly totals.
I've used the formula: =IF(DAY($B28)=1,F28,F28+G27).
This formula checks for the day and if the day is the 1st of the month then basically it resets itself and starts from scratch again (But it checks for the rainfall value on the 1st day of the month).
Now I want to sum the values of the daily rainfall until 31/12 of the year and then it needs to reset to zero again. However, if there is rainfall recorded on 1/1 it should incorporate that value.
I have been using a formula like this: =IF(AND(DAY($B20)=31,MONTH($B20)=12), SUM($F19:$F20),F20+G19) but it only seesm to work till the end of the month and not the year.
I also want to see the running data value on each day of the year.
I have added a screenshot for clarity.
But as you can see it didn't do what I wanted to do when the year changes.
I know I can make sums for each individual year but that's not the point as I want to see this happening automaticcaly.
In real life this spreadsheet is much more complex as I record temperatures, wind speed, etc. So, once I got this formula correct I can then easily adjust it for the other data.
Could somebody take a close look at this and forward me your comments/suggestions?
I can forward more screenshots if required.
Thank you very much in advance and have a nice day!
Cheers,
Luke
I have been recording rainfall data since 15/11/2015. I used to have mutiple sheets for each year but I've learned that it makes more sense to have one table in one data sheet that has all the raw data in it.
So, I've got running data since 15/11/2015.
I was able to write my formulas for the monthly totals.
I've used the formula: =IF(DAY($B28)=1,F28,F28+G27).
This formula checks for the day and if the day is the 1st of the month then basically it resets itself and starts from scratch again (But it checks for the rainfall value on the 1st day of the month).
Now I want to sum the values of the daily rainfall until 31/12 of the year and then it needs to reset to zero again. However, if there is rainfall recorded on 1/1 it should incorporate that value.
I have been using a formula like this: =IF(AND(DAY($B20)=31,MONTH($B20)=12), SUM($F19:$F20),F20+G19) but it only seesm to work till the end of the month and not the year.
I also want to see the running data value on each day of the year.
I have added a screenshot for clarity.
But as you can see it didn't do what I wanted to do when the year changes.
I know I can make sums for each individual year but that's not the point as I want to see this happening automaticcaly.
In real life this spreadsheet is much more complex as I record temperatures, wind speed, etc. So, once I got this formula correct I can then easily adjust it for the other data.
Could somebody take a close look at this and forward me your comments/suggestions?
I can forward more screenshots if required.
Thank you very much in advance and have a nice day!
Cheers,
Luke