Microsoft Excel help

[Aztec Driver]

I have an excel spreadsheet and graph which gives me all of the information I need about my weight and balance for my airplane. I have set up the graph so that all I do is enter the weights and position of people, cargo, fuel, etc and it plots it visually on the graph to instantly tell if I am within the aircraft limitations.

Its great, except... for charter flights, the manifest is written up requiring the forward and rearward CG range at the given weight. Not hard to guesstimate visually, but I would prefer to find a way to have the value pop up automatically like all the other values. Basically I need a way for the graph to output the value of the CG where the weight line crosses the forward cg limit line.

Anyone know how it might be done?

Bryon

 

[Areeda]

This is an interpolation problem and quite a pain if the fwd cg has too many kinks in it.

let's say the fwd cg is 100 from BEW to 1000# and 200 from 1000-2000 (silly numbers I know). Now the fwd cg is in columns A and B and the current weight is C1

So column A looks like 500, 1000, 2000 column B 100,100, 200

The formula looks like =if(C1<A2,B2,(B3-B2)*(C1-A2)/(A3-A2))

I did not test this so it may not be perfect.

Joe

edit: Hope that's clear. If not, post your spreadsheet and I or one of those much smarter than I will do it for real.

 

[Aztec Driver]

Here is the spreadsheet and graph. It is an excel sheet originally authored by Ryan Ferguson, and I modified it to suit my needs.

Well, apparently I cannot upload an excel file. Let me figure this one out first.

 

