[Solved] SUM all cells and dots in cells
[Solved] SUM all cells and dots in cells
Hi, I can sum all cells numbers if they are with 1,2344 format, but it doesn't work when there is dot in numbers, for example 1.2345, how can I force calc to sum all numbers in cells, if there are dots in numbers?
Last edited by MrProgrammer on Tue Jul 13, 2021 5:53 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.10 on Win7
Re: SUM all cells and dots in cells
Are you using 1.2345 and 1,2345 format both to indicate 1 decimal 2345?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: SUM all cells and dots in cells
No, all numbers have dots. For example when I export program data to csv file, numbers have dots instead comma.
Last edited by qba82 on Fri Jun 25, 2021 10:05 am, edited 1 time in total.
OpenOffice 4.1.10 on Win7
Re: SUM all cells and dots in cells
Why are you using csv files? It would be helpful if you uploaded a small .ods file and its resulting csv file to the forum; this will help a speedy analysis.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: SUM all cells and dots in cells
Are you importing a CSV file, produced by an external program, that uses the period as a decimal separator?
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: SUM all cells and dots in cells
You say "My program is exporting only to csv" - can you modify the output to use a period as decimal separator?
If not, I assume you have Language set as Polish in the import filter dialog, and have checked Detect special numbers under Other Options. Try setting the column(s) with the period separators as US English
I just tried that with a simple CSV file:
and that imported the first column as numbers.
If not, I assume you have Language set as Polish in the import filter dialog, and have checked Detect special numbers under Other Options. Try setting the column(s) with the period separators as US English
I just tried that with a simple CSV file:
Code: Select all
1.23,"Foo"
2.27,"Bar"
3.15,"Zip"
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: SUM all cells and dots in cells
Select English as import language for all columns. Choose English (UK) if there are D/M/Y dates or currency with £, choose English (USA) if there are M/D/Y dates or currency with $.
Always check "Detect special numbers"
Always check "Detect special numbers"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: SUM all cells and dots in cells
Ok I will try it later because now I must go out.
OpenOffice 4.1.10 on Win7
Re: SUM all cells and dots in cells
Of course, correct import is preferable.
However, if there are numbers converted to text with mixed usage of the decimal separators "." and ",", and it is assured that none of these characters is used as a "thousands separator",
the formula =VALUE(SUBSTITUTE(SUBSTITUTE(A1;",";".");".";MID(1/2;2;1))) should convert the delimiter actually used in A1 to the current delimiter used for the sheet (or the target cell) - and finally return the number..
However, if there are numbers converted to text with mixed usage of the decimal separators "." and ",", and it is assured that none of these characters is used as a "thousands separator",
the formula =VALUE(SUBSTITUTE(SUBSTITUTE(A1;",";".");".";MID(1/2;2;1))) should convert the delimiter actually used in A1 to the current delimiter used for the sheet (or the target cell) - and finally return the number..
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: SUM all cells and dots in cells
Ok I have found where is a problem. One one machine I have LibreOffice, and there problem is as described, and on second machine I have open office and there is no problem with importing csv files, it automatically convert 1.233 to 1,234. So on open office it work just fine, I will have to open this topic on libre office forum, sorry for my mistake.
OpenOffice 4.1.10 on Win7
Re: SUM all cells and dots in cells
Posting LibreOffice questions here is no problem. No need to apologize. In the ask.libreoffice site there may be helpers with better knowledge of LO-specific issues, but i strongly suspect that the issue spawns from different settings for default number locale, as previously suggested.
The decimal separator setting will normally follow the nationally specified default for the location you have specified for LibreOffice.
Please note:
The decimal separator setting will normally follow the nationally specified default for the location you have specified for LibreOffice.
Please note:
- The "locale", i.e. nationality setting specified for documents (e.g. content language), does not need to correspond to user interface settings (language for menus and dialogs).
- The setting for decimal separator can be set to differ from the locale default.
- The locale for imported data can be set at import time, per column, to any locale available in Calc. This was explained in previous answers.