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

LRATOZ

New Member
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?

Cheers,

Luke

Attachments

• 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.

Well-known Member
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
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

Well-known Member
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.

Replies
16
Views
345
Replies
0
Views
112
Replies
38
Views
1K
Replies
6
Views
179
Replies
2
Views
148

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.

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

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