Interpolation of a Curve in Excel

ferrethouse

New Member
Joined
Mar 13, 2006
Messages
42
Basic statistics help please. I have the following values. I need to know what the value of the first column is given the value of the second column being 55.3 (sorry for the poor formatting "55.3" should be in the second column)...

2000 74.4
55.3
1000 18.2
850 13.5
500 6.5
425 5.5
250 3.9
180 3.3
150 3.1
125 2.8
75 2.2
63 2.1
44 1.8
31 1.5
20 1.2
16 1.1
11 0.9
7.8 0.7
6.6 0.6
3.9 0.4
2 0.2
1.3 0.1
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Take a look at the LINEST function. It allows you to interpolate data.
First, try plotting teh data as an X-Y Scatter and inserting a trend line. Play with linear and third-order fits; Back when I was in a lab we used third-order polynomials for curve fitting if it was meant to be linear -- it made allowance for variation in the readings and gave a better fit.
Once you know whether linear or third-order looks better, the LINEST function will let you specify so that you can calculate the interpolated value.

Denis
 
Upvote 0
Little bit of addition to Denis's suggestion:
Assuming data in column A are x-values and data in column B are y-values, its looks like you want x-value for known y-value. Since trendline gives you y = f(x) and you really are interested in x=f(y), you might want to plot column B as x-values and column A as y-values on chart and then pass the trendline. Make sure "Display equation on chart" is selected. You can use this equation to find out x-values for your known y-values.
Also, while playing with trendlines, you might want to try to extend the curve forward and backward several units to see its behavior and change the order of polynomial (or the curve type) if the behavior doesnt seem right.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top