Excel Formula
Thread Starter
Join Date: Feb 2000
Location: Pacific
Posts: 731
Likes: 0
Received 0 Likes
on
0 Posts
Excel Formula
I am trying to build a weight and balance program in Excel but it needs to offer an Index value as well as the normal Arm. the Index is a quirky one and is based on a pre-existing balance wheel. I have determined that the index, as used, has errors and have calculated a correction value which unfortunately varies with weight and balance. For example the correction is to divide the normal result by 4500 at the higher weights in the range at the forward limit, but only 3300 at the rearward limit and the same weight. At lower weights the corrections are 3300 and 3000 forward to rearward.
I tried getting an average, but that makes it accurate only in the middle of the balance range, and in error at the critical points of the envelope. Ditto for weights at the upper and lower limits.
If I could figure out a way to calculate the correction factor according to the point on the graph I could add that to a formula.
An example of the formula is Index = ((Moment Arm - 178.35) X Weight) /3000. Because of a basic error, the value of the divider (3000) needs to be changed as the weight and the balance changes. If I use the value of 3000 it is accurate only at the lowest weight and most rearward cg and is widely out when the airplane is at max TOW and forward cg.
I am looking for a way to pick a point on the balance graph and compensate for both weight and cg (Arm) so that the correct factor will show.
The result at present draws a graph and the calculated value for the various points are shown as a point on the graph, but the value of the index is not a simple change.
I can then write a formula using the correct value in order to calculate the index so that it is the same as on the balance wheel.
I tried getting an average, but that makes it accurate only in the middle of the balance range, and in error at the critical points of the envelope. Ditto for weights at the upper and lower limits.
If I could figure out a way to calculate the correction factor according to the point on the graph I could add that to a formula.
An example of the formula is Index = ((Moment Arm - 178.35) X Weight) /3000. Because of a basic error, the value of the divider (3000) needs to be changed as the weight and the balance changes. If I use the value of 3000 it is accurate only at the lowest weight and most rearward cg and is widely out when the airplane is at max TOW and forward cg.
I am looking for a way to pick a point on the balance graph and compensate for both weight and cg (Arm) so that the correct factor will show.
The result at present draws a graph and the calculated value for the various points are shown as a point on the graph, but the value of the index is not a simple change.
I can then write a formula using the correct value in order to calculate the index so that it is the same as on the balance wheel.
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
When I wrote my load and balance Excel sheet for the 737 I had exactly that problem with the trim setting. All the other parameters were either linear or could be adequately interpolated from a look-up table that was not too complicated. I knew there are programmes that will calculate the formula required for the complex curve you face, but I could not find one. I got very close (acceptably close) by a process of 'ball parking' a complex equation by doing what you have done - plotting the points on a graph as AAG suggests but then using one of the graph drawing progs to try and approximate to the function required. Once I got close to the curve, a bit of tweaking using a few sample points did the job. Good luck.
There must be a clever way!
There must be a clever way!
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
Maybe this will give some ideas on how it might be done:
Cells C2-4, G2-4, K2, K4, M2, M4, B7 and B8 are referenced using "Defined Names". The names they have been given are in the cells immediately to the left of them. I.e. C2 is named P. You name a cell in Excel 2010 by selecting the cell and typing the require name in the name box, which is immediately tot he left of the cell formula editing box, in my example it currently says "F11". See here for more help : Define and use names in formulas - Excel - Office.com
B7, B8 and B10 contain formulas, which are copied as text into the adjacent cells in column D.
The diagram tries to illustrate how the Defined Names relate to the problem.
What I have done is assume that at a given weight (W), the divider value is a direct linear relationship with the position value (P) as it changes from its lowest value (Pr) to its highest (Pf). So if W is the lowest possible weight (Wl), as P changes from Pr to Pf, the value of divider will linearly progress from Bl value to Br. As W is increased the range is altered linearly. These are the Sl and Sh values: where Sl linearly changes from Bl to Tl as W increases from Wl to Wh, and similarly for Sh.
Divider is then calculate as a linear proportion of the range Sl to Sh as P increase.
The Orange cells need to be populated with the divider values at the extremes of your weights and positions.
The green cells need to be populated with the extremes of weights and position.
The blue cells then become your inputs for actual weight and position.
Samples:
P = -2 i.e. Pr fully rearward.
W = 250 i.e. Wl the lowest weight.
Divider is given as : 3000
P = 2 i.e. Pr fully forward.
W = 250 i.e. Wl the lowest weight.
Divider is given as : 3300
P = -2 i.e. Pr fully rearward.
W = 450 i.e. Wl the highest weight.
Divider is given as : 3300
P = 2 i.e. Pr fully rearward.
W = 450 i.e. Wl the lowest weight.
Divider is given as : 4500
There is an intermediate sample in the capture shown above.
Cells C2-4, G2-4, K2, K4, M2, M4, B7 and B8 are referenced using "Defined Names". The names they have been given are in the cells immediately to the left of them. I.e. C2 is named P. You name a cell in Excel 2010 by selecting the cell and typing the require name in the name box, which is immediately tot he left of the cell formula editing box, in my example it currently says "F11". See here for more help : Define and use names in formulas - Excel - Office.com
B7, B8 and B10 contain formulas, which are copied as text into the adjacent cells in column D.
The diagram tries to illustrate how the Defined Names relate to the problem.
What I have done is assume that at a given weight (W), the divider value is a direct linear relationship with the position value (P) as it changes from its lowest value (Pr) to its highest (Pf). So if W is the lowest possible weight (Wl), as P changes from Pr to Pf, the value of divider will linearly progress from Bl value to Br. As W is increased the range is altered linearly. These are the Sl and Sh values: where Sl linearly changes from Bl to Tl as W increases from Wl to Wh, and similarly for Sh.
Divider is then calculate as a linear proportion of the range Sl to Sh as P increase.
The Orange cells need to be populated with the divider values at the extremes of your weights and positions.
The green cells need to be populated with the extremes of weights and position.
The blue cells then become your inputs for actual weight and position.
Samples:
P = -2 i.e. Pr fully rearward.
W = 250 i.e. Wl the lowest weight.
Divider is given as : 3000
P = 2 i.e. Pr fully forward.
W = 250 i.e. Wl the lowest weight.
Divider is given as : 3300
P = -2 i.e. Pr fully rearward.
W = 450 i.e. Wl the highest weight.
Divider is given as : 3300
P = 2 i.e. Pr fully rearward.
W = 450 i.e. Wl the lowest weight.
Divider is given as : 4500
There is an intermediate sample in the capture shown above.
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
Not sure for what boof is striving, but, trouble is, MacB, if it is for aviation trim terms, the variables are not linear, hence the need for complex equations for the 'curves'.
For aircraft (737-400) trim, the datum C of G is determined from the value of the fan of lines in the graph bottom right on
http://ahmthy.com/AHM560/B737-400/TH...DTRIMSHEET.pdf and from this value the trim setting is determined for a specific flap setting.
It was while putting that fan graph into Excel where my brain hurt.
For aircraft (737-400) trim, the datum C of G is determined from the value of the fan of lines in the graph bottom right on
http://ahmthy.com/AHM560/B737-400/TH...DTRIMSHEET.pdf and from this value the trim setting is determined for a specific flap setting.
It was while putting that fan graph into Excel where my brain hurt.
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
No, that is a separate task
. The problem I had was in finding the right equations for the fan lines - while 'straight lines' on the chart they were not a 'linear' function wrt weight/index on my chart, but, as I say, I found a reasonable fit having located the origin and various slopes - certainly good enough for the accuracy of the trim wheels!
I am embarrassed/proud (delete as applicable) to say that my aero-eng degree maths module was coming to a close 50 years ago, and despite many wet and windy days at KEF beavering away at the problem in later days I never quite got the hang of Trim=ƒ(Wt,Index) - if indeed I ever had 'the hang of it'.................
If you can guide me through the process of establishing the formulae I would be interested - PM probably best so as not to bore all here. I have no doubt it will be a 'doh!' moment - as much of the 50 year old times were
. The problem I had was in finding the right equations for the fan lines - while 'straight lines' on the chart they were not a 'linear' function wrt weight/index on my chart, but, as I say, I found a reasonable fit having located the origin and various slopes - certainly good enough for the accuracy of the trim wheels!I am embarrassed/proud (delete as applicable) to say that my aero-eng degree maths module was coming to a close 50 years ago, and despite many wet and windy days at KEF beavering away at the problem in later days I never quite got the hang of Trim=ƒ(Wt,Index) - if indeed I ever had 'the hang of it'.................
If you can guide me through the process of establishing the formulae I would be interested - PM probably best so as not to bore all here. I have no doubt it will be a 'doh!' moment - as much of the 50 year old times were
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
I can replicate the grey graph in the bottom right hand corner. Using the weight and the flap angle as inputs, and getting the index as a returned value.
Would that be of interest?
Would that be of interest?
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
The required extract is the take-off CoG( given by the 'fan' lines) based on weight and index (vertical and horizontal scales) but yes, that would be of interest too for the 'mechanics' of it.
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
Yeah, boof! We are off and running.
Mac - 'Index' is tabulated for unit loads at various positions and based on an arbitrary datum such as the nose or a particular other fuselage position. The 'fanned lines' indicate the % position of the C of G (normally based on the wing leading edge and bizarrely known as 'MAC') from which a tailplane trim for take-off is derived (from yet another look-up table - which again varies with weight!!??). Let's call it 'Trim'. (I've often pondered why aviation does it this complicated way!)
With the advent of 'computers' both in load offices and the cockpit these 'old fashioned' sheets are becoming mostly extinct, but believe it or not they were a very quick ('back of a fag packet') and accurate way to establish a/c load and balance - once someone else has done all the line drawing/equation crunching for the basic chart of course.
Mac - 'Index' is tabulated for unit loads at various positions and based on an arbitrary datum such as the nose or a particular other fuselage position. The 'fanned lines' indicate the % position of the C of G (normally based on the wing leading edge and bizarrely known as 'MAC'
) from which a tailplane trim for take-off is derived (from yet another look-up table - which again varies with weight!!??). Let's call it 'Trim'. (I've often pondered why aviation does it this complicated way!)With the advent of 'computers' both in load offices and the cockpit these 'old fashioned' sheets are becoming mostly extinct, but believe it or not they were a very quick ('back of a fag packet') and accurate way to establish a/c load and balance - once someone else has done all the line drawing/equation crunching for the basic chart of course.
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
I have modelled this graph in Excel for the weight and fan lines as inputs and INDEX as the output:
It is solved in a very similar way to the original problem set by boofhead.
The trick is to realise that the intersection points between the fan lines and the horizontal weight lines are spaced evenly, i.e. the problem is actually linear.
It is solved in a very similar way to the original problem set by boofhead.
The trick is to realise that the intersection points between the fan lines and the horizontal weight lines are spaced evenly, i.e. the problem is actually linear.
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
Yes, I realised that a few years back, as is the TRIM change with weight zero at '17' but have been unable to make the quantum leap to writing a formula to extract the 'TRIM' value for Wt/Index coordinates. Have you got one?
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
I think the original problem I had was that the reproduction on the photocopied load sheet that I was using (not the one linked) was so small and fuzzy that it was difficult to establish the horizontal spacing of the 'TRIM' units with sufficient accuracy to enable reliable results, giving me somewhat inaccurate results. I guess I should have scanned it and enlarged it. As you correctly point out the spacing is in fact as near linear as you can tell so an equation should have worked, given the correct figures. By accepting that the result from the original paper sheet I used to use was probably in itself a little 'fuzzy' I am no longer striving for the decimal point accuracy and - job done. Thanks for the nudge.
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
Glad it worked out.
I used MS Visio to sketch lines over the top of a grab from the PDF you linked to.
I could then use the Visio dimension measurement tools for me to read work out scale values. The drawing tools in PowerPoint would help a little, but I don't think they are as comprehensive as those in Visio. Once I had created my Tl, Tr, Bl, Br values for the largest rhomboid shape in the graph, the spreadsheet could extrapolate for any input values. I real challenge would be to bounds check the values against to flag up when the solution point is outside the white area.
I used MS Visio to sketch lines over the top of a grab from the PDF you linked to.
I could then use the Visio dimension measurement tools for me to read work out scale values. The drawing tools in PowerPoint would help a little, but I don't think they are as comprehensive as those in Visio. Once I had created my Tl, Tr, Bl, Br values for the largest rhomboid shape in the graph, the spreadsheet could extrapolate for any input values. I real challenge would be to bounds check the values against to flag up when the solution point is outside the white area.
I hadn't seen the earlier posts in this thread until today.
I use complex Excel spreadsheets on a daily basis and I would like to make two observations:
1. In my experience, using Defined Names and/or Defined Ranges is a recipe for disaster when it comes to trouble shooting.
2. If you are creating a chart that duplicates a printed one, for QC purposes, you can clip an image of the printed chart along the X and Y axes and display the clipped image as the background of your Excel XY chart.
I use complex Excel spreadsheets on a daily basis and I would like to make two observations:
Cells C2-4, G2-4, K2, K4, M2, M4, B7 and B8 are referenced using "Defined Names"
2. If you are creating a chart that duplicates a printed one, for QC purposes, you can clip an image of the printed chart along the X and Y axes and display the clipped image as the background of your Excel XY chart.
