[Solved] Problem summing values

Discuss the spreadsheet application
Post Reply
mukiduk
Posts: 17
Joined: Wed May 20, 2009 9:05 pm

[Solved] Problem summing values

Post by mukiduk »

I have a large spreadsheet made in OO calc and now I want to add up different cells but, when I try it I get a zero answer.
this is a snip of the top of my spreadsheet.
this is a snip of the top of my spreadsheet.
It seems to be because many of the amount have an aprostrophe is front of the amount. I do not know how or why this happened, when I entered the amount, I entered it a currency. How can I get rid of this tick in front of the amount on the cells as shown in this snip I made with the snipping tool.

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply [robleyd, Moderator].
Last edited by Hagar Delest on Mon Jun 18, 2018 8:06 am, edited 2 times in total.
Reason: tagged solved
OOo 3.0.X on Ms Windows XP + Ubuntu
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: spreadsheet

Post by crusader »

You can use the Text to Columns option. In LibreOffice, that option is found in the menu under Data; my guess is it may be the same in OpenOffice.

Highlight the cells you want to remove the apostrophe from, click on Data, scroll down to Text to Columns and hit enter.

Hope that helps.
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: spreadsheet

Post by RusselB »

The Text to Columns option is in the same place in Open Office.
The usual way of that problem occurring is with data being incorrectly identified when importing from a text (usually .csv) file.
For future reference, when importing from an external source, if you see the Text Import dialog, ensure that the Detect special numbers option is checked.
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.
mukiduk
Posts: 17
Joined: Wed May 20, 2009 9:05 pm

Re: spreadsheet

Post by mukiduk »

thank you all for this. I have just gone back to the original Open Office file and I see what the problem has been. I usuaslly click on the dollar sign at the top of the page because I want to show dollars in each cell but that is what puts the apostrophe in front and it will not add up. If I format the column as text, it does not show the dollar sign and I do not want to have to click on the dollar key every time I want to enter an amount. How do I make the spreadsheet show a dollar sign automatically and still be able to add the whole or part of the column when I want to do so.
OOo 3.0.X on Ms Windows XP + Ubuntu
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: spreadsheet

Post by robleyd »

Format the cells as currency; then you only need to enter numbers but the values will be displayed with a currency symbol.

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know

OpenOffice Spreadsheet Tutorial for Beginners with Examples
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: spreadsheet

Post by Lupp »

<kidding>
"spreadsheet" A really informative subject! We should encourage users to make it standard.
</kidding>
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem summing values

Post by robleyd »

I usuaslly click on the dollar sign at the top of the page because I want to show dollars in each cell but that is what puts the apostrophe in front
I missed this when I answered before: clicking on the dollar icon shouldn't add a leading apostrophe - it just sets the format to currency, specifically using the format code

Code: Select all

[$$-C09]#,##0.00;[RED]-[$$-C09]#,##0.00
However if the apostrophe is present before you use the dollar icon, it will still be there after the format change.

Is it possible that this data is imported from say a CSV type file? If so, ensure that you check the Detect special numbers option when importing data.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
mukiduk
Posts: 17
Joined: Wed May 20, 2009 9:05 pm

Re: Problem summing values

Post by mukiduk »

I find it very hard trying to get back to the subject when I do manage to login. I am still having trouble with my spreadsheet problem. I made the spreadsheet in OO Calc from scratch by typing in all the entries each time but sometimes I may use a formula to get to the correct amount just to check the sum is correct. Then I may save it as both an open office spreadsheet and an excel spreadsheet. I do not know if this has introduced the aprostrophe, but some cells have it and others do not.
Now I want to an parts of the columns and this is when the trouble is happening. These are shares and some of them came from buyinhg shares and others came from dividends being converted to shares instead of a cash dividend. When I look at the spreadsheet with excel, and highlight the cell a little yellow sign next to it says the entry is a number stores as text, and it shows a green mark in the corner or each cell and the aprostrophe in front of the number in calc. There is no reason for it to be there as far as I know. Now how do I copy the column and paste it to another spreadsheet without that mark???
Any answer would help. Allan
OOo 3.0.X on Ms Windows XP + Ubuntu
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Problem summing values

Post by RusselB »

As already stated, the easiest way to remove that leading apostrophe is to use Text to Columns.
The apostrophe means that Calc thinks that the entry is text, rather than a number. Excel has some kind of code in it that allows it to use entries that are entered as text, but have a numeric value, as numbers without having to convert the text to number first. This is one of many things that Excel can do and Calc can not.
As to getting back to this subject when you log in, if there has been one or more posts since the last time you logged in, there will be a link in the e-mail that will take you directly to this topic, It is the first link in the e-mail that you would receive after a new reply has been made.

