In Part 1 of this blog, we looked at the way that Excel can help generate an equation that describes a curve based on collected data. Then, that curve can be linearized. We left off noting that the method worked pretty well but would benefit from slight refinement.
You may think that we are done, but can you spot the flaw in this approach? It is a good first step and if you are going to dash off and implement this in firmware, well don't let me stop you. But if you are going to try and do this with hardware and you want resistor values to fall out of your calculations you can't access the coefficients of the polynomial for use in formulas. You would have to re-enter them manually and if we are trying to build a reusable model this approach would be inelegant.
In order to generate the coefficients in a cell we need to use the LINEST function in Excel. LINEST returns a number of regression-derived values and is of a special function genre called “array functions,” which allows a function to return a series of values (as an array obviously).
The first step is to prepare the function for a polynomial of order 2, by creating the column for VR1002 in column E. It is the associated entry in column D, squared. (If we had used a single block to create the chart, the data in this column would appear on the chart as well.)
We are only interested in the first three returned values of the LINEST function, those being the polynomial coefficients. Highlight any three contiguous cells. In this example I used C24:E24. Make sure you read to the end of this paragraph before you try this at home! While those cells are selected enter the formula as you see it in Figure 1. Unlike the chart function, do not include the headers in the cell selection. Don't press [Enter] when you have completed it! Simultaneously press [Ctrl]+[Shift]+[Enter], which informs Excel that you are entering an array formula. There — now you can try it!
You will see the result in Figure 2. Note the coefficients in C24 to E24 agree with the coefficients in the chart, but because they are values in a cell they can be accessed by any formula. If you look at the formula of the cell content you will see the LINEST formula you entered surrounded by curly braces. This was added automatically by Excel (you can't add them yourself) to indicate the array nature of the function.
I'm just a simple digital engineer bumbling my way through analog design and I would hesitate to suggest an original approach to realize this function in hardware. However, this blog is based on a sequence of design ideas: IC Generates Second-Order Polynomial in Electronic Design; an EDN Design Idea Design an RTD interface with a spreadsheet by Robert S. Villanucci; and finally, my response A better approach to designing an RTD interface with a spreadsheet. The original provides a generic method to generate a polynomial. Mr Villanucci's idea builds on this and applies it to an RTD providing some amplification and level shifting before he applies the linearization. Both use a relatively expensive IC multiplier and there are probably other ways to do this. Another design idea proposes an alternative Platinum-RTD-based circuit provides high performance with few components — although I haven't attempted to recreate the Excel workings.
If you feel like going further into Excel's capabilities, you can look into the features in Excel's regression function. To do that you will have to install the Analysis ToolPak (look for the step-by-step description in the Help). Upon completion you will find the Data Analysis icon on the Data tab of the ribbon. Click on it and select the Regression and you're on your way to determine goodness of fit and other aspects of curve fitting that I have long forgotten. There may be another blog in that, but we'll wait to see if anyone is still awake enough to express any interest.