Any Excel gurus out there that can help me out???

[Eskimo Pie]

I have a data set at work that I'm trying to interpolate a bunch of new values for and I can't get Excel to give me what I need... Please help.

Say I have a data set of X and Y values:
X Y
5 5
10 9
15 7
20 8
25 3
30 6
35 5
40 4
45 6
50 9

If you plot that out you get a nice little jagged line... Now say I want to get the exact values on that line for the X values of 11,12,13,14,15,16,17,18,19, and 20. How do I get excel to interpolate my existing data set with the new X values to give me new Y values??? I mean Excel is plotting the damn line, it knows exactly where any given X value is going to hit that line and should be able to give me the corresponding Y value but for the life of me I can't find the function. This is a dramatic over-simplification but I think the question is clear enough... For instance given the data set above if I want to know value at X=42.5 then it should be Y=5... but I can't figure out how to make Excel tell me that. HELP Please!

 

[arkamw]

Here's my thinking on this. While Excel is great at being a spreadsheet and can readily graph those numbers, I don't think the function for interpolating numbers on the graph is built in. While it's true that Excel does draw the line, it doesn't draw every point between point A and point B. All it's doing is just drawing the line itself between the two point that you have given it. Excel doesn't have the same functionality (built-in) that, say, a graphing calculator does (if I am guessing your intent here).

I think to do what you want to do, you are going to have to setup the formula (y=mx+b) and then provide your own complete number set for X, either through coding or an auto-fill, etc.

Having said this, note that I haven't seen everything that Excel can do. I just think that if you are going to build a spreadsheet for the purpose of inputting graph numbers, and Excel is very good at calculating, why not just fill in the entire number range you are looking for and let Excel do the rest of the work?

 

[Florida Doc]

Here's one way to do it. Plot the data in a graph. Right click any point in the graph and click Add Trendline. Choose the linear trendline and select the option for show equation. Now go back to your original spreadsheet and use this equation to calculate the values.

It's not automated but it's very easy to do and doesn't take much time.

 

[zlooop]

Doc said the best way to do it. There are better stat programs that you should be using to interpolate.