When I was in high school, we used to plot points on graph paper and then try to fit a curve with some plastic templates named in undoubtedly sexist terms as French Curves. There was also a flexible rod that could be manipulated in two dimensions to match the perceived curve presented by the plotted points. In university we learned mathematical techniques that you would apply to try and fit a curve to a particular set of data. Much of what I learnt has been forgotten, but fortunately, when I do need to work with anything other than a straight line, there are tools to help. Excel is one such tool.
There are many instances in an engineer's life where he/she measures an output in response to a particular stimulus. I could simply present some data and show how to derive the coefficients of the curve. The technique that I use can be used on any set of data, but I would like to immediately extend it since Excel can be used to great effect to derive the function to linearize a particular curve. You could use it translate to almost any desired output, but linear is the most likely. Let's consider an RTD sensor and using its published resistance tables, enter the resistance at steps of 25°C from 0°C as in Figure 1. We will presume a constant current through the RTD of 0.4mA as entered in cell D1 and the calculated voltage for this current is shown in column D. A typical calculation for the developed voltage is shown in the formula bar at the top.
Now let us assume that we want to design some method to linearize (and by that I mean make into a straight line!) the voltage developed across the resistor (VR100). An example might be to make a module that converts the RTD output to a linear 0-4V output that might be used to drive 20mA current loop. The values for Vout in column G are the value we desire at the output for each particular sensed temperature. Just to be plain, the numbers entered in column G are what we want as an output. They are not derived from anywhere on the worksheet (although of course they could be in a more complex application).
We can graphically see the relationship by creating a chart of Vout against VR100. To do this, highlight columns D and G separately. It is possible to do it as a single block, but if we do that it will interfere with the graph that you will see a little while. As two separate entries first select block D4 to D21 and then holding down the [Ctrl] button select the block G4 to G21. Then select the Insert tab of the ribbon and select the Other Charts option and then All Chart Types follow by X Y (Scatter). Finally select one of the X Y types as you see in Figure 2. (As an engineer it seems to me that the most frequent chart type you will use is the X Y type.) To avoid the number of steps to access it you may want to experiment with the Set as Default Chart button you see at the bottom.
Clicking on the [OK] button will pop up the dialog box shown in Figure 3.
You can select the trend-regression type and even the order of the polynomial. Make sure the Display Equation on chart option is checked. Presto! The coefficients of the polynomial are calculated and the whole chart is displayed in Figure 4. There is much that can be done to cosmetically improve the chart, but let's leave that for now.
There is a small flaw in this approach, though not too serious. In part two, we'll address that and push in a bit deeper to produce more elegant results.