Advertisement

Blog

Excel Curve Fitting & Linearization, Part 2

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

Figure 1

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!

Figure 2

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.

Related posts:

8 comments on “Excel Curve Fitting & Linearization, Part 2

  1. RedDerek
    January 11, 2014

    I remember doing a lot of programming in my college days in the Numerical Analysis class. Not sure if they still have this class today. In that class we did the software programming (FORTRAN if I remember) that did linear regression and moved on to polynomial regression. All to get that magic formula. It has been a few years since I have used that type of information.

  2. eafpres
    January 12, 2014

    Hi Aubrey–something I have used a lot is a method to avoid having to implemnet LINEST as an array function, which sometimes I don't like becuase you have to devote a block of cells for the array output, and of course there is that funky 3-key entry method (talk about non-obvious!).  The method I use a lot is:

    =INDEX(LINEST(yrange,xrange,FALSE,FALSE),coef#)

    where yrange is replaced with an actual referenc to cells containing the y data for fitting, xrange likewise, and coef# is an integer, such as 1, 3, etc.

    as a speciifc example:

    =INDEX(LINEST(BA$3:BA$19,AW$3:AY$19,FALSE,FALSE),1)

    Here BA3:BA19 contains a single series of ouput (or y values), and I'm fitting to 3 variables, values for which are in the columns AW, AX, and AY, and this function returns the coefficient for the 3rd variable (notice that the array generated by LINEST has a very specific definition of where in the array various coefficients are stored; you will want to read about LINEST to get this right).  In this case I'm also setting the intercept to 0 (the first “FALSE”) and telling LINEST I don't want all the regression statistics (the second “FALSE”).

  3. antedeluvian
    January 12, 2014

    Hi Blaine

    The method I use a lot is:

    Thanks for the approach. I like it a lot and I intend to use it at the first opportunity I get. Let me say that I have used the array formulas in several blogs on Excel still to come. I am not ignoring you- I just don't want to rewrite and re-picture what I have already done!

    Back to the Array Formulas- I just got a book called appropritely Ctrl+Shift+Enter by Mike Girvin. I started on it, but got sidetracked and I need to get back to see if there is something more there.

  4. samicksha
    January 13, 2014

    The coefficient of determination, estimated vs actual y-values, which further ranges in value from 0 to 1. If it is 1, i guess it's a perfect correlation, and in case if the coefficient of determination is 0, the regression equation is not helpful in predicting a y-value.

  5. yalanand
    January 31, 2014

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

    @Aubrey, is this ToolPak freely available ? Or do we need to buy this ToolPak ? 

     

  6. yalanand
    January 31, 2014

    In that class we did the software programming (FORTRAN if I remember) that did linear regression and moved on to polynomial regression.

    @RedDerek, we can use many different languages to solve them but I think excel is the most easiest way to solve them because it provides very easy interface and lot of inbuilt features.

  7. antedeluvian
    January 31, 2014

    yalaland

    is this ToolPak freely available ?

    It is part of Excel. I don't know why it doesn't install automatically. I actuallyd escribe how to do it in the 2nd paragraph of my blog on the Solver function

     

  8. yalanand
    January 31, 2014

    I actuallyd escribe how to do it in the 2nd paragraph of my blog on theSolver function

    @antedeluvian, thanks for sharing this link. I will refer to this blog to understand more about installing add-in in excel.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.