Your assistance would be much appreciated.

I have created a combination chart as follows:

The line graph (XY scatter) is many thousands of points.

The individual points have the correct x values but the y values are just sequential numbers. What i want to do is change these so that the points sit ‘on’ the line, but i have no idea how. Calculating them manually isn’t an option.

Thanks!

Ummm, a boring first post for me!

Ok, it’s fairly straight forward if I understand you right….

Create an XY scatter chart, then click on the ‘series’ tab. Click ‘add’.

You can then specify the actual X and Y values for your chart rather than just one axis and a fixed, equal sequence that Excel would default to otherwise.

Rock and roll eh?

Thanks Squarewheels, but i obviously haven’t explained myself very well. I don’t actually know what the Y values for the points series are – i want them to sit on the line (i.e. the y values of the two series would be equal), but as it is such a large dataset i can’t go through and figure it for each point manually.

Also the x values of the two series don’t correspond exactly.

Sorry, v. hard to explain.

Not sure what you mean by ‘sit on the line’… is it possible to post a very small sample of the numbers as they are and what you want them to do? I’m struggling to understand (sorry!)…

Okay, the first few points of the line graph (=series 1) are:

90.5, -47.19
92, -42.20
93.5, -34.74
95, -27.02
96.5, -27.59
98, -27.81
99.5, -23.84
101, -20.86
102.5, -21.18
104, -27.79
105.5, -31.61
107, -38.03
108.5, -44.62
110, -44.72

And the first few “points” (=series 2) have x values of:

91.87
105.39
125.76
95.14
90.68
108.97

In the silly picture i drew above, i’ve plotted the series 2 points with arbitrary y values. What i actually want is a y value that makes the point plot on top of the line (series 1 y = series 2 y). I can’t go through and calculate this manually for each point in series 2 as:

a) there are hundreds of them and
b) the x values of the two series don’t match up, so some of the points will be sitting on interpolated sections of the line.

Feel free to admit defeat! (and welcome to the forum by the way :D).

How about plotting all the Series One data then under the “Chart” menu use “Add Trendline” and select the “Display equation on Chart” option. Now use this equation on your Series 2 X values to find the corresponding Y values… simples.

Ok, I understand now. Basically, you want to be able to know what a Y co-ordinate for the ‘points’ would be that sits on the line of the X-Y.

So far as I am aware, there is no proper way to do this. A work around is possible: if you aren’t too fussed about accuracy and the line isn’t too spikey, then you could get Excel to add a trend line and tell you what the equation for it is. Once you have the equation for the line, you could plug in the X value for the points and it will calculate the the Y value.

If that sounds like it might help then I can describe how to do it.

Cheers….

This wont work if the x values dont coincide.

I suggest rounding the x numbers in the second series to nearest 0.5

Create a table of x values from 90.5 to end in 0.5 increments

LOOKUP table against first list to insert y value where they exist
and agains

LOOKUP table aginst second list to insert matching x values in seperate column and then against second list for matching y values

combine the two columns and plt those figures

I’ll have ago and see if that works

Using your sample data for Series 1 the equation (2nd Order) you require is;

y = -0.2605×2 + 52.181x – 2636.3

Use this equation in your spreadsheet to find the Series 2 values for Y and plot on your graph.

You could use a 3rd or 4th order equation for greater accuracy.

Cotic853 – liking the sound of that, i’ll give it a go.

crankenstein – unfortunately unlike the snapshot of data i posted the line graph series repeatedly oscillates from -ve to +ve and is pretty chaotic, so you can’t fit a convincing trendline to it.

Thanks very much for your input all.

Cotic853 – does work, just depends on deired accuracy.

As cRaNkEnStEin said…. that’s what I got too.

By this method the values would be….
91.87 -41.07677245
105.39 -30.33148205
95.14 -29.7465658
90.68 -46.5825752
108.97 -43.43349445

Better accuracy with optional higher order polynomial. But as mentioned, depends on what the rest of the data does and how bothered you are by accuracy.

Hey hey!

(its a sea-level curve for the past 400 or so thousand years by the way, and the points are the ages of sand dunes on the south african coast…)

sorted I think, YGM

(its a sea-level curve for the past 400 or so thousand years by the way, and the points are the ages of sand dunes on the south african coast…)

Fascinati…. ZZzzzzzzzzzzz

😉