If you have already tried the Text to Columns and found that it doesn't work (though that would be a first for me regarding this type of situation, which has been asked about many times previously), then please upload a sample of your spreadsheet (remembering to remove or alter any sensitive data).
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
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem summing values

Post by robleyd »

It might be helpful to read [Tutorial] Text to Columns to give you more information about that option.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
mukiduk
Posts: 17
Joined: Wed May 20, 2009 9:05 pm

Re: Problem summing values

Post by mukiduk »

Thank you David. Looks like when I originally made the spreadsheet, I saved it as an excel for 97/2000/xp and have added to the same file over the years. in the file I am trying send, I have opened the excel file in Calc and tried to copy and paste several columns so I can find out why I cannot add the lists. I could not find anything about 'text to columns" but I will read that tutorial later. For some reason, this file does not add the large amounts of money and I see the amount on row 23 is a formula. Should this be added also or do I have to change all the formulas to currency? This file is on a usb so I hope I can upload it. I cannot seem to copy the file here, I will try after I submit this reply.
OOo 3.0.X on Ms Windows XP + Ubuntu
mukiduk
Posts: 17
Joined: Wed May 20, 2009 9:05 pm

Re: Problem summing values

Post by mukiduk »

From EXCEL to OO.ods
(11.23 KiB) Downloaded 96 times
OOo 3.0.X on Ms Windows XP + Ubuntu
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem summing values

Post by robleyd »

In the sample file you provided, there are three types of cell content - text, numbers and formulae. You can see which is which if you select View | Value Highlighting, or pres Ctrl-F8. Text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted. If this function is active, colours that you define in the document will not be displayed. When you deactivate the function, the user-defined colours are displayed again.

The cells H1:H21 are text; they have formatting named Excel_Builtin_Currency which suggests they were created in Excel, or saved in Excel format. When opened again in Calc, they are apparently treated as text. To allow Calc to recognise them as numbers, Text to Columns is used.

Simply select the cells that are text, go to Data | Text to Columns, and click OK. The cells that can be converted to number will be converted and will appear as currency format. Any cells which contain words such as H2,which contains the string nil, won't be converted - you will have to deal with them manually.

If you are having issues when pasting from other sources, you might need to use Paste Special which gives you some additional options about how the paste should be handled. Without knowing what, and from whence you are copying, I can't be more specific about the options you might need to use.

Note that once the contents of a cell are treated as text - with the leading apostrophe or if the cell(s) are formatted as text, changing the format won't change the contents of the cell from text.

You may find the following to be useful resources for working with Calc.

[Tutorial] Ten concepts that every Calc user should know
OpenOffice Spreadsheet Tutorial for Beginners with Examples
[Tutorial] Absolute, relative and mixed references
[Tutorial] VLOOKUP questions & answers
[Tutorial] How do I specify the formula for a column?
Pivot tables formerly Data Pilot
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
mukiduk
Posts: 17
Joined: Wed May 20, 2009 9:05 pm

Re: Problem summing values

Post by mukiduk »

Thank you David, That is exactly what I wanted and it solves my problem. Nobody else that I have asked in my computer club, who are supposed to be experts on spreadsheets have told me that. In future I will save all my work in calc. I do not know why I ever saved it in XLS because I do not have microsoft office on my computer and do not use it. except when I have to do something for the club. who are all MS clones. The only office I have available is version 97
OOo 3.0.X on Ms Windows XP + Ubuntu
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem summing values

Post by robleyd »

MS Office 97 should be able to open OpenOffice documents, with the usual caveat that Microsoft may have its own implementation of the Open Document Format (ODF) that Apache Open Office - and other open source office suites - use; so there might be some elements handled differently from AOO.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
mukiduk
Posts: 17
Joined: Wed May 20, 2009 9:05 pm

Re: [Solved] Problem summing values

Post by mukiduk »

I am trying to alter the original spreadsheet and many of the entries are text and some are numbers.If I highlight the top cell so it selects all the entries I can alter everything to ?.
But I am not sure what I should alter them to. Should it be text or number? And will everything add up properly then? Thank you. Allan
OOo 3.0.X on Ms Windows XP + Ubuntu
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Problem summing values

Post by robleyd »

Do what I suggested above.
Simply select the cells that are text, go to Data | Text to Columns, and click OK. The cells that can be converted to number will be converted and will appear as currency format. Any cells which contain words such as H2,which contains the string nil, won't be converted - you will have to deal with them manually.
If you are trying to "alter" them by changing the format, I remind you that:
Note that once the contents of a cell are treated as text - with the leading apostrophe or if the cell(s) are formatted as text, changing the format won't change the contents of the cell from text.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply