interpolation function?

[UncleBose]

Hello all.
I'd like some function to return a value from in between known values in a range.

<pre> A B C
1 114 90 175 <---user entry
2 123 98 135 <---returned value
3 132 105
4 148 119
5 165 129
6 181 138
7 198 146
8 220 155
9 242 159
10 275 164
11 319 167</pre>

These relationships are nonlinear. Column B is dependent. I want to be able to enter any number within the A range into C1, and return a properly interpolated value from within the B range in C2. For example, entering 175 should return something close to 135. (175 is between 165 and 181 in A, returning something between 129 and 138 in B. I did that one on paper.) Of course, if some number that is actually known in A is entered, the corresponding value in B should be returned.
I guess I don't understand the FORECAST function, because using the exact ranges as in this example it returned 129.1 for 175.

 

[venkat1926]

HIGHLIGHT the values in columns A and B and draw a xy scatter diagram
values in column A will be in x axis and col B will be in y axis
at first locate the chart in the sheet itself.

click the points in the chart. go chart menu in the menu bar.
click ADD TRENDLINE. In the trend line window click logarithmic (second chart in the first row) and choose OPTIONS and check DISPLAY EQUATION ONCHART and click OK.
now a smooth curve will be drawn on the chart and
the equation will be on the chart like this
y=79.667Ln(x)-281.25
here Ln means logarithm to base e(exponential)
in any cell type this formula (for x value 175)

=79.667*LOG(175,2.7182818)-281.25

it comes to 130.213.

You can experiment with various types of charts depending upon how your scatter diagram looks.
+

 

[tusharm]

If step-wise linear interpolation is OK with you see
Interpolation
http://www.tushar-mehta.com/excel/newsgroups/interpolation/index.html

If not, you may be able to adapt the ideas in the above article to your own needs. Alternatively, if a regression is OK with you, compute a non-linear one and use it. See
Trendline Coefficients and Regression Analysis
http://www.tushar-mehta.com/publish_train/data_analysis/16.htm