[Cap'n Jack]

Upload it in a zip file.

 

[Clark1961]

Here's a general purpose linear interpolation routine that I put together a few years ago. It's a bit of overkill for this problem but it'd work.

Give the interpolation function, IterpVal, a table in the form of a range of X values and a range of Y values and then give it the value for which you want to interpolate a particular Y value and it'll do the rest.

In this case, the X range would be weights and the Y range would be their associated minimum CG. After those ranges are defined then give the function the actual aircraft weight and it would return the minimum allowable CG. I suspect you've already got the weight and CG ranges laid out in your spreadsheet so all you'd have to do is copy the code below over to a macro sheet in Excel.

Function IterpVal(Xrng As Range, Yrng As Range, targ As Double)
Dim x() As Double, y() As Double, dpts As Long, i As Long
Dim vX As Variant, vY As Variant, tempVal As Double
vX = Xrng 'range(Xrng)
vY = Yrng 'Range(yrng)
dpts = Application.Count(Xrng) 'Range(Xrng))
ReDim x(1 To dpts)
ReDim y(1 To dpts)
For i = 1 To dpts
x(i) = vX(i, 1)
y(i) = vY(i, 1)
Next i

Call locateCHH(x, dpts, targ, i)
Call interpCHH(x, targ, y, tempVal, i)
IterpVal = tempVal

End Function


Sub locateCHH(xx() As Double, n As Long, x As Double, j As Long)
' bisection table lookup from NR in Fortran
' j is the target index such that xx(j)<x<xx(j+1) or xx(j)>x>xx(j+1)
Dim j1 As Long, jm As Long, ju As Integer
j1 = 0
ju = n + 1
While (ju - j1 > 1)
jm = (ju + j1) / 2
If ((xx(n) > xx(1)) = (x > xx(jm))) Then
j1 = jm
Else
ju = jm
End If
Wend
j = j1
End Sub
Sub interpCHH(xx() As Double, targ As Double, yy() As Double, value As Double, j As Long, Optional aMax As Integer, Optional aMin As Integer)
' simple linear interpolation
If aMax + aMin <> 0 Then
If j >= aMax Then
' value = yy(aMax)
value = yy(j) + (targ - xx(j)) * ((yy(j) - yy(j - 1)) / (xx(j) - xx(j - 1)))
Exit Sub
End If
If j < aMin Then
value = yy(aMin)
Exit Sub
End If
End If
value = yy(j) + (targ - xx(j)) * ((yy(j) - yy(j + 1)) / (xx(j) - xx(j + 1)))
End Sub

 

[Graueradler]

If I understand the problem, "Goal Seek" might work. Write an equation for the limit line minus the weight. Set the result to Zero by varying the arm.

 

[Clark1961]

If I understand the problem, "Goal Seek" might work. Write an equation for the limit line minus the weight. Set the result to Zero by varying the arm.

If yer gonna write the equation for the continuous but non-smooth line then ya jus' well write it so the unknown is CG rather than weight...

Hint: why'd I go to all the trouble of writing a linear interpolation routine?

 

[Capt. Geoffrey Thorpe]

why'd I go to all the trouble of writing a linear interpolation routine?

Because you don't have Matlab.

 

[Clark1961]

Because you don't have Matlab.

Now that's a good comeback.



Matlab has it's place...it just isn't in my VBA routines in Excel.

 

[Areeda]

Byron,

Did you get this working?

Joe

 

[Graueradler]

If yer gonna write the equation for the continuous but non-smooth line then ya jus' well write it so the unknown is CG rather than weight...

Hint: why'd I go to all the trouble of writing a linear interpolation routine?

The OP said "at the given weight", not as the weight varies during flight.

I'm not familiar with the subject aircraft but the limit lines for mine are all linear (y=MX+B) "the bee) is turning into a smiley" and the only weight that varies during flight (fuel) also produces a linear result. I think I could grab actual static weights from the W&B input for use in a formula. I haven't tried to develop a solution, just suggesting an approach that I'd try if I did.

 

[Clark1961]

The OP said "at the given weight", not as the weight varies during flight.

I'm not familiar with the subject aircraft but the limit lines for mine are all linear (y=MX+B) "the bee) is turning into a smiley" and the only weight that varies during flight (fuel) also produces a linear result. I think I could grab actual static weights from the W&B input for use in a formula. I haven't tried to develop a solution, just suggesting an approach that I'd try if I did.

If you're going to solve for the equation of a line, do it for the unknown, CG, not the known, weight. If you do that then a goal seek (which you suggested) is not required.

 

[Areeda]

I think we're making this more complicated than necessary. If I understand the problem, it is similar to the automatic "am I in CG limits" problem.

Here's a piece of a W&B spreadsheet for a Seneca II:

If I understand what Byron wants to do, it is similar to the numbers in rows 25 & 26: Given the loading at takeoff what are the Forward and Aft CG limits. I also compute these at the empty fuel weight but weight on landing is another possible.

The problem for me was to use the data that makes up the envelope as graphed on the right and compute the CG. The formula in C25 is =IF($C$19<I20;J20;(IF($C$19>I21;$N$7+C19*$N$6;$O$7+C19*$O$6)))

It uses the IF's to figure out which kink in the FWD CG the loaded weight is then calculates the CG limit for that weight with a simple interpolation.

Comparing that CG with the calculated CG it turns the cell red if outside the limits with a "conditional formatting" option.

 

[nyoung]

Not an answer to your question, but if you are interested, I would be happy to give you the php code for this...
http://needlescentered.com/N3962R_wb.php3

I am curious why the charter flights have the max/min CG position info as a requirement? If the W&B works for the full -> empty fuel profiles, what else matters?

 

[Aztec Driver]

I think we're making this more complicated than necessary. If I understand the problem, it is similar to the automatic "am I in CG limits" problem.

Here's a piece of a W&B spreadsheet for a Seneca II:

If I understand what Byron wants to do, it is similar to the numbers in rows 25 & 26: Given the loading at takeoff what are the Forward and Aft CG limits. I also compute these at the empty fuel weight but weight on landing is another possible.

The problem for me was to use the data that makes up the envelope as graphed on the right and compute the CG. The formula in C25 is =IF($C$19<I20;J20;(IF($C$19>I21;$N$7+C19*$N$6;$O$7+C19*$O$6)))

It uses the IF's to figure out which kink in the FWD CG the loaded weight is then calculates the CG limit for that weight with a simple interpolation.

Comparing that CG with the calculated CG it turns the cell red if outside the limits with a "conditional formatting" option.

That is exactly what I am looking for. I don't suppose I could look at a copy of that to see how it is done completely?

You can pm me for my email, if so.

Thanks everybody, I appreciate the help.

 

[Aztec Driver]

Not an answer to your question, but if you are interested, I would be happy to give you the php code for this...
http://needlescentered.com/N3962R_wb.php3

I am curious why the charter flights have the max/min CG position info as a requirement? If the W&B works for the full -> empty fuel profiles, what else matters?

I don't know why, since we calculate the CG at takeoff weight and landing weight, to make sure it falls within the envelope, but it is on the manifest, so we fill it in.