Page 1 of 1

[SOLVED] Unable to sum after download

PostPosted: Sun Nov 10, 2019 9:17 pm
by fatgreta
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

Re: Unable to sum after download

PostPosted: Sun Nov 10, 2019 9:37 pm
by RusselB
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.

Re: Unable to sum after download

PostPosted: Sun Nov 10, 2019 9:51 pm
by fatgreta
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

Re: Unable to sum after download

PostPosted: Sun Nov 10, 2019 10:21 pm
by RusselB
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

Re: Unable to sum after download

PostPosted: Sun Nov 10, 2019 10:39 pm
by fatgreta
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

Re: Unable to sum after download

PostPosted: Mon Nov 11, 2019 12:14 am
by RusselB
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.

Re: Unable to sum after download

PostPosted: Mon Nov 11, 2019 12:40 am
by fatgreta
I assume I entered that manually? It’s probably a typo, I’ll check next time I fire it up.

Re: Unable to sum after download

PostPosted: Mon Nov 11, 2019 1:01 am
by RusselB
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.

Re: Unable to sum after download

PostPosted: Mon Nov 11, 2019 3:46 am
by fatgreta
I do have 4.1.7, can't find where to change that info here though.

Re: Unable to sum after download

PostPosted: Mon Nov 11, 2019 3:47 am
by RusselB

Re: SOLVED Unable to sum after download

PostPosted: Mon Nov 11, 2019 5:01 am
by fatgreta
Got it thanks, the special characters box did the trick. I think I'll remember that now.