Complex X-Y scatter graph error bar challenges

I tend to find even adding just Y-error bars difficult to implement on complex X-Y scatter graphs and I am hoping there are some quick fixes that others know but that I can’t figure out.

The primary issue, is if one has one has an X-Y scatter graph and needs to change the data source from the default “Data Series in rows” to “Data Series in columns” and one is supplying calculated Y-error bars, then using (Menu Bar) → Insert → Y-Error Bars it seems to only add in the Error bar data in rows and thus transpose relative to the data series it is sampling which means the same dynamic source tables I use to calculate the data for these graphs and the error bar layout cannot be used without transposing one of them and generally making the situation so complicated I have never gotten it to work correctly.

The only other option I can find is to select each data series on the graph individually and add the Y-error bars data for each one individually. This is unbelievably laborious on complex graphs, and for some series that are tightly following one another, it is actually hard to select the back-most line to add the error bars to it (I sit there clicking around like an idiot for 5 minutes per each such data series on like 400% zoom to try and get them.) Not only is this manual process painfully slow, but I often end up missing a few on really complex graphs (I have a couple X-Y scatter graphs with over 300 series and hundreds of points each – Libre Office handles it like a champ … until I have to add error bars or adjust certain series appearance and then the nightmare begins.)

Is their a way to transpose the columns/rows data range for the full chart “Inset Y-error bars” so that it matches the data range pattern of the underlying data values and I don’t have to manually insert for each in the most ridiculous way?

Also, baring that, is there a more efficient way to “manually” add error bars to individual series then selecting each of the series from the graphs and playing “find the right pixel” for those distant highly occluded back lines? When one does the Edit->Data Ranges → Data Series Tab there is that convenient break down for each of the data series where it lets one select the ranges for “Name”, “X-Values”, and “Y-values” plus the range for the data labels of each. … it would be super convenient if one could edit other aspects of each of the series from there, like turning off and on X and Y error bars and selecting the ranges for them. The series style/appearance would also be useful to edit from the Data series / Data Ranges window as I have also had to spend a ton of time clicking difficult to click lines (and many of them) just to adjust each of their appearance as I don’t know a better way to select and adjust their appearance.

Please upload your ODF type sample file here.

Just hit Tab, to select the next serie.

MOVED TO COMMENT FROM FORMER “NON-ANSWER” (my bad)

Sure thing, sorry for the delay I was under a big work crunch until today so I didn’t have a lot of time to produce a demo file

I have now built a small, vastly simplified demo file now attached “Errorbar_graph_example.ods” Here is the description so people can test things:

-one tab “Example Processed”

-Table “Graph values” at top left (start at cell B2) has the actual values for the graph. I have made them static for simplifying this example file but they used to be dynamic based on processing between 3 much larger tables.

-Table “Y-error bars” to the right of the above (start at cell R2) has the values for the Y-error bars to be added to the graph. These are a single set of errors, i.e. “positive values = negative values” which is how they should be added to the graph. This table has also been made static for simplifying this example file but was formerly dependent on number of larger tables.

-Graph “How Graph Should look (manually “click” added Y-error bars)” starts at Cell B21. This graph and its error bars are correct because each data series was individually selected and the error bar series was manually added to each one.

-Graph “Try Adding the error bars in a single step to this graph” starts at Cell B57. This graph is the example graph. It has the structure, source data, scale, etc… of the graph above, but no error bars have been added to this graph. How does one add error bars to this graph in a single step as opposed to laboriously clicking on each of the series zooming in and struggling to with the more difficult to reach ones?

I can select the “Try Adding the error bars in a single step to this graph” graph. Edit it. Do Menu->Insert->Y Error Bars… and select the values on the “Y-error bars” table. This adds error bars to this graph BUT THEY ARE WRONG!!!. One can check this by looking at the “How Graph Should look (manually “click” added Y-error bars)” and see that these may be the right values but they have been added to the wrong points on the graph.

Errorbar_graph_example.ods (62.0 KB)

Hi LeroyG,

I didn’t know about that trick. Thank you that will certainly be very useful for this type of thing in the future. At least it will for the smaller graphed data-sets like the one I attached. It will surely help on the larger one as well, but doing the larger ones manually even with your useful trick will still be kind of slow and painful – though thankfully less slow, and much-much less painful.

I see that if there is only one row of data, the error bars are correct. So, there must be a bug there.

Interesting. I am also wondering if their might be a bug in that I had to switch the X-Y graph to “Data in rows” from the default “Data in columns” to graph properly, but I a have an unproven sneaking suspicion that the error-bar sampling might not switch to “Data in rows” and remains trying to sample down the columns. Again this is an unproven suspicion as I am under a bit of a work crunch and thus haven’t had the time to experiment to prove or disprove it by making lots of little fake graphs with obvious Y-error bars of regular size differences to indicate their position visually on the graphs for the purpose of easy visual discernment.

When you are working with a diagram then it is a good idea to surround your data for the diagram with empty rows and columns. Alle labels belong to your data for the diagram.
In the way you want work with the erros you need for each value in the diagram table also a value for the error bar. You do not need data for the labels. So take care that you also surrouds this table with empty rows and columns.

The reason for the empty rows and columns: you can easy select the data or it can be done by the wizard.

I found it was a good idea to have made ready the values I need for the the error data so can only paste it.

