Cumualtive Totals

[RGSL]

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

[Sajan]

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

Hi,
Assuming that you are adding the Banked Today value on new rows each time, if you have the Banked Today in column A, and the "Month to Date" in column B, you could do something like the following:
in cell B1
=sum($A$1:$A1)


Copy down to additional rows.

Cheers,
Sajan.

[JeffreyWeir]

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!