Graph of the Week: The Bagel Graph

There are many traits that are prevalent amongst engineers (there are plenty of jokes about this, normally told with glee by lesser humans), but i think there is possibly only one which appears to be universal:
We love free food.

Most engineers are honest people, who are not open to corruption, but they will do things for food that they would never do for money. As a result, it is common practise for equipment suppliers to come in to our offices to present “lunch and learn” sessions. While these sessions aren’t going to sway us to use a certain product (we are generally, after all, technophiles, and will always go for the technically superior product), the mention of a free lunch does ensure that the vendors get a room full of engineers who are too busy eating to ask annoying questions.

After these lunch and learns, we will discuss the merits of the presentation amongst ourselves: starting with the quality of the food, then on to the quality of the equipment being demonstrated.

But when you get engineers doing technical evaluations on food… weird things happen…

I was telling my wife about one such Lunch-and-learn, and the duck & coleslaw bagel that I ate:
“You know what the best thing about a good bagel is?” I asked her, as we ate dinner.
“What?” She asked, and really should have known better.
“The bit in the middle where there is a hole in the bagel, but the filling continues… actually, there’s probably a graph for that…” I mused.
“You don’t need to graph it, baby” she said, wishing she wasn’t just dead wrong.
“Oh yes, my little turnip. Yes I do…” I turned to the whiteboard on our kitchen wall…

I present to you… The Bagel Graph


(Filling as a ratio to bagel bread. Apologies for the quality of the graph, I tried to sketch it as neatly as I could on my smartphone).

Digitising Graphs – Turning Paper Graphs into Functions

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:

  1. Insufficient data on how the curve was produced; or
  2. 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:

AS1403 - Ks Size Factor

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:

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.

step 1 - import

On the toolbar, there are a number of tools for selecting points on your curve.

Engauge buttons

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.

step 2 - 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:

step 3 - select segments and adjust 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.

step 5 - CSV in excelNext 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…

Low Resolution Equation

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).

Step 6 - forula in action

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.