Excel Help (Interpolation)

Aero

Aero

Aero

Soldato
Joined
19 Sep 2007
Posts
3,149
Hey people!

I kind of need some help with a formula I'm trying to put together for a spreadsheet but I am having some difficulty with it.:(

The basic problem is this, I have a table (below) which I need to interpolate the vales from but I am unsure as to how to do it in excel.

Aspect Ratio | 1.00 | 1.6 | 3.00 | 5.5 | 7.5 | 13.5 | 20 | 30 |
Force Coeff | 1.26 | 1.3 | 1.35 | 1.4 | 1.5 | 1.6 | 1.7 | 1.8 |

Thats the basic table (I hope it displays ok)

I was trying to make it work using the IF function but that wasn't working out well :rolleyes: any advice people as I'm really quite stuck on this!!!

Potential reward if you can sort it out :eek:

Aero
 
Sorry i didn't really clarify that, its a linear interpolation e.g. If I had a value for the aspect ratio of 1.3 the force coefficient would be 1.28.

Its for wind loading on a sign (exciting stuff I know!)

Thanks for any help

Aero
 
There's no simple way to do this in Excel unfortunately. You could try and interpolate linearly between individual data points, but you need a lot of points if the series is large to make this work.
General engineering practice is to plot a curve of best fit and read off that with a pencil and ruler. Adding gridlines really helps with this!
If you don't believe me, have a look at the ESDU data sets, Roskam, Torenbeek... Course, they're largely from pre-PC days, but unless your lecturer has specifically asked you to get Excel to interpolate reading the results by hand is fine.

EDIT: gawd but I'm slow this morning. Haven't plotted your data myself, but if it's linear it's generally acceptable to use y=mx+c (using gradient and y-intercept read from graph).
 
Last edited:
I realise that there isn't a simple why to sort this but I do need it in excel the interpolation actually isn't a problem for me but I am trying to design a spreadsheet that will perform a number of calculations and this is just 1 in a step of about 30.
 
I looked at the Trend function when I seen the OP but it doesnt give the values required. if you actually plot the points and use Trend it doesnt return the matching values.

Are you just able to plot all the data by hand in excel then just use a lookup function to bring back your results?
 
What's the actual equation that relates x to y in the first place? Trend can smooth the points out slightly due to it's approach (least mean squared) but the normal reason it doesn't produce good enough data is that the constant hasn't been entered correctly.

On the other hand, if the above is experimental data, then I would expect some variations from the trend line.
 
What I think you need to do is use the FORECAST(x, known_y's, known_x's) function, where:

x = your new aspect ratio that you want to find a co-efficient for
known_x's = the two aspect ratio's each side of 'x'
known_y's = the corresponding two co-efficients for those known_x's

I'll have a look writing at an example for you. Give me a few minutes :)
 
Danger Will Robinson!

While it is an interesting and worthwhile exercise to find out how to use Excel to generate the equation for a specific best fit curve, it is rarely used in practice (at least in engineering). Check with your lecturer to see if this is the road he wants you to go down - as you say, having this as step one of thirty seems a tad excessive.

Out of interest, what are you trying to find? I'm assuming it's finding velocity across a flat plate at an angle of attack? If your data is from an experiment, just plot the data and read by hand as necessary. Finding the equation of the curve for the best fit line will NOT increase your accuracy in this case!
 
div0 said:
I'll have a look writing at an example for you. Give me a few minutes :)
Click to expand...

Ok I've got something that works, but it relies on a few things - but it's nothing you can't work around.

Assume column A is your 'Aspect Ratio' (sorted in ascending order)
Assume column B is you 'Co-efficient'

You're looking to achieve this:

FORECAST(x, known_y's, known_x's)

I am assuming that 'x' is entered at the bottom of your list of 'Aspect Ratios' (eg A10) - but it can be any cell you want.

You need to have 4 more cells that calculate your 'known_x's' and 'known_y's' - which are the values either side of your 'x' value.

To do this use the following formulas:

[NOTE: this assumes that 'x' isn't already a value in your known list!)

(D1) known_x_below: =VLOOKUP(A10,A2:A9,1,1)
(D2) known_x_above: =HLOOKUP(D1,A2:A9, 2, 1)
(E1) known_y_below: =VLOOKUP(D1,A2:B9, 2, 1)
(E2) known_y_above: =VLOOKUP(D2,A2:B9, 2, 1)

Code: 
(B10): =FORECAST(A10, E1:E2, D1:D2)

This seems to do what you want (ie when you enter 1.3 for the 'Aspect ratio', then it gives 1.28 for the Co-efficient.
 
Last edited:
Hey guys thanks for the effort I have now sorted it by using the Vlookup and basically writing out all the values of the table to 2 decimal places.

Thanks a lot!

Aero
 
With the above method (Div0's), do consider that the data is not perfectly linear, so taking only the data points either side of the forecasted point may not be the best solution.
 
Dolph said:
With the above method (Div0's), do consider that the data is not perfectly linear, so taking only the data points either side of the forecasted point may not be the best solution.
Click to expand...

I only did it that way, because in the example he gave (see below), it looked like that was the type of interpolation he wanted to apply ;)

Sorry i didn't really clarify that, its a linear interpolation e.g. If I had a value for the aspect ratio of 1.3 the force coefficient would be 1.28.
Click to expand...

The value of 1.28 is achieved by taking a linear interpolation of the values either side of 1.3.

My method may not give the 'best' solution - but it does what he asked for :p :D

But I agree it's worth pointing out that he may want to consider other ways of interpolating, to get a 'better' result.
 
You must log in or register to reply here.
Back
Top Bottom