excel 2003 - revising a formula to make it more accurate

[Doug]

Hi everyone,

I have two lists of data - one showing fuel weights at 67 lb increments from
0 up to 3645 lbs (sometimes at different increments for special weights) and
the other showing a corresponding moment (weight times the distance of that
weight from the center of gravity). These come from an aircraft weight and
balance manual. As you can tell, for a specific fuel weight not listed in
this manual, one has to interpolate between two listed values in order to
estimate a moment value for that unlisted weight.

I'm trying to come up with a very precise formula using excel that will
accurately estimate the moment for a specific fuel weight, but the arm
(distance from the cg) varies for each fuel weight in a non-linear manner.
Using a trendline from a chart, the closest R^2 value I can come up with is
0.999811266 (which still yields up to a 5% error).

Is there any excel tool or trick that can help revise this formula and make
it more precise? Thanks for any help.

 

[JLatham]

Seems a simple enough formula, what you need is the "arm" (distance from
center of gravity). I presume that information is unavailable in your
tables? It may take some legwork to build your own calculator, but the
starting point could be here:
http://www.airweb.faa.gov/Regulatory_and_Guidance_Library/rgMakeModel.nsf/MainFrame?OpenFrameSet
Which is a page from which you can locate the Type Certificate Data Sheets
(TCDS) for virtually every aircraft made. The links on that page take you to
alphebetized lists for manufacturers which in turn allow you to choose
aircraft made by them. Other options for 'searching' are provided also.

You then could make a list of arm lengths for various points in the aircraft
and simply fill in the weight for a point to obtain its moment.

Hope this helps some.

 

[JLatham]

Upon further thought - how about this (and it probably has problems in light
of your statement that the arm varies for each fuel weight in a non-linear
manner) -
But for any given weight/moment combination in the table, the Arm can be
calculated exactly by the formula:
Arm = moment/weight

What I'm thinking is that you could have a table in an Excel sheet like
this, where the first 2 columns (A & B) consist of values from your existing
tables, then column C computes the arm for the pair on a row, then you put
in a new weight in D and calculate new moment in E. Would look something
like this (using inches for arm length, pounds for weight, so moment is in
in-lb)

A B C D
E
1 Wt(Lb) Moment (In-Lb) Arm (In.) New Wt Calc.Moment
2 67 10589 =B2/A2
=C2*D2

I left D2 empty since that's where you'd type a new weight for that point.
Actually, the unit of measure for Moment and Arm length are kind of
irrelevant. Only the unit of measure for the weight has to be the same
between entries in columns A and D. The resulting calculated moment in E
will be in the same terms/units as those reported in column B.

 

[MartinW]

Hi Doug,

Here is a spreadsheet that I used to demonstrate to a colleague
how to calculate to various lines on a chart. The chart is a mess
as it was just developed on the run to suit my purposes at the time.
I did mean to tidy it up and make it more useful one day, but
that day hasn't come yet. <g>

If you click on either of the spinners you will see how each series
is calculating to the various lines drawn through the same data.

I think that the piecewise trend, shown as the orange dot tracing
the blue line, may be what you are trying to achieve.

Is that correct?

Here is the file in Excel 2000 format.
http://www.savefile.com/files/1637760

HTH
Martin