Place the cursor in the table for the diagram
make a diagram Menu–> Insert → Diagram
Insert Y-error-bars : Menu → insert → Y Error Bars
Do this for the first row and paste there the range for the error-data or select it

I do have the idea that the small set of data is to much but that is to much but when I hide a lot of rows so I can see only the row I want. But that is an other problem.

In the documentation for Calc you can find some information for how it is working.

I hope I did understand your question.

I will add an example with your data and also a small example which I have used for understanding.
I did need some testing before I could click it all together. It seems to me you have to made each time a new diagram.

Errorbar_graph_exampleRPG03.ods (70.8 KB)
proefje_grafiek_met_error.ods (15.5 KB)

MOVED TO COMMENT FROM FORMER “NON-ANSWER” (my bad)
Hi RPG,

Thanks for your help. I loaded up your file with the Errorbar_graph_exampleRPG03.ods file. There are error bars on the points, but those are WRONG. If I put your graph in the same scale as the example one I have in the file I attached (its x-axis = logarithmic, scale is manual from min =0.0005 to max = 1.0) One can see the error bars you have added do not match the ones I manually put into the graph and checked. It is possible they are the right error-bar values, but if that is true, then they are attached to the wrong points.

I appreciate your help in looking at this, but your proposal is not a solution as the error bars are wrong relative to the data.

I donot understand the words as logarithmic and also I donot understand what kind of data it is. But that is not a problem.

I have the idea it is better to start with a new diagram and do use the wizard and also insert empty rows around the data. Then the wizard select the good data for the diagram. For the error data you have to select it your self. I spent a lot of words to this idea but it is important. Later you can do it on your own way.

Forgive but for some reason I do thrust on this moment more my idea then your original diagram. I did now plot also the data as labels.

I’ll take a look at the wizards

Your graph is correct all the way down to the Y-error bars, where their size is not correct for the correct points on the graph. I understand why you trust your work, but I have manually taken this apart and put it back together a couple of times now and poured over this in my files and data for multiple days taking things apparent and making smaller and simpler graphs to check all my work. Everything on your graph is fine – the logarithmic scale and the start points I listed are just adjustments to the X-axis to properly see all the data, their points and their error bars. Your chart is fine when these simple adjustments are made to the x-axis except the Y-error bars on your graph are not correct. I can prove this: one easy check, is that all the error bars on the “Media and Cells” data series should all be exactly the same size because it is all the same error (by nature of the design of the experiment), but on your chart they vary in size which is simply not correct as can be seen in the table where they are all the same. Checking the points on your chart across the other data series across the table for their relative size relative to the error table reveals the Y-error bars on your graph are mostly wrong.

I do look some more time to it but it cost me some time to understand all. But maybe other people do have an idea.
Your comments makes also more clear what is the problem.

I did now only show two lines. One of the lines is “Media and Cells” that makes clear what you explain. I have the idea that XY(scatter) is maybe only for one line.

I do add an example
Errorbar_graph_exampleRPG03.ods (33.9 KB)

When I display only one data-serie it seems to me that the good error-values are displayed. I do hide all data-series which I want not see. I do hide the rows in a spreadsheet.

I do not know an easy way to display more then one data-serie at the same time but it is maybe possible with a macro.

Thanks RPG, I’ll look into your suggestions and the macros.

In the meantime I submitted an error report to the Bugzilla site.

1 Like

tdf#163715
Errorbar_graph_example LeroyG.ods (32.1 KB)

Thank you for the link.

How do I make now the diagram with your original file from Bugzilla. But I have changed one thing. The value for “Media and Cells” is now 3. I can now see it more easy. Below you can read how I do make now the diagram there I do now know how to do but it more easy, but is not really changed.
You can still display only one data serie correct.

Make the diagram

I place the cursor where I want have the diagram

Menu → Insert → Diagram
Choose: XY(scatter)
Choose: Points and Lines

Select the tabpage Data Range
Data Range : Insert : $‘Example Processed’.$B$3:$N$14

Be sure that is select:
*Data Series in Rows
*First Column as label
*Click finish

The diagram is still in edit mode and does have a menu for diagrams. So you can insert Y-Error Bars
menu → Insert → Y-Error Bars

In the new popup:

  • Select Cell Range
  • Insert : $‘Example Processed’.$S$4:$AD$14
  • Mark : Same value for both.

Now for the scale for the X-as
*Set the scale for Logarithmic
Errorbar_graph_exampleBugzillla02.ods (58.8 KB)

I do agree with the problems who are describe by Nats and LeroyG. It did cost me some time to understand it. But working with the diagram it did learned me also: the diagram follows the table. In this case it seems also to follow the table for the error data. That the diagram does follow the table did I also learned a month a go on the Dutch forum.

This case leaned me now we must not drag the data to the diagram but past the data to a table between the original data and the diagram. I did make a small table for only 5 data-series. We have to make a good diagram in which we have to make the good connections but I believe that this is not a problem for Nats and LeroyG.

I have test this idea. I did make a database-document of the example-data.
In the datasource I did select 5 row.
Copy the rows
Delete the data in the small table
Paste the rows in the small table.

It cost me some time to understand it and also it cost time to work it out. I have also learned: frequent to start with a new diagram in stead of to correct a not good working diagram.

Thanks RPG, I’ll have to take some time to look at your diagrams and solution.

imagen

Which cells?

Which cells?