Well, it seems an age since I last blogged – somehow work gets in the way. I’ll have to complain to the boss about that one day.
Anyway, we recently had a case where we needed to convert a chart into an algorithm, and I thought I’d give you the recipe I have used for many years. This goes back to computing functions for nonlinear control characteristics in my control system days, working out surface functions from aircraft performance plots and most recently working out the function for engine derate charts. In all cases the technique is similar and can be done with basic Excel functions. OK, I am sure the purist mathematicians will groan, but this works and gives answers you can understand.
In the best cake recipe style, let’s start with the basic ingredients.
You need the best copy of the chart you can find. Here is the one we were working with:
The y-axis is engine thrust, the x-axis is engine speed (N1) and each coloured line is for a different Mach number. We want a function of the form:
Thrust = f(N1, Mach)
Preparing the Ingredients
The first thing we need to do is to read a good number of data points from the graph. This is helped by overlaying a grid for which I plot a chart in Excel with any old data points, force the scales to be the same as the chart and include lots of grid lines. Finally set the plot area to None (“Clear plot area fill”) and stretch the grid to match the chart. It will look like this:
Make it big on your screen and start reading points off.
List them all in Excel, one row at a time. We were only interested in a limited range of N1 and Mach values so these are the values read off. OK, I found it easier to miss off a couple of zeros but you can see the work involved. Not too bad, if a little laborious.
Finally rearrange so that each data point is on a line with the thrust for each N1, Mach combination.
It is a good idea to have a check for errors here. A quick way is to compute differences from one row to the next and see if any are abnormal. The odd slip-up will quickly become obvious.
Mixing the Cake
Add columns for each term in the equation you expect to use. I always start with the simplest form of
Thrust = a + b x N1 + c x Mach
Put a, b & c in three cells of the spreadsheet and work out the calculated thrust in new cells =$B$1+$B$2*A6+$B$3*B6. Then compute the square error and add the column to give a total error. It will look like this:
OK – this example has a main table 64 rows long so imagine 59 more rows beneath this bit. The Const, Mach and N1 values I have given initial values of 1 for luck. I use one because often zero won’t work, but you can just insert your favourite number if you like.
Cooking the Cake
You will need to use the Excel Solver function. If you don’t have this, follow these instructions:
In Excel 2010 and later go to File > Options. …
Click Add-Ins, and then in the Manage box, select Excel Add-ins.
In the Add-Ins available box, select the Solver Add-in check box, and then click OK.
In solver, select the cell with the total error and ask to minimize this by changing the three cells holding a, b & c. In my case the result was this:
The error is 100 times smaller and a solution has been found which minimizes the error for this function.
Here is what the result looks like, with the blue points taken from the graph and the red lines are the fitted curves.
The eagle-eyed will have spotted that using a very simple formula the four lines are barely spaced. In fact, there really are four red lines, just that their separation is minute.
Now, to change the form of the equation computing the estimate, you will need to change the equation for the estimate and repeat the solver process. The coefficients calculated will be those which give the best fit for the formula you have selected. The problem you need to address is which formula gives the best answer? One option is to add more terms in a polynomial (x2, x3, x4 etc), and if the data is very curved this is suitable, or you can add products (x.y). The best way to assess this is to plot the graph and estimate values and look where the differences lie.
Because the change in thrust varies with both thrust and Mach, I added a product term and obtained this plot:
This gives the separation we are looking for, but the lines are still straight. To add some curve to the lines I added a second order polynomial term in N1 thus:
The total error has reduced from 274,261,863 to 2,169,560 and then 674,900 in these three charts.
By this time the errors are mostly below 200, and the worst case was a point on the yellow curve at 95%. I tried adding further terms but no simple extensions improved the overall accuracy, so this solution was selected. How bad is it?, well here is a review of the original chart with the least accurate point shown. That is, the yellow dot should be on the yellow line and the error is that it is below the line at this point. For the rest of the chart, our equation is more accurate and normally within the width of the line on the chart.
Eating the Cake
So all we have to do is use the finished equation and we get an answer for any values over the range of input values used, and of course the answers are interpolated across all intermediate values.
Like all techniques, this one has its limitations. Simple equations do not replicate step changes or strange nonlinearities, and these charts are best automated using look-up tables. That is usually clear either from the shape of the chart or when Solver declares that it cannot find a solution.
On the other hand, when it does work, there is a happy, geeky, satisfaction to getting the red line to match the blue dots.