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

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

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.
Last edited by Hagar Delest on Fri Jan 15, 2021 1:03 pm, edited 1 time in total.
Reason: tagged solved.
RobinZ

Posts: 17
Joined: Tue Nov 26, 2019 5:37 am

### Re: How to have one column for a running balance?

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   Expand viewCollapse view
`=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   Expand viewCollapse view
`=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
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6281
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: How to have one column for a running balance?

> 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.
LibreOffice 6.3.3.2, Win7x64 Ultimate
RobinZ

Posts: 17
Joined: Tue Nov 26, 2019 5:37 am

### Re: How to have one column for a running balance?

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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer

Posts: 8100
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

### Re: How to have one column for a running balance?

RobinZ wrote:> 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.
LibreOffice 7.0.3 on Xubuntu 20.10 and LibreOffice 6.4.6 (portable) on Windows 10.

Hagar Delest
Moderator

Posts: 29288
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

### Re: How to have one column for a running balance?

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!
LibreOffice 6.3.3.2, Win7x64 Ultimate
RobinZ

Posts: 17
Joined: Tue Nov 26, 2019 5:37 am

### Re: How to have one column for a running balance?

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.
Apache OpenOffice 4.1.8 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 32031
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: How to have one column for a running balance?

RobinZ wrote:I thought it was pretty clear.

It wasn't.
RobinZ wrote: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.

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.
Attachments
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer

Posts: 8100
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

### Re: How to have one column for a running balance?

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)
LibreOffice 6.3.3.2, Win7x64 Ultimate
RobinZ

Posts: 17
Joined: Tue Nov 26, 2019 5:37 am

### Re: How to have one column for a running balance?

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.
Apache OpenOffice 4.1.8 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 32031
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: How to have one column for a running balance?

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.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3659
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia