How to repeat a formula and reset to zero on a monthly base?

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
Hi,
I've got one big table in which I keep my weather data.
Every day I add a line to the table with new data.
So, at the start of a new month I do the following:
On the first of the month in Column H Cell 100 =0
On the second of the month in Column H Cell 101 =G101+H100
This formula is then copied in that column till the end of the month.
On the first of the next month the system repeats.

My data goes back over the last five years and I will need to backfill many colums over all these years.
Obviously I can do it by hand but this will take some serious time and will be very repetitive.

I have attached a screenshot which shows the end of a month and then the start of a new month.

Does somebody know hoe I could write a formula (Or a VBA code) that when I click on the first day of the month I can insert this formula?

Many thanks in advance!
Cheers,

Luke
 

Attachments

  • Screenshot TBRG.JPG
    Screenshot TBRG.JPG
    50.3 KB · Views: 7

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows
Hi LRATOZ,

If your date is in column A then would this in column H work?

=IF(DAY(A100)=1,0,H99+G100)
 

LRATOZ

New Member
Joined
Aug 17, 2014
Messages
38
Hi Toadstool,
Thank you very much for your suggestion! It works!
Now, I realize, I should freshen up on my IF-statements.
I was looking for something much more complex, but this works fanatstic.
Thank you very much for your contribution and have a nice day!
Luke
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows
Hi Toadstool,
Thank you very much for your suggestion! It works!
Now, I realize, I should freshen up on my IF-statements.
I was looking for something much more complex, but this works fanatstic.
Thank you very much for your contribution and have a nice day!
Luke
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,227
Members
416,908
Latest member
Streetsweeper

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