[Solved] SUM all cells and dots in cells

Discuss the spreadsheet application
Post Reply
qba82
Posts: 6
Joined: Wed Jun 23, 2021 9:52 am

[Solved] SUM all cells and dots in cells

Post by qba82 »

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]
OpenOffice 4.1.10 on Win7
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM all cells and dots in cells

Post by RoryOF »

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
qba82
Posts: 6
Joined: Wed Jun 23, 2021 9:52 am

Re: SUM all cells and dots in cells

Post by qba82 »

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
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM all cells and dots in cells

Post by RoryOF »

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
qba82
Posts: 6
Joined: Wed Jun 23, 2021 9:52 am

Re: SUM all cells and dots in cells

Post by qba82 »

My program is exporting only to csv.
OpenOffice 4.1.10 on Win7
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUM all cells and dots in cells

Post by robleyd »

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
qba82
Posts: 6
Joined: Wed Jun 23, 2021 9:52 am

Re: SUM all cells and dots in cells

Post by qba82 »

Yes, exactly.
OpenOffice 4.1.10 on Win7
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUM all cells and dots in cells

Post by robleyd »

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
oo_decsep.png
I just tried that with a simple CSV file:

Code: Select all

1.23,"Foo"
2.27,"Bar"
3.15,"Zip"
and that imported the first column as numbers.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM all cells and dots in cells

Post by Villeroy »

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"
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
qba82
Posts: 6
Joined: Wed Jun 23, 2021 9:52 am

Re: SUM all cells and dots in cells

Post by qba82 »

Ok I will try it later because now I must go out.
OpenOffice 4.1.10 on Win7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUM all cells and dots in cells

Post by Lupp »

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..
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
qba82
Posts: 6
Joined: Wed Jun 23, 2021 9:52 am

Re: SUM all cells and dots in cells

Post by qba82 »

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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: SUM all cells and dots in cells

Post by keme »

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 "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.
This is the same for both suites, and the way to change settings are quite similar.
Post Reply