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.