Hi, I hope some one can help me.
I have a banking spreadsheet which I update daily. It is roughly set out as below:
BANKED TODAY: £500
BANKED FOR MONTH TO DATE: £9,000
Can I create a formula that adds ‘Banked Today’ to the ‘Month to Date’ figure so that when I update the ‘Banked Today’ figure tomorrow, it’ll add to a running total of £9,500?
EXAMPLE:
Say today is 10th of the month and the ‘Banked for month to date' total is £9,500
If on the 11th I changed the ‘Banked Today’ figure to £200, I want an autosum to add the £200 to the £9,500 so when I change the ‘Banked Today’ figure on the 12th, it’ll be added to £9,700?
Any help appreciated.
Thanks.
RGSL
Last edited by Sajan; 2013-06-10 at 03:22 PM. Reason: Adding one more clarification
Here's something of interest on this:
http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx
There are two methods of doing period-to-date or cumulative SUMs. Suppose the numbers that you want to cumulatively SUM are in column A, and you want column B to contain the cumulative sum; you can do either of the following:
- You can create a formula in column B such as =SUM($A$1:$A2) and drag it down as far as you need. The beginning cell of the SUM is anchored in A1, but because the finishing cell has a relative row reference, it automatically increases for each row.
- You can create a formula such as =$A1 in cell B1 and =$B1+$A2 in B2 and drag it down as far as you need. This calculates the cumulative cell by adding this row's number to the previous cumulative SUM.
For 1,000 rows, the first method makes Excel do about 500,000 calculations, but the second method makes Excel do only about 2,000 calculations.
I tried this out with a cumulative sum of 10,000 rows. Boy did it make a difference!
Bookmarks