After a simple =sum addition of series of numbers, no arrays, and making sure formatting is number, not text, alignment, only error message or just zero as a result has returned, and reading here about suggestions, it doesn’t work.
Yes I have updated, rebooted and nothing.
I can’t believe such a simple formula can waste half a day.
Formatting does not matter if the cell values are text actually. No formatting attribute changes the value of a cell. You imported text or pasted from somewhere, right?
You can upload a sample file with the problem here for someone to look at and examine it.
And please provide your operating system and LibreOffice version.
Thank you.
example.ods (18.3 KB)
thanks.
I just opened a new document, made 1+1 and the sum formula worked for this…sigh
Windows 10
Version: 24.2.4.2 (X86_64) / LibreOffice Community
Build ID: 51a6219feb6075d9a4c46691dcfe0cd9c4fff3c2
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Hello James
your supposed numbers are text!
In such a case, it is advisable to switch on the Value Highlighting function. Numbers are shown in blue.
You can use the following function to convert your numbers.
=NUMBERVALUE(E3,",",".")
If you select the correct language when importing the CSV file, you will not have such a problem.
somehow classical …
Hit Ctrl+F8 to turn on value highlighting. Constant numbers are blue now, formulas are green and text is black.
In column E, you entered comma decimals which are not accepted in your environment. Therefore, these “numbers” are black, and they are aligned to the left cell borders whereas numbers are right aligned.
The dates are 6th of April. Possibly you meant 4th of June. “13/6/2024” in row 13 was not recognized as date, most likely because your system uses a US-English locale and LibreOffice takes over this US-English locale which uses decimal points and M/D/Y dates.
The decimal issue can be solved by simply replacing all commas in that column with points. Select the column, call menu:Edit>Find&Replace…, check “Current selection only”, search for comma and replace with point.
For the numeric dates, enter =DATE(YEAR(A11);DAY(A11);MONTH(A11)) in cell F3 and double-click the cell handle in the lower right corner.
To avoid this type of problems, I recommend Tools>Options… Language>LanguageS ettings>Locale “English (South Africa)” instead of “English (USA)” which accepts decimal points and UK-style dates.
yes it was downloaded from my bank. csv, default, and after copied and pasted back (special) as unformatted
In the import dialog, choose import locale “English (South Africa)” and check “Detect speical numbers” and it will import correct decimals and dates.
That’s a great idea regarding the language, being from the US (laptop…) and living in France, and especially the irony of using SA omg I am dying laughing.
Thanks for making me smile.
If the csv data come in this way, “English (South Africa)” is the easiest way to adjust for correct data import. Oh, “French (France)” and most Spanish flavours would do just as well.
So change the locale setting in the office options to “French (France)”, and when you import csv, this will be the default.