Summing cumulative daily values for each month then resetting next month
Hi - I need a bit of help devising some code for this - thought it would be a relativelty common requirement but can't find much on the net / text books.
I want to devise a VBA function which sums the daily values in a month and have it reset at the end of the month. The values are both historic data and forecasts into the future. So for example:
...
A (date); B (values); C
29/9/12; 3; 3
30/9/12; 4; 7
01/10/12; 2; 2
02/10/12; 6; 8
....
As always, any help is gratefully received!
One way:
=IF(MONTH(N(A2))<>MONTH(N(A1)),N(B2),N(B1)+N(B2))
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Sorry, what does "N" stand for in this context?
Last edited by Cutter; 09-21-2012 at 09:36 AM.
Also, any chance I could write it in VBA?
One way:
Regards, TMS
Please Login or Register to view this content.
Last edited by TMS; 09-21-2012 at 05:40 AM. Reason: Add code tags ... oops
N: Converts non-number value to number, dates to serial numbers, TRUE to 1, anything else to 0 (zero).
In this case N(A1) = N("Date") = 0 ... hence not the same as N(A2) where A2 has a date in it. So, for the first data record, it will force a difference and start the running total again (in fact, for the first time).
Regards, TMS
Thanks but it doesn't seem to work. To be clear, once I add the code to module, the formula I should be entering in C1 is "=IF(MONTH(N(RC[-2]))<>MONTH(N(R[-1]C[-2])),N(RC[-1]),N(R[-1]C[-1])+N(RC[-1]))"
You have the worksheet formula, which you would put in cell C2 and drag down. And you have the code which puts an R1C1 type formula in cells C2 down to the last row as determined by the number of cells in column A.
Alternatively, you could use:
Please Login or Register to view this content.
Unless I really want/need to work out a complex formula in VBA and, perhaps more importantly, I already have a working formula in a cell, I will use that.
Select the cell with the formula, start the macro recorder, press F2 to enter Edit mode, press Enter, stop the macro recorder.
Change "ActiveCell" to be the cell/range where you want the formula and delete the Range(...).Select that will follow it. This gives you the R1C1 type formula. It's quite useful because it shows you where you need to double up on quotes, etc., although, in this case there aren't any. And, if you wish, you can then change FormulaR1C1 to Formula and copy in the formula from the cell ... making sure to double up quotes when you need to.
Regards, TMS
Note that you'd start with the formula in C2, not C1 (as mentioned in your last post).
Thanks very much. I got it working!
You're welcome.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks