I have a data set of four columns, X, Y1,Y2,Y3, which has been calculated from input data copied by LibreOffice Calc from a .xlxs file. I want to plot the three variables, Y1, Y2 and Y3, against X using Chart.
I have formatted the column headings as “format cells text”. I have formatted the numerics in the columns as “format cells number -1234.57”.
I highlight the dataset and click on “Insert chart, chart type line”. The values of the X’s appear as if they were Y’s, with the row numbering appearing on the X axis. In other words, there is only one line plotting the X values against the row numbers. The lines showing) Y1, Y2 and Y3 are missing completely although the headings are shown in the reference to the colours of the chart. But I keep trying! I go to Data Range, which shows “Data Series in Columns” and “First Row as Label”. I click on “First Column as Label”. Everything disappears except the frame of the chart. It seems that LibreOffice Calc cannot recognise that any of the values of Y1,Y2, Y3 are existing. They seem to be seen by the system as blank.
What have I done wrong?
I run Kubuntu 18.04 with LibreOffice Version: 6.0.7.3
question.ods (16.5 KB)
Upload the file, please
How do I upload the file? nomizo.
Start a new comment or edit a previous one. Click the 7th icon on the toolbar above the text.
Thank you. Duly uploaded (I think!)
Thank you for uploading the file.
All your data are text except the first column.
When you remove the centered orientation, you see all numbers right aligned and text left aligned.
When you turn on View>Highlight Values (Ctrl+F8) numbers appear in blue, text in black font.
=ISNUMBER(B2) returns FALSE
The text “144” is a sequence of 3 characters. It is not the same value as the number 144.
Convert text to columns:
Select the cells in question and apply any number format that is not “Text” (this is OK in your file).
Call menu:Find&Replace
under “Other options” check “Regular Expression” and “Current Selection Only”
Search: .+ [point and plus]
Replace: &
[Replace All]
I knew I must have done something wrong! Thank you.
… and if you want a real diagram based on the numeric values for the x-axis (instead of only using the contents of the respective column as “categoies”) don’t choose a “line chart” (mostly very misleading) but an X-Y diagram.
See attached example.
reasonableDiagramVSnonsenseChart.ods (47.3 KB)
To get an impression of the issue in the given example of noise-like data, you need to look at the tiny details. The arbitrary example in the second sheet is more clear, but may be less instructiuve just for that reason.
Thank you, Lupp,
I shall try it.
In the meantime I tried the first solution, telling the system that the entries were numerical, and it worked easily.
I have now understood that the problem arose because the device producing the input data must have been sending text, even though it appeared as numericals.
The odd thing, however, is that Calc was happy to carry out the calculations on those text entries as if they were numerical. (The file I sent was sheet 5. It had been produced after many a manipulation, including sorting on value.).
Again, many thanks.
Nomizo
Indeed, this is difficult to understand because exceptions to simple rules piled up over the years.
Calculating with text values as in =B2*C2+D2 where each cell contains some text consisting of digits only does work because such strings are unambiguous. The same operation fails with error #VALUE! if any of the cells contains a decimal fraction because “1,234” and “1.234” mean different numbers in different locale context. Either of the 2 strings could be interpreted as a decimal fraction or as one-thousand-two-hundred-thirty-four when opened with the same software using different locale settings.
A formula like =SUM(B2:D10) will ignore any text value. In your document, it returns zero.
Avoid all numeric text except for zip codes, phone “numbers”, article numbers etc. All these “numbers” serve as identifiers. You never perform any arithmetics with these numbers but you want to keep leading zeroes in “0012345”. In order to enter a numeric text into a spreadsheet, format the cells as text before entering the numeric text or enter the text with a leading apostrophe into a cell with any non-text number format.