[SOLVED] Unable to sum after download

Discuss the spreadsheet application

[SOLVED] Unable to sum after download

Postby fatgreta » Sun Nov 10, 2019 9:17 pm

Hi,

I just downloaded data from my checkbook program in .csv format, opened it and saved it in .xls. The column that has the transaction amounts reads like this: $1000. However, when I try to use the sum feature it gives a value of 0. I've tried format cells-> numbers but can't figure out a way to change this. Can you tell me what I'm not doing right (or doing wrong)?

Thanks,

Chris
Last edited by robleyd on Mon Nov 11, 2019 5:31 am, edited 2 times in total.
Reason: Add green tick
Open Office 4.1.7 on Windows 10
fatgreta
 
Posts: 39
Joined: Sat Feb 26, 2011 1:59 am

Re: Unable to sum after download

Postby RusselB » Sun Nov 10, 2019 9:37 pm

I suspect, like most people that import using a .csv file, you didn't check the Detect Special Numbers box in the Text Import Dialog, thus the $ in front of those numbers is making Calc think that they are text, not numbers.
Formatting the cells will make no difference in this situation.
We do not recommend that you use the .xls format with OpenOffice, as that can lead to a loss of formatting and/or data.. statistically it's usually formatting that is lost, but there have been reports of data being lost.
We highly recommend that you use the native Open Document Format (.ods for spreadsheets).

If you have the .csv file, just open it again in Calc, and check the Detect Special Numbers box in the Text Import Dialog.
If you don't, then we might be able to get things corrected using Text to Columns, but it would make things easier if you can just import the .csv file again.
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: 6171
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Unable to sum after download

Postby fatgreta » Sun Nov 10, 2019 9:51 pm

Oh right, I actually posted about the same thing before I now realize. Thank you. The problem that I have with .odf format is that most people report not being able to open it (who don’t use Open Office). For my own use I suppose it would work fine.

Thanks again,

Chris
Open Office 4.1.7 on Windows 10
fatgreta
 
Posts: 39
Joined: Sat Feb 26, 2011 1:59 am

Re: Unable to sum after download

Postby RusselB » Sun Nov 10, 2019 10:21 pm

Those people should check that the version of whatever program they are using is up-to-date.
I have tested 5 different programs, and the latest versions of each have all opened the Open Document Format.
The programs I have tested are OpenOffice, LibreOffice MS Office, Google, and WordPerfect
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: 6171
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Unable to sum after download

Postby fatgreta » Sun Nov 10, 2019 10:39 pm

Ok it’s actually been a long time since I’ve had to share a spreadsheet so going back to .odf shouldn’t be a problem, and would probably lead to less frequent document crashes and need to recover the file.

Thanks again,

Chris
Open Office 4.1.7 on Windows 10
fatgreta
 
Posts: 39
Joined: Sat Feb 26, 2011 1:59 am

Re: Unable to sum after download

Postby RusselB » Mon Nov 11, 2019 12:14 am

FYI: Please check your version of OpenOffice. Currently the latest official version of OpenOffice is 4.1.7, so you either have an error in your signature, which shows 4.2.1, or you have an unofficial, and therefore, technically, unsupported, version of OpenOffice.
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: 6171
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Unable to sum after download

Postby fatgreta » Mon Nov 11, 2019 12:40 am

I assume I entered that manually? It’s probably a typo, I’ll check next time I fire it up.
Open Office 4.1.7 on Windows 10
fatgreta
 
Posts: 39
Joined: Sat Feb 26, 2011 1:59 am

Re: Unable to sum after download

Postby RusselB » Mon Nov 11, 2019 1:01 am

Fake versions of OpenOffice reporting version numbers that have not been released yet have been noted, but, more commonly with situations like yours, it's a typo...specifically a transposition of 4.1.2 (the actual version number) to 4.2.1 (the entered version).
It is an item you do have to manually enter and, if needed, update.
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: 6171
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Unable to sum after download

Postby fatgreta » Mon Nov 11, 2019 3:46 am

I do have 4.1.7, can't find where to change that info here though.
Open Office 4.1.7 on Windows 10
fatgreta
 
Posts: 39
Joined: Sat Feb 26, 2011 1:59 am

Re: Unable to sum after download

Postby RusselB » Mon Nov 11, 2019 3:47 am

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: 6171
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: SOLVED Unable to sum after download

Postby fatgreta » Mon Nov 11, 2019 5:01 am

Got it thanks, the special characters box did the trick. I think I'll remember that now.
Open Office 4.1.7 on Windows 10
fatgreta
 
Posts: 39
Joined: Sat Feb 26, 2011 1:59 am


Return to Calc

Who is online

Users browsing this forum: Zizi64 and 12 guests