When I downloaded some credit card statement data into CALC I was not able to =sum(k3:k65) because the answer was always 0. This appears to be a formatting thing but changing the formatting does not make a difference. If I download the same credit card statement into excel then there is no problem.
In calc If I just enter numbers into a column and do =sum(j1:jx) I get the right answer
Hit Ctrl+F8 and you will see that you sum up numbers highlighted in blue. The values in your csv remain black which indicates that they are text. The text “123” is not the same value as the number 123, not even the same category of value.
You mustt not ignore the import options.
The numbers may be actually entered as text in Calc. Excel may be converting automatically.
If you are downloading a CSV from your credit card company then when you go to open the CSV in Calc be sure to look over the dialog box that appears really carefully. Under the Other Options look for the checkbox with Detect Special Numbers by it. Make sure it is checked. Excel may just be doing this as a default, where Calc wants you to tell it that it’s okay.
Excel wrongly interpretes numeric text as number like the VALUE function does. This gives correct results in most cases and wrong results when you open the same file on another system.
1 Like
I have tried formatting the cells as numbers/general or numbers/1234.56 or as currency and=sum() still returns zero.
Excel does not SUM() text the same way as Calc doesn’t.
If you Test Calc and Excel on the same system, then what is happening must be you using a locale that uses comma as decimal separator; you fine-tuned your system to override decimal separator explicitly to be dot (on Windows, it’s in Region and Language applet); and thus Excel sees dots in your “statement” as decimal separators, but Calc doesn’t (it doesn’t take manually overridden parameters).
What you need is to make sure that when you import the “statement”, you either set the “language” of the text import to en-US, or you set the type of specific columns with decimal separators to US.
Formatting the cell under Cell Format will just change the way the number/text appears (with some caveats, unfortunately). What you probably need is to truly change the cell contents from text to number. A “2” and 2 are not the same thing, no matter how they are displayed. The easiest way is to have the conversion done when loading, per the CSV import dialog I mentioned in the earlier comment, or to follow one of the tips in erAck’s FAQ link below.
I suppose you could try Tools>Options>LibreOffice Calc (header at left)>Formula (sub-header at left)>Detailed Calculation Setting (at right)>Details button>Convert only if unambiguous or Convert also locale [which I think should be Convert as per locale]. I’ve never noticed this setting outside of 7.3, so I don’t know its availability in different versions.
There was no import option when I downloaded the csv file. Only tab or comma delimeted or quickbooks/quicken format
I tried downloading as tab delimited and have the same problem.
But when you open the csv in Calc there is an import-screen. And here you can actually change the type if the columns, to avoid cells imported as text. (Also look for “detect special numbers”).
It sums =“1,111”+“2,222” and the English version returns integer 3333 whereas the German version returns the decimal fraction 333.333. Same formula, same data, different results.
Please note the “SUM()”, which is specifically about SUM spreadsheet function. And Calc sums strings the same way with +, with same locale-dependent result, which is completely unrelated both to my comment that you refer to, and to the topic here.
That’s correct.
=“1,111”+“2,222” is off topic.
Using the wiki page I was able to do the find/replace and get the format changed so summing works. BUT, I should not have to do this., this should be fixed IMHO. The next time I do this could be months from now when I don’t have excel running on my machine. Have to figure where to store the wiki page adress so I can use CALC at that time.
Who shall do this, on your computer… The problem is, csv does not carry information on the contents. In my country it is a common problem to get downloaded csv from banks or quicken, where the locale doesn’t fit. Or the files are sent by mail, and the conditions at download are not prevesed/noted. So it need an educated guess or information on the context to do this right…
If excel fit your needs, use it. But don’t expect all options of this software will be hidden to behave like excel. And guessing wrong is not good for your data… (You may find out if you have to repair such a mess.)
1 Like
You are the one and only able to do this because you have the natural intelligence to decide what the text “1,234” actually represents. Is it an integer with a thousands separator, a decimal fraction or a literal text?