Summation of Data in cells of mixed Numerical/Text Data

Discuss the spreadsheet application

Summation of Data in cells of mixed Numerical/Text Data

Postby open-office » Sat Nov 30, 2013 12:27 pm

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.
LibreOffice 3.5.4.2
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
open-office
 
Posts: 2
Joined: Sat Nov 30, 2013 12:15 pm

Re: Summation of Data in cells of mixed Numerical/Text Data

Postby Villeroy » Sat Nov 30, 2013 1:39 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27290
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Summation of Data in cells of mixed Numerical/Text Data

Postby open-office » Sat Nov 30, 2013 5:29 pm

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?
LibreOffice 3.5.4.2
Build ID: 350m1(Build:2)
Linux 3.10-0.bpo.3-rt-amd64
open-office
 
Posts: 2
Joined: Sat Nov 30, 2013 12:15 pm

Re: Summation of Data in cells of mixed Numerical/Text Data

Postby Villeroy » Sat Nov 30, 2013 5:39 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27290
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests