+ Reply to Thread
Results 1 to 10 of 10

How to deal with a non-linear calibration curve

  1. #1
    Registered User
    Join Date
    04-07-2008
    Posts
    8

    How to deal with a non-linear calibration curve

    I have a non-linear calibration curve that I am trying to deal with. What is the easiest way in Excel 2007 to do this? I am including an example of what I need to do. In the example I have a linear equation - this is what the data needs to correspond to. The nonlinear curve is the calibration curve. (This is only an example, my actual calibration curve has hundereds of points, so please keep that in mind when responding).

    Basically, I need something of a curve fit, or look up table. When I use my sensor and get, let's say, I put 10 pounds on the sensor, but I only get a reading of 7. I need that 7 value to translate to 10 - a linear fit so to speak. So how do I take this non-linear calibration curve, and get the data to correspond to what the readings should be (linear) - I need to know if I put 10 pounds on the scale and get 7 as an output, that it is actually 10, not 7. Thanks for any help!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to deal with a non-linear calibration curve

    No attachment.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-07-2008
    Posts
    8

    Re: How to deal with a non-linear calibration curve

    D'oh, my bad, here is the attachment
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to deal with a non-linear calibration curve

    So, you want to interpolate the polynomial, or interpolate the linear fit, or interpolate linearly between the measured points, or ... what?

  5. #5
    Registered User
    Join Date
    04-07-2008
    Posts
    8

    Re: How to deal with a non-linear calibration curve

    I basically want to interpolate the curved polynomial data to the linear fit. I would prefer to do it with equations, instead of just doing a basic interpolation with a look up table of sorts.

  6. #6
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to deal with a non-linear calibration curve

    linest is the tool (it creates the numbers you have in the equation by your trendline)

    You would need to put your x values in the bit called 'y values' and vice-versa to get the figures in the right order and include the necessary magic to make it a polynomial equation:
    {=linest(known ys, known xs^{1,2,3,4,5,6})}

    I haven't provided much in-depth instruction, I don't know how adept you are, let us know if you need more.
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  7. #7
    Registered User
    Join Date
    04-07-2008
    Posts
    8

    Re: How to deal with a non-linear calibration curve

    I might need a little more. I've never been really good with this sort of thing. Is there a way to do a look up table like this? Like, say if the value is between .03 and .04 it will return "x" value and so on? Basically a way to spit out a certain number if it is in a range, but without having to enter the specific numbers (I have hundreds of calibration points and they are in the thousandths range, so to enter those ranges would take forever). So i guess it would be something like, if the value "x" falls between these to cell values, then give me "y" as the value...

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to deal with a non-linear calibration curve

    See attached.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to deal with a non-linear calibration curve

    LINEST is an array formula which creates the coefficients in the formula:
    y = ax^6 + bx^5 + .. + fx + g
    For the line which most closely matches (according to the least squares method) a relationship provided with known x values and known y values reflecting that relationship.

    Once you know the coefficients it's simply a case of calculating y for any given x by multiplying and adding the numbers back up (according to the equation above).

    So:
    we take your file
    we select the cells L2:R2
    we type: =linest(H2:H7,J2:J7^{1,2,3,4,5,6})
    we hit ctrl+shift+enter
    in L2:R2 we now have a, b, c ... f as outlined in the equation above
    You want to turn a 7 into a 10, so in H8 we write:
    =L2*J8^6+M2*J8^5+N2*J8^4+O2*J8^3+P2*J8^2+Q2*J8+R2
    (this is the formula i keep banging on about)
    Then when we put 7 in J8, H8 shows 10

    I have shown the very specific steps for the very specific example, it will take a little playing around with to get comfortable with how it works, but I'm sure you'll recognise how much more powerful a formulaic solution is than your multiple-small-window-linear-interpolations idea.

    hth

    ...

    I really need to stop writing half a post then wandering off - I have explained, shg has demonstrated (although to different degrees) between us you should get it
    Last edited by Cheeky Charlie; 07-20-2010 at 05:48 PM. Reason: addendum

  10. #10
    Registered User
    Join Date
    04-07-2008
    Posts
    8

    Re: How to deal with a non-linear calibration curve

    Done and done, thanks for the help fellas. Linest it is...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1