Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 - revising a formula to make it more accurate
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 - revising a formula to make it more accurate
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 he http://www.airweb.faa.gov/Regulatory...e?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. "Doug" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 - revising a formula to make it more accurate
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. "Doug" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 - revising a formula to make it more accurate
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 "Doug" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Validation List Wider - Excel 2003 | Excel Discussion (Misc queries) | |||
Make Excel 2007 work like 2003 | New Users to Excel | |||
How can I make graphics with 5 inputs in Excel 2003? | Charts and Charting in Excel | |||
Can't make changes in Tools/Options in Excel 2003(2) | Setting up and Configuration of Excel | |||
how to make a title always on the page on excel 2003 | Excel Worksheet Functions |