I have a column of mixed numerical and text data in each cell.
I would to apply a function, =SUM() say, to just the numerical data types of the columns. To sum up all the numerical data in the column.
I've tried doing =SUM(A1:A9) but the result is 0. I've also tried formatting the cells and have the Category as Number and Format as General. The result is still the same.
Summation of Data in cells of mixed Numerical/Text Data
-
- Posts: 2
- Joined: Sat Nov 30, 2013 12:15 pm
Summation of Data in cells of mixed Numerical/Text Data
LibreOffice 3.5.4.2
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
Re: Summation of Data in cells of mixed Numerical/Text Data
ALl your figures are text. menu:View>HighlightValues [Ctrl+F8] displays numbers in blue and text in black. Why do you enter, import or copy text if you want numbers? Formatting attributes must not change the data type of a cell value. Number formats do not apply to text.
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
-
- Posts: 2
- Joined: Sat Nov 30, 2013 12:15 pm
Re: Summation of Data in cells of mixed Numerical/Text Data
The data types are mixed, Text and Numerical values in the cells of the column.
I would like to find a way to sum just the numerical values in the cells.
I have other columns which have more strict data types but in this case I want to find a way to make the best use of the data I already have.
Is there a counter solution around this problem?
I would like to find a way to sum just the numerical values in the cells.
I have other columns which have more strict data types but in this case I want to find a way to make the best use of the data I already have.
Is there a counter solution around this problem?
LibreOffice 3.5.4.2
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
Re: Summation of Data in cells of mixed Numerical/Text Data
SUM works for me. It sums up the numbers and disregards the text.
If you want to sum up all data that are either numbers or numeric text, you've got to convert the text to numbers. The VALUE function may do so or not depending on your office locale and on the aleged locale of the concrete text values.
If you want to sum up all data that are either numbers or numeric text, you've got to convert the text to numbers. The VALUE function may do so or not depending on your office locale and on the aleged locale of the concrete text values.
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