Matching and interpolating data

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?

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.

Request...

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

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

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?

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?

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

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

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.

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! ; )

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

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