I thought I’d write a short guide to anyone who is, like I once was, looking to turn a graphed curve into a mathematical function. There are many reasons why one might do this, but they boil down into one of two fundamental motivators:
- Insufficient data on how the curve was produced; or
- Too lazy/not enough time to sit down and figure out the maths behind the curve from first principles or known mathematical models
One could argue that Reason 1 actually a subset of Reason 2, because if you really made the effort, you could figure it out… But being lazy is a perfectly good reason, and exactly why people have made the software used in this guide.
In this example, we’ll look at one of the graphs from AS1403 – Australian Standard for Design of Rotating Steel Shafts. We shan’t look at all of them, because I can’t be bothered explaining the process of interpolating between multiple curves on a single chart. If anyone’s interested in that, leave a comment. Here’s the Size Factor chart:
First, use your favourite screen capture software (or take a screen shot and trim in paint) to save the graph as a .bmp file. I suggest
The next step for turning this chart into a mathematical function is to convert it into a series of datapoints. I use “Engauge”, which is a free software package. Unfortunately there are some bugs with Engauge. In particular, to use this software you have to locate and install “Qt3Support4.dll”. Fortunately the folks at bConverged have done the hard work for us, and compiled a build which takes care of these issues, here: http://www.bconverged.com/download.php
Once you’ve installed the software, click File >> Import File >> then select the graph image that you want to import.
The graph should come in, with a heap of green lines all over it. These green lines are the curve/line sections that Engauge has automatically detected.
On the toolbar, there are a number of tools for selecting points on your curve.
First, click the “Axis Point” tool. This will allow you to define the three points which establish the coordinate system for your chart. In the example below, I’ve set the Origin, and extreme points on the horizontal and vertical axes as my points. Always pick the extreme points on your axes, for greater accuracy. I also recommend zooming in (select zoom percent in the tool bar) when placing the points, to ensure accurate positioning. Remember to always check the actual coordinates of these points. You’ll notice that the vertical axis starts at a value of 1.0; this means that the Origin is a (0,1.0) and the furthest horizontal axis point is as (250,1.0). The red crosses in the photo below are the Axis Points.
Next use the “Select Section” tool to select segments of the curve (as identified by Engauge), for quick filling of points. You can then use the Curve Point tool (button with the little blue crosses) to add extra points, and use the Arrow tool to relocate points (or select them for deletion). The blue crosses are selected data points:
Now at this point, it’s worth going to Settings >> Curves. This will allow you to Rename the curve which contains the datapoints that you’ve selected. It also lets you define new curves. You can later use the drop-down box in the toolbar to select different curve names, so that you can add points to those other curves. Doing so means that when you export your curve, the set of data points will be appropriately labelled, and you can export multiple curves from a single file.
Now click File >> Export File As >> then name your file and save it. I like to save as a .CSV file, for use in excel.
Next create an X-Y scatter of the exported points, and add a trend-line (selecting the curve type appropriate, and adjusting to get a reasonable R² value. Right Click on trendline >> Format Trendline >> select the option to “display equation on chart” and “Display R-Squared value on chart”. Now you may notice that the equation is displayed in scientific notation with very few significant digits. This caught me out the first time, and gave me a final function that was WAY off the chart…
Next, right click on the trendline label text (equation and R²) >> Format Trendline Label. Under the “Number” tab, set the Category to “Number”, and adjust the number to show sufficient significant digits for each coefficient in the equation (for this equation I went 10, because there are some very small coefficients). If you don’t do this, you won’t have enough significant digits for your equation to work, and the results will be off by a significant margin.
Now you’ll have a formula in this general format: “y = 0.0000000000×5 – 0.0000000035×4 + 0.0000012276×3 – 0.0002226645×2 + 0.0219923943x + 0.7781145214”
Copy and paste that into a cell, delete the “y” and replace each “x” with a reference to the cell next to it and raise the exponent. i.e. “= 0*D3^5 – 0.0000000035*D3^4 + 0.0000012276*D3^3 – 0.0002226645*D3^2 + 0.0219923943*D3 + 0.7781145214”
This formula is your digitised graph. Congratulations! Type you should be able to change the value in your referenced cell, and the result will be pretty close to what you’d measure from the chart (even more accurate, if you’ve done it right).
If people don’t trust your digitisation skills, you can set the background of the chart to No Fill, and lay it over the top of the original chart, and even plot on any key points of interest (i.e. a know X value and calculated Y value) to prove the fidelity of your equation.
You can also turn these formulas into User Defined Functions, for easy use within calculation sheets. For info on User Defined Functions in Excel, see my previous post here, or google the topic.