7 Replies Latest reply: Jan 18, 2013 9:55 AM by Barry
Jay Miller3 Level 1 Level 1 (10 points)

How does one create a column, inside Numbers, where each subsequent cell is added, in value, to the preceding cell above it? For example, a column could be labeled "credit card subtotals for the month." In the first cell, one might insert a dollar value, eg., $24.00. The next credit card charge might be $100.00 and that would be added to the $24.00 to create a running subtotal.

 

Thank you.


MacBook Pro (15-inch Mid 2010), Mac OS X (10.7.5), 2.53 GHz Intel Core i5, 4 GB 1067 MHz DDR3
  • 1. Re: How to create a column where each subsequent entry is added to the former?
    Barry Level 7 Level 7 (29,180 points)

    Hi Jay,

     

    Cells can contain an entered value or a formula, but not both.

     

    Your running total will need to be displayed in a cell other than the one into which you make the new entry. Usually this is located on the same row, of a separate column,

     

    Here are two ways to get a running total. Both use the same layout—individual amounts entered in column B, with the first item is in B2, running total displayed in the same row of column C.

     

    For the first, C1 is assumed to be in a Header row, and must either be blank, contain the starting balance or contain a Text value such as "Total".

     

    C2: = SUM(C1,B2)

     

    For the second, B1 is assumed to be in a Header row, and must either be blank, contain the starting balance or contain a Text value such as "Amount".

     

    C2: =SUM($B$1:B2)

     

    Fill either formula down to the rest of the cells in column C.

     

    To avoid the running total being repeated beside all the empty cells in column B, enclose one of the formulas above in this shell:

     

    =IF(LEN(B2)>0,formula for C2,"")

     

    As before, fill down from C2 to the rest of the column.

     

    Regards,

    Barry

  • 2. Re: How to create a column where each subsequent entry is added to the former?
    Jay Miller3 Level 1 Level 1 (10 points)

    Thank you, Barry.

     

    So, would the formulas offered be entered in the cells just as written? And, where is the shell where you suggest putting the special formula?

     

    Thanks and best,

     

    jay

  • 3. Re: How to create a column where each subsequent entry is added to the former?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Jay,

     

    In Barry's use of the term "shell", the shell is everything but the part in italics. 

     

    =IF(LEN(B2)>0,formula for C2,"")

     

    An alternate way to suppress the display of zeros is to use a Conditional Format.

     

    Jerry

  • 4. Re: How to create a column where each subsequent entry is added to the former?
    Barry Level 7 Level 7 (29,180 points)

    Hi Jay,

     

    Both formulas are written as they would appear entered into C2.

     

    The 'special formula' is also written as it would appear in C2. Where I have used 'formula for C2' substitute your choice of the two formulas listed above the 'special formula' in my earlier post.

     

    If you choose to use the first version, the finished 'special formula' will look like this in C2:

     

    =IF(LEN(B2)>0,SUM(C1,B2),"")

     

    Jerry's remarks regarding using conditional formatting to suppress the display of zeroes is well taken, but in this case, will not work as there are no zero values to be hidden.

     

    In this case, either of the initial formulas will repeat the last 'running balance' in every cell from the last row containing an entry in column B to the bottom row of the table.

     

    The formula checks, by counting the number of characters displayed, whether an entry has been made in the column B cell on the same row as the formula (B2 in the example in this post. IF an entry has been made, the formula calculates the sum of that entry and the previous running balance. IF no entry has been made (ie. there are no chanracters displayed in B2, the calculation is skipped, and the empty string ( "" ) is placed in C2, giving the appearance of a blank cell.

     

    Example:  the formula in this post is used in column C. Column D uses the core of that formula (with the C1 reference replaced by D1) without the IF statement. Both formulas are filed down to the bottom cell in their columns. Note the difference in rows 7 and below:

    Picture 2.png

     

    Regards,

    Barry

  • 5. Re: How to create a column where each subsequent entry is added to the former?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Barry,

     

    You wrote: Jerry's remarks regarding using conditional formatting to suppress the display of zeroes is well taken, but in this case, will not work as there are no zero values to be hidden.

     

    I must have been sleep walking when I wrote that suggestion. Thanks for pointing out that it doesn't apply here.

     

    Jerry

  • 6. Re: How to create a column where each subsequent entry is added to the former?
    Jay Miller3 Level 1 Level 1 (10 points)

    HI Barry,

     

    Thanks, so much, for helping me with this. But, I'm confused over a number of things (no pun intended). In cell C2, I'm reading that there are two entries to make, but this doesn't make sense to me. I think this is so confusing, I probably need to find a video tutorial, something to watch, in order to understand it. Do you know how I might find this? Again, thank you.

     

    Regards,

     

    Jay

  • 7. Re: How to create a column where each subsequent entry is added to the former?
    Barry Level 7 Level 7 (29,180 points)

    Hi Jay,

     

    I think I've just given you a bit too much information.

     

    =IF(LEN(B2)>0,SUM(C1,B2),"")

     

    entered in C2 and filled down to the end of column C will give the results shown in column C of the table shown in my earlier post.

     

    Regards,

    Barry