Non Linear Scatter plot, need to find y value

I

iainprice

Hi,

There are lots of people asking this question but there is never a
answer that works. I have a non linear scatter plot. I want to find som
y values for given x values from the plot. A trend line will not work
Ananlysing exisiting data will not work, I want to read from the chart.

I have a little example attached.

I want to use 10 values to plot a curved scatter plot chart, then creat
a list of y values from given x values to plot another chart, this tim
a lonear chart to see if it close to the curved one.....

Any help would be great, thank you...

+-------------------------------------------------------------------
|Filename: Mine.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=650
+-------------------------------------------------------------------
 
I

iainprice

Update... I have managed to simulate this mostly....

I have some points, Excel creates a curved scatter plot, then I can rea
these interpolated points and plot a straight line version

The problem is that it is not flexible, I have to state all th
interpolated points I want between x values with a fixed gap. I want
solution that I can put data for x-axis 0, 10, 50,52,70,85...... an
then create a regular plot with x-axis values fo
0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,8

Excel needs to figure out how many interpolated points between th
points given and then use the correct parameters in the FEvaluate_Bezie

Any ideas? I think I might have to use VB to find the gap between
values and insert formulas into cells

+-------------------------------------------------------------------
|Filename: mine2.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=653
+-------------------------------------------------------------------
 
I

iainprice

Update, solved...

I used the FEvaluate_Bezier solution that Brian created and added som
of my ugly VBA to copy the correct formulae and values for the chart.

Now you can state x values for time (ten values) and the plot creates
curve, then the FEvaluate_Bezier reads back values for a regular ste
interval, then it plots these new values so you can see how straigh
line and rounding of values effects the simulation

+-------------------------------------------------------------------
|Filename: Done.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=655
+-------------------------------------------------------------------
 
C

christie.statistics

If you are happy with a linear interpolation, then paste
=FORECAST(D2,OFFSET($A$1,MATCH(D2,$A$2:$A$11),1,2,1),OFFSET($A$1,MATCH(D2,$A$2:$A$11),0,2,1))
into E2 in the
"Mine" sheet and copy down.

It has exactly the same effect as the "Done" sheet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top