# Plotting a graph with 2 sets of very different number of values

1. ## Plotting a graph with 2 sets of very different number of values

Hello,

I would like to create a graph.

On y axis, I have a lot of data to put in and on x axis, I have only about 10 values to put in.

How do I plot it?

I am attaching my excel file for clearer view.

I am trying to plot column H on y axis and column I on the x axis.

Thanks in advance and have a nice day!  Register To Reply

2. ## Re: Plotting a graph with 2 sets of very different number of values

In many ways, this seems like the same problem in your other thread: https://www.excelforum.com/excel-cha...variables.html For a scatter chart especially, Excel expects an x value for every y value. As before, the basic idea is to come up with a strategy (because Excel does not "know" anything about how to reconcile number of data points) to get the same number of x and y values.

In the previous thread, we appear to have settled on a lookup strategy that effectively eliminated all of the data from the larger data set that did not correspond to the x values from the smaller data set. I suspect that this strategy won't work here (but I could be wrong) because that will remove a lot of data from the chart. But I also don't see anything in your sample file or description that suggests a different strategy.

Help us understand the strategy you want to use, and we can help you program that strategy into the spreadsheet. I'm not sure we can choose a strategy for you.  Register To Reply

3. ## Re: Plotting a graph with 2 sets of very different number of values

Hi thanks for helping me again!

So I would like to fit all my y axis values in the space of the x axis values.

Would there be a way for the y axis data to evenly spread out in between the first and the last x axis values?  Register To Reply

4. ## Re: Plotting a graph with 2 sets of very different number of values

I'm sure there is a way to do what you want, but I don't think I understand. What do you mean by spreading the y axis data out evenly between the x axis values?  Register To Reply

5. ## Re: Plotting a graph with 2 sets of very different number of values

Hmm.. think of the X axis as a timeline and the y axis as something you download.

In that timeline the download will start from 0 and finish at 100%.

In this case, the timeline ends at 15.34.

What I have done is that my column H is the area under the curve of that load vs displacement graph and I am trying to plot that against another length.

I am not too sure how I should explain this.  Register To Reply

6. ## Re: Plotting a graph with 2 sets of very different number of values

So, have row 1 correspond to x=0 and row 1257 correspond to x=15.34 and "interpolate" everything in between????

1) Enter 1 in K1, 1257 in K2, 0 in L1 and 15.344161 in L2
2) Enter =TREND(\$L\$1:\$L\$2,\$K\$1:\$K\$2,ROW()) in J1 and copy down to J1257?

That's a wild guess that ignores the other x values you have listed in column I, but it's the best I've got so far.

You talk about the values in column H being "areas under a curve" Is there something about the geometry or calculus or engineering behind these numbers that might shed some light onto what "x" really means?  Register To Reply

7. ## Re: Plotting a graph with 2 sets of very different number of values

Okay, so the values in column H were calculated using the excel formula that I found on the internet.

The reason why I calculated the area under the curve is that N/mm would be equivalent to kJ/m^2.

This means that load/displacement (which I plotted on the other graph in the same excel sheet) is equivalent to the energy release rate.

And I think calculating the area under the curve would give me the values of the y axis of the graph that I want to plot now.

I am not sure what you mean by "what x really means", but my x axis value is the crack length.

So as the crack length increases, the energy release rate would increase too until some point, and when the crack propagates the energy release rate would drop dramatically.

I hope this kind of explains what I am trying to do and clear some doubts.

I will try doing what you said above, thank you so much for helping me!

edit) I have just tried plotting the graph and the graph does not look like how I want it to look like, so I don't think I am going to use the graph as my results Sorry for making you do some unnecessary work. Thank you so much for helping me for the last couple of times!  Register To Reply

8. ## Re: Plotting a graph with 2 sets of very different number of values

Hi!

I realised that the graph that I want to create is like a graph of load vs displacement but 90 degrees tilted to the left.

I don't know if you would understand what I mean so I am attaching a picture.Attachment 731253

Would you know how to tilt the data like that?  Register To Reply

9. ## Re: Plotting a graph with 2 sets of very different number of values

Your picture didn't attach. Be sure to follow the procedure in the yellow banner when attaching files (including pictures).

I don't know how much I will understand anyway. I remember high school physics and doing some "force balance" and similar statics problems, but what you are doing appears way beyond anything I did.

Your current charts are basic scatter charts. My first guess for "tilting" a chart 90 degrees is to switch the Xvalues and Yvalues ranges (Select Data dialog or equivalent) and maybe formatting the x axis so that the values are in reverse order (format axis dialog).  Register To Reply

10. ## Re: Plotting a graph with 2 sets of very different number of values

Oh, okay I have put the image at the top of the thread!

I don't quite understand what you mean there. I hope the image helps you understand my problem.

Oh and also, do you know how to change some of the line into dotted line like the attached photo above?

Thank you so much for helping me! I really appreciate it a lot.  Register To Reply

11. ## Re: Plotting a graph with 2 sets of very different number of values

I'm not sure what I'm looking at. If I assume that the right chart is related to the left chart, it looks like there is some kind of "transformation" going on, where you have a transformation that converts P(N) into Gt and another tranformation that converts d into Delta-a. I have no idea what those transformations would look like, but assuming that you do, Simply add a Delta-a and Gt columns with appropriate formulas that perform the transformation.

As far as different colors, the usual approach is to have each "color" be its own data series (so it will also have its own column in the spreadsheet). See tutorial here: https://peltiertech.com/conditional-...-excel-charts/  Register To Reply