Interpolate in a Lookup table 3

[MattGreer]

I have two columns of data, first is temperature, second is the density of water. Temperature goes by increments of 10 deg F. I would like to be able to interpolate between, say, 60 and 70 degrees, and then get the corresponding density.

Can this be done?

Thanks!!


Matt

 

[SkipVought]

Hi,

Sure! Just like you'd interpolate by hand.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

How would I pull out the values in the left hand column that bracket the desired value? The left hand column values might not always be 10 deg apart.

60
70
80
90
100
120
140
160

etc.

I'm thinking... The desired value is entered in the spreadsheet, and then you use equal to or less than to pick the lower (x0) value and then greater than to pick the upper value (x1). Or something like that. How do I do this? I'm looking at the Excel formulas (Excel 2007, by the way) and I see LOOKUP, VLOOKUP, HLOOKUP, but nothing seems to apply directly.

What I'm thinking is I take the x value I'm looking for and round it down? And then I get the index from the first number and add one to get the second? That make sense? Is that how you would do it? (Because you're a genius!!)

Thanks!!


Matt

 

[MattGreer]

roundup

and

rounddown

with a -1


Works just peachy. Thanks!

Thanks!!


Matt

 

[SkipVought]

Using Named ranges for Temp & Density, if the temp is in D3...
[tt]
D3: =INDEX(Density,MATCH(D3,Temp,1),1)+(INDEX(Density,MATCH(D3,Temp,1)+1,1)-INDEX(Density,MATCH(D3,Temp,1),1))*(D3-INDEX(Temp,MATCH(D3,Temp,1),1))/10
[/tt]
I had a table I constructed
[tt]
Temp Density
60 7.8
70 9.3
80 13.5
90 17.6
100 23.5
120 31.8
140 42.1
160 55
[/tt]
my temp was
73.8

interpolated density
10.896

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

I bow before the master.

I'm running into the problem with my solution where it rounds up or down to the nearest 10. This doesn't work if the index/temperature column is separated by more than 10. This also probably isn't going to work (haven't tried it yet) if the numbers in the left hand column don't end in zero.

I'll give your solution a whirl tomorrow. Thanks!!

Thanks!!


Matt

 

[SkipVought]

Temperature goes by increments of 10 deg F

Therefore, my solution assumes that.

Otherwise, simply calclate the temp difference just like the density difference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

Skip,

I used the INDEX and MATCH functions to achieve the desired result, regardless of the "span" between the first column values. Your formula is a bit different than mine, but, no matter. I get the right answer.

Thanks again for your help!

Thanks!!


Matt

 

[lionelhill]

Hm, this is probably not the right approach for this sort of problem.

You have a set of numbers that lie on a smooth curve. Instead of interpolating with straight line segments, you could better use Excel to fit the curve, and then calculate the correct value from the curve-fit parameters. Linest is probably the function you need.

The side-effects of sticking with the interpolation approach are:
(1) if the data really lie on a straight line, you are wasting effort interpolating. Just use the correct straight line.
(2) if they do not lie on a straight line, then the interpolation is not accurate.
(3) if your points are from measured data, then interpolation retains the errors on each measurement, while fitting an appropriate curve evens things out and gives more accurate answer.

Incidentally, if you do use linest and curve fitting, it would be very neat to add conditional formatting to flag up any answers that are off the end of the data-set and derived from dubious extrapolation.

 

[MattGreer]

It is highly dependent on the type of data you're looking at. If you're looking at an nth order curve, of course this wouldn't be recommended, unless of course you had enough data points where the first derivative changes.

What I have above does not require you to create a graph, then click on the graph and create a regression, then double check the regression to make sure it looks "OK", then manually type the equation into Excel somewhere, keeping all the needed digits, and then using that equation in your application. This has been my frustrating experience with using Excel's curve fit.

This isn't meant for some enduser somewhere. It's for me, and I interpolate often in my line of work. I have it plugged in my calculator but it would be more convenient to have it in Excel.

That being said, is there an automatic way to get Excel to put an equation into a cell for you so you don't have to reach around your butt to get to your elbow??

I'll look up this "linest" function you mention. If it does what I'm talking about, that would be quite awesome.

Thanks!!


Matt

 

[lionelhill]

Obviously it goes without saying that if you stick a 4th order polynomial through 3 points you are going to come to grief.

No, I wasn't suggesting that you actually plot a graph and create a regression, let alone start typing in values from the Excel regression annotations.

If you want a regression without plotting a graph, you can either use slope() and intercept() functions, which yield those values, or you can use linest(), which (if entered as an array formula) yields slopes and intercepts for line-fits of the sort y=a+bx1+cx2+dx3.... etc., together with a lot of useful statistical information about the fit. The Excel help on linest is a bit wobbly though.

If you want to do a curved fit using a known physical relationship, then you can also use Excel's "solver"; my approach is to set up "theoretical" values next to the measured ones (theoretical values based on a set of constants held in some cells somewhere), calculate the sum of squared errors, and ask Excel's solver to minimise this by modifying the constants of the curve. It's probably far from perfect, but I've found the solver fairly good.

You're writing for yourself, so you probably don't need to automate the actual solver, but on the one occasion where I had to do this for a non-savvy user, I vaguely remember I put a button on the worksheet that activated the solver with everything set up correctly, together with instructions in a text-box.

 

[MattGreer]

No, I wasn't suggesting that you actually plot a graph and create a regression, let alone start typing in values from the Excel regression annotations."

No no... I was just saying, that's what I had to do in the past and it was truly annoying. I was not/am not familiar with the function(s) you described.

I'm looking more into your information. I think I might be able to use it rather than my method, which would be great.

Thanks for the info! Star for you!

Thanks!!


Matt

 

[lionelhill]

ooh, thanks for that pink sparkly thing! Good luck with this. The only reason I push it so much (after all, interpolation isn't a bad technique!) is that line-fitting, the solver, and such features of excel don't get much advertisement.

 

[SkipVought]

lionelhill,

I agree! ==> [purple]*[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}