MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Matching and interpolating data


Posted by Jason on March 26, 2001 12:13 PM


It seems like no one understood what I was talking about last time.So Ill try again !!

I have a set of data as an example:

Yaw Pitch Cp13 Cp24
-35 30 6.71 0.48
-30 30 5.08 0.31
-25 30 4.15 0.18
-20 30 3.42 0.03


what I want to be able to do is say I enter a value : cp13=6.81 and cp24=0.39

I would want excel to search trough the data Ihave(which nis much more than that shown above) and return a value of yaw and pitch for these coressponding values
So for : cp13=6.81 and cp24=0.39
The returned values would be yaw = -33 pitch = 30

ie the values of cp13 and cp24 are interpolated to give approx values of yaw and pitch

Could someone please help me I have no idea where to start in Excel please!!
If anything is not clear please tell me and ill try and make it clearer!
Im sure somone some Excel expert know how to does this!!

Thanks alot!!

Jason Bassi

Jasonbassi@hotmail.com


Posted by Mark W. on March 26, 2001 12:20 PM

Jason, AutoFiltering would do the trick. Just
select a single cell in you data list and then
choose the Data Filter...AutoFilter menu command.
Once this filter has been applied you can click
on the drop down lists (see the little arrows)
and pick your desired cp13 and cp24 values.

Powered or sailplane?

Posted by Mark W. on March 26, 2001 12:25 PM

Wait a minute...


I thought I saw your Cp values in the sample data.
They're obviously not there. AutoFilter won't help
you. Let me mull this one over.

Posted by Mark W. on March 26, 2001 12:28 PM

Request...

Jason, please provide a formula relating
Yaw, Pitch, Cp13 and Cp24.

Posted by Jason on March 26, 2001 2:32 PM

Sorry could you please elaborate on autofiltering!

and the data filter!

In response to your first question, i dont know the formula for the relationship between yaw, pitch to the cp values

but with a plotted graph it is an approx linear relationship

but the whole point of using the data is too work out the required yaw and pitch for any given cp values, you see there are about 1000 values which I have taken experimentally!!
Its all for the clibration of a 5 hole probe in a wind tunnel!
Thanks !
Jason

Posted by Mark W. on March 26, 2001 2:44 PM

Jason, your request can be done with an AutoFilter
as I first suspected. I'd just be guessing at the
relationship of these values. What you need is an
aeronautical engineer. How did you derive -33 from
your example?

Posted by Mark W. on March 26, 2001 2:45 PM

Oops, Typo!

Jason, your request CAN'T be done with an AutoFilter
as I first suspected. I'd just be guessing at the
relationship of these values. What you need is an
aeronautical engineer. How did you derive -33 from
your example?

Posted by Aladin Akyurek on March 26, 2001 2:54 PM

Mark & Jason: What about the following?

They're obviously not there. AutoFilter won't help you. Let me mull this one over.

Assuming the range A1:D5 (including the labels).
Sort the data on columns C then D.

Enter cp13=6.81 and cp24=0.39 in F1 and G1.

=AVERAGE(INDEX(A2:A5,MATCH(F1,C2:C5,1)),INDEX(A2:A5,MATCH(G1,D2:D5,1)))

=AVERAGE(INDEX(B2:B5,MATCH(F1,C2:C5,1)),INDEX(B2:B5,MATCH(G1,D2:D5,1)))

This system of formulas produces

a yaw of -32.5 and a pitch of 30.

I get for Cp3=4.15 and Cp4=0.18 the following values: -25 and 30.

I assumed linearity. Working with log values I get the same values. I don't know whether using just 2 numbers to interpolate can be considered sufficient.

Aladin

Posted by Jason on March 26, 2001 4:58 PM

Re: Oops, Typo!

hi again
Ive had a look at autofiltering, It wont work
because the cp values given wont be exactly the same as those in the data and the cp values given need to be interpolated to give good values of yaw and pitch
you see the data which i have has to be used for this interpolation, I think a Macro needs to be made which searches through this data finds the nearest points to the cp values given and uses these nearest points to interpolate to give values of pitch and yaw! (there should be 4 points from which to interpolate ie you could say a quadalatarile(4 sided shape appox rectangular according to my data)

but I have no idea about programming !!
im an aerospace undergraduate in my 3rd year they didnt teach us anything!!
I now I have this project with this small thing to do but I cant!!

Mark I hope you know your programming! or anyone else!!

thanks Jason


Posted by Mark W. on March 26, 2001 6:15 PM

Re: Oops, Typo!

Jason, I think this can be solved with a simple
Excel formula. Maybe there's something in your
text books that'll provide some clues about the
relationships between these variables.

Posted by Mark W. on March 27, 2001 7:18 AM

I'm just reluctant to proceed because...

I'm not an aeronautical engineer, and I think that the
info needed to produce the correct formula exists somewhere...
perhaps, n a text book. I'd hate to be responsible
for an airliner going down! ; )

Posted by Bruce Mu on July 24, 2001 4:38 AM

It seems like no one understood what I was talking about last time.So Ill try again !! -35 30 6.71 0.48 -30 30 5.08 0.31 -25 30 4.15 0.18 -20 30 3.42 0.03 what I want to be able to do is say I enter a value : cp13=6.81 and cp24=0.39 I would want excel to search trough the data Ihave(which nis much more than that shown above) and return a value of yaw and pitch for these coressponding values So for : cp13=6.81 and cp24=0.39 The returned values would be yaw = -33 pitch = 30 ie the values of cp13 and cp24 are interpolated to give approx values of yaw and pitch Could someone please help me I have no idea where to start in Excel please!! If anything is not clear please tell me and ill try and make it clearer! Im sure somone some Excel expert know how to does this!! Thanks alot!! Jason Bassi Jasonbassi@hotmail.com

Posted by Bruce Mutton on July 24, 2001 4:41 AM

It seems like no one understood what I was talking about last time.So Ill try again !! -35 30 6.71 0.48 -30 30 5.08 0.31 -25 30 4.15 0.18 -20 30 3.42 0.03 what I want to be able to do is say I enter a value : cp13=6.81 and cp24=0.39 I would want excel to search trough the data Ihave(which nis much more than that shown above) and return a value of yaw and pitch for these coressponding values So for : cp13=6.81 and cp24=0.39 The returned values would be yaw = -33 pitch = 30 ie the values of cp13 and cp24 are interpolated to give approx values of yaw and pitch Could someone please help me I have no idea where to start in Excel please!! If anything is not clear please tell me and ill try and make it clearer! Im sure somone some Excel expert know how to does this!! Thanks alot!! Jason Bassi Jasonbassi@hotmail.com
Jason
Try this code from JWalk. I was going to write this tomorrow, but then decided to look around...
Created by Myrna Larson

An UDF that returns values "between" the points in the lookup table.

Function InterpolateVLOOKUP(x As Single, Table As Range, _
YCol As Integer)

Dim TableRow As Integer, Temp As Variant
Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double
Dim d As Double

On Error Resume Next
Temp = Application.WorksheetFunction.Match(x, Table.Resize(, 1), 1)
If IsError(Temp) Then
InterpolateVLOOKUP = CVErr(Temp)
Else
TableRow = CInt(Temp)
x0 = Table(TableRow, 1)
y0 = Table(TableRow, YCol)
If x = x0 Then
InterpolateVLOOKUP = y0
Else
x1 = Table(TableRow + 1, 1)
y1 = Table(TableRow + 1, YCol)
InterpolateVLOOKUP = (x - x0) / (x1 - x0) * (y1 - y0) + y0
End If
End If
End Function