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

Discuss the spreadsheet application

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

Postby RobinZ » Thu Jan 14, 2021 1:51 am

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?

Postby RusselB » Thu Jan 14, 2021 2:17 am

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.
User avatar
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?

Postby RobinZ » Thu Jan 14, 2021 7:43 pm

> 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?

Postby John_Ha » Thu Jan 14, 2021 8:15 pm

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.
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?

Postby Hagar Delest » Thu Jan 14, 2021 8:21 pm

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.
User avatar
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?

Postby RobinZ » Thu Jan 14, 2021 8:25 pm

John_Ha wrote: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!
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?

Postby RoryOF » Thu Jan 14, 2021 8:40 pm

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
User avatar
RoryOF
Moderator
 
Posts: 32031
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby John_Ha » Thu Jan 14, 2021 9:25 pm

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.

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.
Attachments
answer.ods
(7.93 KiB) Downloaded 3 times
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?

Postby RobinZ » Thu Jan 14, 2021 10:20 pm

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?

Postby RoryOF » Thu Jan 14, 2021 10:41 pm

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
User avatar
RoryOF
Moderator
 
Posts: 32031
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby robleyd » Fri Jan 15, 2021 12:40 am

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
User avatar
robleyd
Moderator
 
Posts: 3659
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests