Excel 2007 : Need the help of a math whiz for interpolation formula

**BigGPL** · 03-25-2012, 08:13 AM

Morning

My math skills suck! I'm lucky if I get 4 if I add 2 + 2. So I am in need of someone who speaks math as a second language.

The given:

Airplane's compass correction card showing deviation correction for heading.

FOR.....STEER....DEV.
0............0..........0
30.........33.........3
60.........62.........2
90.........95.........5
120......120........0
150......155........5
180......176.......-4
210......210........0
240......243........3
270......270........0
300......303........3
330......325.......-5

Every compass is affected by the electrical equipment and the ferrous materials near the compass. So in order to make a heading FOR 60 degrees, allowing for deviation, you would need to actually STEER 62 degrees. etc.

What I would like is to have a formula that would interpolate the deviation for the headings between that of those shown on the correction card.

So if the user enters the value of 115 in A1 then B1 would show the deviation and C1 would show the corrected heading.

Much appreciate any help on this.

George

**DBY** · 03-25-2012, 09:59 AM

Hello
I'm no maths wizard either but do you just require to lookup the values from your correction card? For example what would be the value for 115?

Having read MrShorty's detailed and informed reply I now realize it's more complex than a mere lookup problem so please ignore this post.

Regards DBY

**MrShorty** · 03-25-2012, 10:23 AM

Can I assume you want a linear interpolation.

The hardest of a linear interpolation problem is the lookup part -- how do I find the interval containing the given x. I think the MATCH function does this job well.

Once you have located the interval, the INDEX function will easily return the x and y values for the interval determined from the MATCH function

I would use MATCH and INDEX to build a small, 2 entry table off to the side of the main table. then you can use the TREND function to do the interpolation.

I'm not a fan of building big complex formulas like this into a single cell, so, if that's what you want, someone else will have to help you combine all this into one.

I also might add, if you have access to QuattroPro instead of Excel, they had a nice built in linear interpolation formula that would handle this problem easily.

**BigGPL** · 03-26-2012, 07:19 AM

Hey Guys

Thanks for the replies.

I've been tinkering around and came up with the attached file.

The problem I have is that it needs to return values between 0 and 360. So in other words if the magnetic heading is 358 degrees and the deviation is 4 then the new heading would be 002 degrees and not 362!

Any Thoughts

**BigGPL** · 03-26-2012, 09:11 AM

Crap!
I goofed.

Please remove the Data validation from D20 in the attached file I provided in the previous post.

George.

**MrShorty** · 03-26-2012, 09:48 AM

For reasons on my end, I can't download your file, so I can't see exactly why you are getting a +4 result for 358, so there might be a more elegant solution that I can't see.

The easiest solution might be to simply put the final result inside of an IF() function to handle those cases. =IF(finalresult>360,finalresult-360,finalresult)