Results 1 to 3 of 3

Thread: Cumualtive Totals

  1. #1

    Cumualtive Totals



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Quote Originally Posted by RGSL View Post
    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.
    Last edited by Sajan; 2013-06-10 at 03:22 PM. Reason: Adding one more clarification

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •