[Solved] Table sum not working

Discuss the word processor

[Solved] Table sum not working

Postby jhearcht » Tue Mar 06, 2018 8:26 pm

Apache OpenOffice 4.1.4

I have repeatedly tried to make a Table work as a simple record of income, Daily, Weekly, & Monthly, for a self-employed individual, not a company. But the column Sum function refuses to work properly, obviously due to operator error. I won't go into the details, because it gets complicated & crazy & frustrating, with disappearing numbers and "expression is faulty" messages. When I setup a test table with only a few rows & columns, the sum works properly. So I know how it's supposed to work (and I've done it before with MS Word). But in the fully formatted table something is wrong, and I can't get a simple sum to work. Yes, I've looked at previous posts on the topic. So I'm giving up on do-it-yourself, and looking for a template.

I haven't been able to find a simple Day, Week, Month Income template for OO. I'd appreciate a tip.
Last edited by Hagar Delest on Fri Mar 09, 2018 8:57 am, edited 1 time in total.
Reason: tagged solved
Open Office 4.1.1 on Windows 10
jhearcht
 
Posts: 23
Joined: Fri Jun 26, 2015 5:32 am

Re: Table sum not working

Postby RoryOF » Tue Mar 06, 2018 8:46 pm

Try
Personal/Family Budget Spreadsheet

It is probable that you should be using Calc not Writer for such a task. Calc is specifically designed for arithmetic calculations.
Apache OpenOffice 4.1.6 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27656
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Table sum not working

Postby jhearcht » Wed Mar 07, 2018 1:00 am

Thanks Rory
But this table is much too simple for a spreadsheet. If I could understand why a simple summation acts so crazy, Write would be more appropriate. I'm assuming that the problem is a misunderstanding on my part, even after following the instructions step by step. So I'm giving up, and looking for a ready-made table created by someone less stupid.

However, I would use a Calc template if I could find one suitable. But all I find is the kind of thing an accountant would create for a large business.
Open Office 4.1.1 on Windows 10
jhearcht
 
Posts: 23
Joined: Fri Jun 26, 2015 5:32 am

Re: Table sum not working

Postby RoryOF » Wed Mar 07, 2018 1:11 am

A spreadsheet can be quite simple and in my view is better adapted for such a project.

I manage my bank account with a twelve sheet spreadsheet - one for each month, with a row for each day. Five columns - date in col1, expenditure amount in col2, income amount in col3, running balance in col4, and narrative in col5. If I have multiple expenditures on any one day, I insert the amounts as an addition chain in col2 (=28+350+45); similarly for income.
Apache OpenOffice 4.1.6 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27656
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Table sum not working

Postby jhearcht » Wed Mar 07, 2018 2:35 am

Thanks again

Making a spreadsheet may be simple, once you know how. But when I open Calc, I don't know where to start. I see a full sheet of cells, but no way to organize them into a table of names & numbers. The Help file tells about specific details, but not a step by step procedure to layout and format a table. I searched online for tutorials, but the only one that looked like what I need doesn't do anything. It refers to pages, but no way to get there. This just adds to my feeling of frustration.

I made similar tables in MS Word years ago, and didn't have any difficulty with summing. In Write it's easy to setup a Table, but once everything is in place, the Sum function doesn't work. If I could figure out what the problem is, I would stop bothering the Forum with my petty problems.
Open Office 4.1.1 on Windows 10
jhearcht
 
Posts: 23
Joined: Fri Jun 26, 2015 5:32 am

Re: Table sum not working

Postby robleyd » Wed Mar 07, 2018 2:48 am

If you could provide us with a sample of a Writer document which shows your problem, the forum might be able to resolve your problem for you, be it petty or otherwise.

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire. The link also contains information on how to anonymise your document if it contains confidential information.

If you are interested in using Calc you might find OpenOffice Spreadsheet Tutorial for Beginners with Examples a useful resource.
Cheers
David
Apache OpenOffice 4.1.6 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2254
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Table sum not working

Postby jhearcht » Wed Mar 07, 2018 5:25 am

Here's the table I'm working on. As I said, sums work fine in a simpler test table, but not in the one I have formatted. I assume I've done something wrong in the setup, but no idea what it was. If that's not it, then I just don't understand the procedure for summing a column.
Attachments
UBER Daily-Weekly-Monthly income 2018 - sums not working.odt
(14.18 KiB) Downloaded 23 times
Open Office 4.1.1 on Windows 10
jhearcht
 
Posts: 23
Joined: Fri Jun 26, 2015 5:32 am

Re: Table sum not working

Postby Zizi64 » Wed Mar 07, 2018 8:43 am

Here's the table I'm working on. As I said, sums work fine in a simpler test table, but not in the one I have formatted. I assume I've done something wrong in the setup, but no idea what it was. If that's not it, then I just don't understand the procedure for summing a column.
UBER Daily-Weekly-Monthly income 2018 - sums not working.odt


In my opinion the Writer is absolutely wrong tool for this task.
I suggest you: use the Calc application. You can format the cells containing numeric values as Date, as Currency, and you can set the background color of the cells. Use some user defined Cell style instead of the direct (manual) formatting....
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7449
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Table sum not working

Postby robleyd » Wed Mar 07, 2018 10:46 am

I agree with Ziz64 that Calc would be the better tool for what you want to do; particularly when you want to expand the information you store and calculate.

That said, the problem with your Writer file is simply that the SUM formulae in your table are incomplete - they mostly do not contain the range of table cells to sum. Please compare the formulae in the attached file with your sample.

I had thought to put together a quick sample spreadsheet based on your sample Writer file but I'm not sure exactly what you are trying to achieve overall.
Attachments
uber_sums.odt
(11.84 KiB) Downloaded 26 times
Cheers
David
Apache OpenOffice 4.1.6 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2254
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Table sum not working

Postby RoryOF » Wed Mar 07, 2018 11:52 am

I have just checked: a range of budget templates is available on the Templates repository. This link will offer you some personal budgets
https://templates.openoffice.org/en/search?query=personal+budget&sort_by=created&sort_order=ASC


I pick one out at random - I have not examined it so cannot say how good/bad or simple/complex it is.
https://templates.openoffice.org/en/template/personal-finance-spreadsheet-10

On Forum we often have to tell posters or sometimes bite our tongues and not say that Calc is the wrong tool for their task, whatever that might be. It is a refreshing change to have to say for once that Calc is the correct tool.

For what you want to do you could learn Calc in about 30 minutes. You need (initially) to be able to enter text in a cell (for narrative of a transaction), to be able to add, subtract, multiply and divide values of one cell by another. It is helpful if you master how to enter a formula - be it a calculation or a date, and pull it down the sheet so that it applies to all subsequent cells, autoincrementing to adjust for values on the current row.

Attached is the very simple spreadsheet I use for my finances - I offer the 2018 version, with a very few sample entries - overwrite these with your figures. If you open this, you will see that it consists of 12 sheets, one for each month, the running total (col D) taking the value from the previous month. This is a quick and dirty sheet I threw together for my own use - it is simple (childishly so) and has worked for me for several years - I live a very simple life!

Col A is the date. Col B is expenditure on that day - if I have multiple expenditures I enter them as an addition chain. Col C is income for that day (ditto addition chain if multiple incomes on the day). Col D is the running total; Col E is the narrative. Play with this - enter some values and see how simple it is. You may then decide to make a more complex sheet.

Col A and Col D should not have any values entered or you'll destroy their formulae.

Hover the cursor over any Cell to see what the formula is.
Attachments
Year 2018.ods
(17.99 KiB) Downloaded 18 times
Apache OpenOffice 4.1.6 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27656
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Table sum not working

Postby jhearcht » Wed Mar 07, 2018 7:42 pm

robleyd wrote:I agree with Ziz64 that Calc would be the better tool for what you want to do; particularly when you want to expand the information you store and calculate.
That said, the problem with your Writer file is simply that the SUM formulae in your table are incomplete - they mostly do not contain the range of table cells to sum. Please compare the formulae in the attached file with your sample.

Robley
Thanks for the reply. But I really don't want to take the time to learn how to use Calc. I have used Word tables for similar purposes in the past while self-employed, and Write is also ideal for my records in retirement. The only problem with Write is that apparently I don't understand how to set a Sum in a column. You say the formulae are incomplete, but they were set by selecting the whole column of numbers including empty cells. I suspect I did something out of order, but I was trying to follow the Help steps one by one.

How did you set the Sums in your corrected version of my file?
Open Office 4.1.1 on Windows 10
jhearcht
 
Posts: 23
Joined: Fri Jun 26, 2015 5:32 am

Re: Table sum not working

Postby jhearcht » Wed Mar 07, 2018 7:54 pm

RoryOF wrote:On Forum we often have to tell posters or sometimes bite our tongues and not say that Calc is the wrong tool for their task, whatever that might be. It is a refreshing change to have to say for once that Calc is the correct tool.

Rory
Thanks for the suggestions, but I'm too old to learn how to use Spreadsheets. Calc is too abstract, and Write is more graphic. So Write is perfect for my simple-minded needs, if I can only learn how to set Sums in the table. I seem to be laboring with a key misunderstanding of the process.
Open Office 4.1.1 on Windows 10
jhearcht
 
Posts: 23
Joined: Fri Jun 26, 2015 5:32 am

Re: Table sum not working

Postby RoryOF » Wed Mar 07, 2018 8:04 pm

You need 30 minutes to understand all of Calc you will need. Did you try the sample sheet I uploaded? Put a figure in column B, another in Column C and see what happens. Enter some text in Column E. It won't bite you! Calc will calculate more accurately than you or I will.
Apache OpenOffice 4.1.6 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27656
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Table sum not working

Postby robleyd » Thu Mar 08, 2018 3:28 am

How did you set the Sums in your corrected version of my file?


There are several ways - I'll describe, with pictures, what I suspect is the simplest way. First, of course you will need the document open.

1) Click in the table cell where you want the sum to be displayed. If it isn't already open, the Table window should appear.
table_sum1.png
table_sum1.png (8.9 KiB) Viewed 1070 times


2) Click on the Sum icon Σ in the Table window - the Table window will be greyed out and the Formula bar will appear at the top of your document with the basis of the Sum formula, which now needs to know what to sum.
table_sum2.png


3) Select the cells in the column above the location of the formula that you want to include in the sum, be they empty or contain a value. The range you have selected will appear as part of the Sum formula in the formula bar and in the cell in which you are creating the formula.
table_sum3.png


4) Once you have the required range in the Sum formula, click the green tick on the Formula bar - which has a tooltip message Apply - or press Enter to apply the formula. Once applied, the result of the formula will be displayed in the cell, and the formula itself will appear as a tooltip when you hover your mouse over the cell.
table_sum4.png
table_sum4.png (10.28 KiB) Viewed 1070 times
Cheers
David
Apache OpenOffice 4.1.6 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2254
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Table sum not working

Postby jhearcht » Thu Mar 08, 2018 11:58 pm

Robley

Thanks for the step by step. That's exactly what I was trying to do, so I'm still not sure what I was doing wrong to make the numbers in the column disappear and an error message in the sum cell appear. Anyway, the tables are working now. Except I still need to learn how to make row sums work. But that's a problem for another day.
Open Office 4.1.1 on Windows 10
jhearcht
 
Posts: 23
Joined: Fri Jun 26, 2015 5:32 am

Re: Table sum not working

Postby RoryOF » Fri Mar 09, 2018 12:02 am

Row sums should work the same way. Instead of =Sum<b3:b23> the expression would be =<b3:k3> - note that in the column expression, the row numbers change, in the row expression the column letters change.
Apache OpenOffice 4.1.6 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27656
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland


Return to Writer

Who is online

Users browsing this forum: No registered users and 42 guests