[Solved] Plot multiple series, stretch values to fill X-axis

[bootsmaat]

Hello,

first of all, I know this is the OpenOffice Forum and I am using LibreOffice. I could not find a Forum for LibreOffice and I know the two projects are very similar so I hope someone can help me here?

I have two series of values which I want to plot in a line chart. Series A has 10 values. Series B has 15 values. I want to plot them in a way that the length of their graphs is equal. In other words, Series B has to fit its 15 points in the same width as Series A fits its 10 points. In even other words, the horizontal distance between points of Series B has to be smaller than the distance between points of Series A.

I can plot the two series, but Series B just exceeds Series A by 5 points. As I said before, I need Series B to be shrunk to the same width as Series A.

Edit for clarification: The two mentioned series are just an example. I am looking for an approach that shrinks/expands multiple series to the same width.

How can this be done?

Thanks in advance!

[MTP]

I know the OpenOffice label is in big letters, but there are small letters that say "User community support forum for Apach OpenOffice, LibreOffice and all the OpenOffice.org derivatives". Welcome

I don't think it's possible to have a secondary x axis in the way that Calc allows a secondary y axis. While the axis can be viewed (Insert -> Axes and check the box for secondary x-axis), it appears to be just the same as the primary x-axis with no way to assign series to it. The feature request for use of a secondary x axis was marked as "fixed" in 2007, but I haven't figured out how to use it and posts asking about the secondary x-axis have gone unanswered. Excel does have this feature, so if you really need it Microsoft Office might be the way to go.

I see two possible options if you stick with Calc. One is to "spread out" the values of the series with fewer data points, leaving blank cells so that the last data point of both series is in the same column (or row, if your grid is in rows). The second is to graph the series sideways to take advantage of the two different y axes feature. You could even rotate most of the labels (I couldn't find a way to rotate the legend box), so that when printed (without the legend) the graph could look like it had two x axes. I've attached a small file showing these options.

[MrProgrammer]

Hi, and welcome to the forum. Thanks for the interesting question.

Series A has 10 values. Series B has 15 values. I want to plot them in a way that the length of their graphs is equal.

I don't know how to do it with a line chart but you can use an XY Scatter chart. This chart type allows you to control the placement of values on the X axis.

201402240943.ods

(28.76 KiB) Downloaded 229 times

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know.

[bootsmaat]

I know the OpenOffice label is in big letters, but there are small letters that say "User community support forum for Apach OpenOffice, LibreOffice and all the OpenOffice.org derivatives". Welcome

Thanks!

I see two possible options if you stick with Calc. One is to "spread out" the values of the series with fewer data points, leaving blank cells so that the last data point of both series is in the same column (or row, if your grid is in rows). The second is to graph the series sideways to take advantage of the two different y axes feature. You could even rotate most of the labels (I couldn't find a way to rotate the legend box), so that when printed (without the legend) the graph could look like it had two x axes. I've attached a small file showing these options.

Sorry for not making this clear, but I actually have more than two series. (Series A and B were just an example.) So the secondary x-axis approach will not work and neither will the secondary y-axis. I updated the original post to clarify.

[bootsmaat]

I don't know how to do it with a line chart but you can use an XY Scatter chart. This chart type allows you to control the placement of values on the X axis.

Thanks! This looks very good.
It looks like you used some math to inerpolate the "missing" values of series A, correct?
Could this approach be expanded to plot a large number of series (say, 30)? Or will the math not work then?

[MrProgrammer]

It looks like you used some math to inerpolate the "missing" values of series A, correct?

No. I didn't calculate/estimate any new Y values. The chart contains only 10+15 points. This is easier to see if you double click the chart and use Format > Chart Type > Points Only. If you want interpolation, enable XY Scatter > [Points and] Lines > Smooth Lines.

Could this approach be expanded to plot a large number of series (say, 30)? Or will the math not work then?

I did this for two series, as you indicated in your topic, but you can add more series. The method used in my first attachment will work until the LCM in cell E4 exceeds the number of rows allowed in a spreadsheet, presumably about a million for your installation. However there is nothing to be gained by using any horizontal positioning finer than your screen resolution supports.

Let's say your display supports 1680 pixels horizontally. Use Edit > Fill > Series to put integers 0 through 1680 in a column. In the next column, distribute the values from your first series evenly in its 1681 cells. The attachment below does this with formulas. Repeat for other series, then use Insert > Chart > XY Scatter. For series with 10, 15, and 18 values, these need to be distributed about every 187, 120, and 99 cells respectively. Note that the last value in each series is placed in row 1682. You'll probably want to remove the labels from the X axis. Read about functions COUNT/MOD/OFFSET and working with charts in Help > Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

Edit: #1 To be precisely correct I should have 0 through 1679 (1680 values) in column H, and F6 through F8 should be =($F$2-1)/F3, =($F$2-1)/F4, and =($F$2-1)/F5. Calc and the video hardware will manage the display either way. Your chart probably won't extend the entire width of the screen anyway, and will have to be scaled, so 1681 values will work just as well as 1680.

In case you're wondering, the series I used for the attachment are Bessel functions, J₀, J₁, and Y₀.

One is to "spread out" the values of the series … leaving blank cells so that the last data point of both series is in the same … row ….

That's the approach I use.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Edit: #2 The attachment didn't attach because it was too large. I reduced the number of values from 1680 to 1280.

[acknak]

OO Chart also seems to support multiple sets of x-y points so it would be possible to provide a calculated series of x values that spread evenly over whatever range you want.