Advertisement

Blog

Excel Curve Fitting & Linearization, Part 1

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.

Figure 1

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.

Figure 2

Clicking on the [OK] button will pop up the dialog box shown in Figure 3.

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.

Figure 4

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.

Related posts:

26 comments on “Excel Curve Fitting & Linearization, Part 1

  1. Victor Lorenzo
    January 9, 2014

    Nice post. I'll look for some spare time to try it. For messuring the content temperature in a portable container device based on one ARM7TDMI from ST (STR710) I used one thermistor and embedded the NTC's table in flash. The SoC did not have floating point unit so I worked in that case with fixed point and interpolation for values not included in the lookup table.

    The results were very precise. Major problem I found was self-heating, just as expected.

  2. etnapowers
    January 9, 2014

    @Victor: could you describe the main functionalities  portable container device based on one ARM7TDMI from ST (STR710)?

     

    Here a description of the ST microcontroller:

    ARM® core with embedded Flash and RAM

    The STR71x series is a family of ARM-powered 32-bit microcontrollers with embedded

    Flash and RAM. It combines the high performance ARM7TDMI CPU with an extensive

    range of peripheral functions and enhanced I/O capabilities. STR71xF devices have on-chip

    high-speed single voltage FLASH memory and high-speed RAM. STR710R devices have

    high-speed RAM but no internal Flash. The STR71x family has an embedded ARM core and

    is therefore compatible with all ARM tools and software.

    Extensive tools support

    STMicroelectronics' 32-bit, ARM core-based microcontrollers are supported by a complete

    range of high-end and low-cost development tools to meet the needs of application

    developers. This extensive line of hardware/software tools includes starter kits and complete

    development packages all tailored for ST's ARM core-based MCUs. The range of

    development packages includes third-party solutions that come complete with a graphical

    development environment and an in-circuit emulator/programmer featuring a JTAG

    application interface. These support a range of embedded operating systems (OS), while

    several royalty-free OSs are also available.

  3. Victor Lorenzo
    January 9, 2014

    Hi @etnapowers, the smart container I mentioned is a product we developed a few years ago for a customer when we were working in our previous occupation. We had another team member who was in charge of putting it alltogether for production and also helped in prototyping. I still maintain the software, host side and firmware), for the customer.

    The device included Li-Po battery and charger, OLED display, speaker for audible signals, indicator LEDs, switches (key-pad), USB device connection, expansion connector for Bluetooth/WiFi/Zigbee module, RFID reader for ISO14443 and ISO15693, high voltage generator for piezoelectric lock, temperature measurement, extra memory for datalogging and a few more (perhaps less relevant) features. The container enclossure was designed so it can be placed inside the standard capsules used by hospitals for sending things using compressed air pipes (tubes)….. and of course, survive 😉 to that.

    I used IAR for developing the firmware and Visual Studio (2008/2010) for the desktop and server applications. For the device's GUI, as it was extremely simple, I used a homebrew visual interface I had created before for one OLED display.

    Regarding the STR710, it was relatively easy to master and work with, but in case I would have to design a new version for this product I would relay on a newer SoC, most surely Cortex M4 based (STM32 from ST or Kinetis K20 from Freescale).

     

  4. Vishal Prajapati
    January 10, 2014

    I have heard the technique for implementing the near linear curve by Look up table. But can we really put the 2nd order equation in to the Microcontroller? Is there any way to implement it in the firmware? If yes how?

  5. Davidled
    January 10, 2014

    -> But can we really put the 2nd order equation in to the Microcontroller?

    I think that the second order equation should be digitized using the simplified formulation to let it be coding.

  6. Victor Lorenzo
    January 10, 2014

    @Vishal, with respect to “implementing the near linear curve by Look up table “. Lookup tables are very handy for speeding up code execution but that comes at the cost of flash/eeprom memory. You use the ADC output value (decimal) as the address into the look up table for calculating the sensor output value.

    But can we really put the 2nd order equation in to the Microcontroller? ” Yes, it is possible and in the case of this formula it's also easy to implement in C : y = 312.24x2 + 114.28x + 2.4113 .

    #define COEF2   312.24
    #define COEF1   114.28
    #define COEF0   2.4113
    #define sqr( v )  ((v)*(v))

    float poly_y( float x ) {
        return COEF2 * sqr( x ) + COEF1 * x + COEF0;
    }

    Depending on target processor and compiler toolchain you'll be able to make a lot of optimizations. The machine code will be faster o slower depending on target processor architecture and availability of DSP and/or floating point unit. There can be a lot of discussion about several more ways to implement the formula.

  7. Vishal Prajapati
    January 10, 2014

    Oh thanks Victor.

     

    Actually I knew the code that can be written in C for the euation. But I was afraid of the code generated by it. I have worked mostly on the 8 bit and 16 bit processors. So, for them to execute this would take like hundreds of kilos of machine cycles. I haven't worked on floating point microcontrollers. That is why probably I don't understand the power of that machines. I hope I would get a chance to work on that soon. But thanks for explaining it.

  8. Victor Lorenzo
    January 11, 2014

    @Vishal,

    DSP algorithms can be implemented in floating point or fixed point. Often 8 bit MCUs have limitations that make us fly-back to that age when programming was more an art than engineering. A floating point unit (FPU) requires a lot of silicon compared to other SoC modules/parts and it adds cost.

    Due to application requirements, some CPUs I've worked with don't have FPU, but most of them do have code/data caches and 32bit RISC like and many single-cycle execution instructions which greatly improve performance. Of course, when designing the application and analysing the timing requirements, one must decide amongst other things if floating point or fixed point arithmetic should be used (or is required).

    32 bit ARM microcontrollers are very interesting as a starting point into the 32bit world, some are indeed very powerfull. You'll find low cost (under $20) Cortex powered LPCExpresso boards from NXP and the Freedom boards from Freescale, both with very active support communities and lots of ready to use sample code. ST Microelectronics has the STM32 series which is also interesting.

    I would recommend starting with one LPCExpresso or Freedom board as they have everything you need, JTAG hardware debugger included.

  9. Davidled
    January 11, 2014

    If you do not want to use high-level language, as one of classic method, floating point calculation could be executed using adding/subtraction/multiplication in either 8 bit or 16 bit Microcontroller with assembly code. I guess that that save a huge program memory size.

  10. antedeluvian
    January 11, 2014

    DaeJ, Victor, & Vishal

    If you do not want to use high-level language, as one of classic method, floating point calculation could be executed using adding/subtraction/multiplication in either 8 bit or 16 bit Microcontroller with assembly code. I guess that that save a huge program memory size.

    As you have all discussed, you can obviously implement any 2nd order expression in software using floating point or even integer arithmentic. All that changes is the precision, code space, exceution speed and of course ease of implementation.

    I have been planning to write a blog on integer arithmetic and this has spurred me on to complete it. My only issue is whether it belongs here on Planet Analog or on the other host site for my blogs: MCU Designlines.

    My conundrum is which is the more suitable forum…

    But I will place a notice here when it is published.

  11. RedDerek
    January 11, 2014

    I do the linear crunching quite a bit with Excel. Did not try the CTRL method to select a column not adjacent. Is it always understood that the first selected column is X and the second is Y?

    I have found that with very scattered data, a good 'R' correlation helps. Then I start eliminating points that I can call trash data in order to get a tighter 'R'. Current project that is using this method is for temperature compensation for some strain gages used in the 787 brake systems that a company I work for manufactures.

    This is a good tool in the engineer's box. Oh, you can also select other equation formats as well, based on the basic characteristics of the curve – exponetial, polynomial, etc.

  12. Victor Lorenzo
    January 12, 2014

    @DaeJ, fortunately all current toolchains (at least for C compilers) provide suport for runtime libraries implementing all sort of math libraries. It is not too difficult to specify compiler options when building the libraries so its output code is optimized according to your needs (code size/execution speed).

    I use assembly language only when strictly needed like interrupt context switching and access to very specific CPU features not supported by generic compiler toolchains.

  13. antedeluvian
    January 12, 2014

    RedDerek

    Is it always understood that the first selected column is X and the second is Y?

    It is when you go with the defaults. Before Microsoft adopted the ribbon paradigm it was possible to work through the options to get to the chart you desired in a logical sequence. I have not foubnd tis route yet in the ribbon, but it is still possible to modify the chart you end up with- right click on the chart and click on Select Data.. .

    You can then switch data and axes to your heart's content.

     

    Oh, you can also select other equation formats as well, based on the basic characteristics of the curve – exponetial, polynomial, etc.

    Yes- thanks for pointing this out.

  14. eafpres
    January 12, 2014

    Hi Aubrey–I have lived with the newer versions of Excel for some time and the ribbon does sometimes obscure things.  I have found, as you mentioned, that often the fastest way is to select some data (almost anything) and choose the desired final chart type.  Then use the Design tab (which appears when the chart is selected) and Select Data.  You can then do various things.  Often, Excel kind of makes some dumb assumptions.  If you use the option “Switch Row/Column” very often you get a mess.  So the simplest way I have found is to select the data in the Series side of the Select Data dialog, then choose edit.  This allows you to redefine the x and y data by clicking on the icon on the right of each, and just click-highlighting whatever you want in the sheet.  

    It sounds like a lot but once you get used to it you can get exactly what you want very quickly.

  15. Vishal Prajapati
    January 13, 2014

    Thanks for  your suggestion victor. I would keep in mind your pointers. I have spent time for looking for both of their features. And I perticularly like the LPCXpresso boards. Their community support is also good.

  16. Victor Lorenzo
    January 13, 2014

    @Vishal, for starting experimentation the LPC11U14 LPCExpresso is very handy. We started with this board for a small project and replaced the LPC11U14 IC by and LPC1343 (which was more suitable for our purposes). We did not start with the LPC1343 LPCExpresso board for not having to ptototype the USB device port components.

    Both chips are mostly compatible at software level, but with different USB device IP cores.

    This low end LPC ICs are not as powerfull as most Kinetis microcontrollers, but cover a wide range of 8/16 bit MCU replacement applications and include USB MSD/HID drivers (device side) in ROM.

    One detail, there was a bug in the LPC1343's USB device driver ROM, but I suppose it should be corrected in current silicon revisions.

    For more demanding applications there are more Cortex M3/M4 devices from both series LPC/Kinetis.

  17. SunitaT
    January 31, 2014

    @Aubrey, thanks a lot for the post. I have never done curve fitting and linearization using Excel. After reading this article I am sure I will find it easy to implement curve fitting and linearization.

  18. etnapowers
    February 11, 2014
    @Victor, thank you for your post, the Cortex M4 based solution is for sure
    the best solution nowadays , many similar application have been developed
    mainly by ST and Freescale  for energy metering, smart power, gas
    monitoring and many other possible applications
  19. Victor Lorenzo
    February 11, 2014

    @etnapowers, I can recall several ST Cortex processors from the STM32 series. Could you please tell us about some ST kits with similar characteristics to those mentioned in previous posts? Thanks –Victor

  20. etnapowers
    February 11, 2014

    @Victor, I can recall the following from STM:

     

    AN4309

    AN4117

    these kits are STM32 Low power microcontroller based.

     

    • The AN4309 describes the use the STM32L1xx I2S feature to play audio files using an external codec

     

    • The AN4117 outlines the expected performance when using the SPIRIT1 under EN 300 220-1 (v2.3.1, 2012-02) in the 433.050 to 434.790 MHz band

     

  21. Victor Lorenzo
    February 11, 2014

    I was thinking more in kits and discovery packs like this (32F0308DISCOVERY) or some of the ones listed here.

    Low cost discovery kits are very handy when it comes to starting new projects or simply getting in touch with new technologies.

  22. chirshadblog
    February 12, 2014

    @Victor: Yes cost wise its good to go for a lower budgeted product for start-ups but if its beyond that then I feel its good to consider other factors 1st rather than going for the cost

  23. etnapowers
    February 12, 2014

    @Victor: Between the list that you provided I can indicate the STM32F0DISCOVERY:

    The STM32F0DISCOVERY helps you to discover the STM32F0 Cortex-M0 features and to develop your applications easily. It includes everything required for beginners and experienced users to get started quickly.

    Based on the STM32F051R8T6, it includes an ST-LINK/V2 embedded debug tool, LEDs, pushbuttons and an additional prototyping board for easy connection of additional components and modules.

    A large number of free ready-to-run application firmware examples are available on http://www.st.com/stm32f0discovery to support quick evaluation and development.

     

    This kit is really helpful in case of new technologies, as you correctly said.

  24. etnapowers
    February 12, 2014

    @chirshadblog: that's correct, cost is a important factor but it is not the only one that has to be optimized, expecially for very new technologies. Many other things have to be considered: the similar solution of competitors, for example, or the possible scenario of application of the new technology proposed.

  25. Victor Lorenzo
    February 12, 2014

    @chirshadblog, I agree with in that “its good to consider other factors “.

    When I start evaluating which processor best fits one particular project I not only look at cost, I also check its specifications, performance, power consumption, peripherals, tools support, development environment (and hardware debugger) availability and design support (sample kits, application notes, sample code and field application engineers or community support).

    The kits I mentioned in previous posts fit for one specific purpose, as learning platforms for people wishing to get in touch with Cortex M ARM powered SoCs.

    One very important thing, in my oppinion, is the deveopment environment. If I have to pay 4K-USD for the development environment + 1K-USD for the hardware debugger + 300-USD for the discovery kit + ??K-USD for basic libraries for communication/RTOS/LCD Display/Cryptography/etc…. chances are I'll not select and use that processor (SoC/MCU).

  26. antedeluvian
    March 24, 2014

    DaeJ, Victor, & Vishal

    A while ago we were discussing integer math on micros. I said

    “As you have all discussed, you can obviously implement any 2nd order expression in software using floating point or even integer arithmentic. All that changes is the precision, code space, exceution speed and of course ease of implementation.

    I have been planning to write a blog on integer arithmetic and this has spurred me on to complete it.”

    The blog has now been published. You can find it here “Integer Arithmetic Rules!”

Leave a Reply

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