Currently Being ModeratedJan 16, 2013 11:50 AM (in response to Jay Miller3)
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".
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.
Currently Being ModeratedJan 16, 2013 12:40 PM (in response to Barry)
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,
Currently Being ModeratedJan 16, 2013 12:56 PM (in response to Jay Miller3)
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.
Currently Being ModeratedJan 16, 2013 11:39 PM (in response to Jay Miller3)
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:
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:
Currently Being ModeratedJan 17, 2013 6:26 AM (in response to 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.
Currently Being ModeratedJan 18, 2013 7:29 AM (in response to 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.
Currently Being ModeratedJan 18, 2013 9:55 AM (in response to Jay Miller3)
I think I've just given you a bit too much information.
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.