[Solved] How to have one column for a running balance?

[RobinZ]

I have looked and looked and cannot find this formula. You'd think a sensible designer would just have you right click the column and then click "Running Balance". But no. This is spreadsheets, land of the needlessly complex.

Many people talk about highlighting a column and clicking Sum which totals it at the bottom. But all that is doing is adding up a column of numbers. The whole point of a spreadsheet program is telling a column to do something and forgetting about it as it deals with the numbers entered beside it. I think this is called a running balance. I would have thought it would be
=SUM(B2:B:99)
but this doesn't work. It says #NAME when I paste that in. Seem logical. I've always appreciated calculators that spit out letters instead of numbers.....not.
I tried
=SUMPRODUCT(B2:B:99)
and get #NAME again.
for showing a running balance of the B column where you're sure you won't have more than 99 rows. But isn't there a way of just formatting the column itself? There must be.

What DOES work is when I use
=SUMPRODUCT(B1:B8)
and have row 1 to 8 filled in for the B column.
I, like the rest of the world, don't want to program one cell. We want to do the whole column.

[RusselB]

In OpenOffice you can't specify the entire column by just using the column letter.
In LibreOffice you can.
'while you are getting the #NAME error message for the first two, in fact the problem is with the parameters rather than the name.
If you know that you won't have more than 99 entries, then you can just use

Code: Select all

=sum(B1:B99)

and place it anywhere except in the range B1 to B99
IF you want to see it on a row by row basis, then (for example) put

Code: Select all

=sum($b$1:$b1)

into cell C1, then copy that formula down in column C for the range you want to cover from column B

[RobinZ]

> Code: Select all Expand view
What is this "Code"? I don't see it anywhere on my screen or in any Menu. I'm using version 4.1.8.

> =sum(B1:B99)
Doesn't work. It says Err523 when I placed it way beyond the last specified cell in the column. I have 8 cells so I tried putting it around the 15th one down.

> =sum($b$1:$b1) into cell C1, then copy that formula down in column C for the range you want to cover from column B
This just displays whatever is in the top cell of B column.

My needs are simple. Just a running balance for now. I don't want to need a software engineering license to add up numbers. This is pure lunacy. Clearly this is not the right program for me. Can you suggest something simple that can add and keep a running balance? I wonder if that is too gargantuan a request. Surely there must be people as dumb as me out there that need simple spreadsheets. You highlight a column and click Running Balance of which column you need. Imagine that. No formulas, no insanity.

[John_Ha]

Please upload a small spreadsheet file together with a clear explanation of what you are trying to do in the spreadsheet.

Press POSTREPLY and click the Upload attachment tab below where you type (128 kB max); or use a file share site, Dropbox or Google Drive for a larger file.

[Hagar Delest]

> Code: Select all Expand view
What is this "Code"? I don't see it anywhere on my screen or in any Menu. I'm using version 4.1.8.

It is not an AOO menu, it is a forum feature that should enable you to select the code that is displayed in the white area under that line. But actually it doesn't work. So just select the code itself, that is "=sum(B1:B99)" (without quotes) and paste it in the spreadsheet cell.

[RobinZ]

Please upload a small spreadsheet file together with a clear explanation of what you are trying to do in the spreadsheet.

Press POSTREPLY and click the Upload attachment tab below where you type (128 kB max); or use a file share site, Dropbox or Google Drive for a larger file.

I thought it was pretty clear. How's this for clarity?
Column A has the numbers 1, 2, 3.
Column B is the running balance. Its corresponding numbers should be 1, 3, 6.
Pretty clear? Let's see how complicated our beloved spreadsheet people can make this!

[RoryOF]

In B2 enter = B1+A2, then pull down for as many entries as you are likely to need by the little black square in the corner of B2.

I use this on a 12 sheet file, one sheet for each month, so I would only have 31 lines on each sheet.

B1 should be 0, as there is no running balance to start with.

[John_Ha]

I thought it was pretty clear.

It wasn't.

How's this for clarity?

Much better.

It's 22 words precisely describing what you want to do. Compare it with the 214 words of verbosity in your original post and 187 words of clarification in your second post for a total of 410 words.

I have uploaded the answer.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[RobinZ]

Well that seemed to work perfectly. I could add numbers in the A column and they summarized the running balance in the B column. I notice you didn't use the SUM after the = sign. I've seen people recommend other formulas. Say for summarizing column B from row 1 to 9:
=SUM(B1:B9)
=SUM($B$1:B9)
=SUMPRODUCT(B1:B9)
They didn't explain why they used these particular formulas vs others.
I didn't try =SUMPRODUCT($B$1:B9)

[RoryOF]

Use Sum only when you need to add up a contiguous number of cells in a column or row; in the formula I gave, it is clearer to itemise each component of the formula. A formula should be written so that, if in future time one needs to alter that formula, it is clear exactly what it does.

=Sum(B1:B9) is clearer and more concise than =B1+B2+B3+B4+B5+B6+B7+B8+B9, and gives less opportunity of omitting one of the values.

The $ prefixes indicate that the following reference, be it a Column (Letter) or Row (number) is not to be altered in a pull down. See Help file item Addresses and References, Absolute and Relative for more information.

[robleyd]

Based on your posts, you seem to be a newcomer to spreadsheets As such, you may find the following to be useful resources if you are interested in learning more about Calc:

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Absolute, relative and mixed references

There may be other topics in the Calc Tutorials Forum that may be of assistance from time to time.