Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Parsing trendline equation

    Hi
    I have written a macro to parse a trendline equation (peak area vs concentration) so I can use it to calculate chemical concentrations from area data. What I have written does the job for a single trendline equation on a spreadsheet. However, when I run the macro on a second trendline equation on the same sheet, the first set of extracted coefficients becomes the same as the second set of coefficients. This is because my macro places a formula in the cell I want the coefficient to be in, not a value (see attached JPG). Is there some way that I can convert the extracted coefficient from a formula to a value?
    Thanks
    Arjay
    Attached Images Attached Images

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,201
    Thanks
    14
    Thanked 330 Times in 323 Posts
    To use values instead of formulas, your code could be written like:
    Code:
    Sub Parse_Coeffs()
      Dim str As String
      With ActiveCell
        str = .Value
        If Left(str, 1) = "-" Then
          .Offset(1, 0) = Left(str, 10)
          .Offset(2, 0) = Mid(str, 13, 12)
          .Offset(3, 0) = Mid(str, 27, 12)
        Else
          .Offset(1, 0) = Left(str, 9)
          .Offset(2, 0) = Mid(str, 12, 12)
          .Offset(3, 0) = Mid(str, 26, 12)
        End If
      End With
    End Sub
    This puts the calc in VBA and just adds the values into the cells, instead of putting the formulas into the cells.

    Steve

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    arjay13 (2012-12-10)

  5. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,042
    Thanks
    195
    Thanked 755 Times in 691 Posts
    Now that Steve did the heavy lifting...
    How about:

    Code:
    Sub Parse_Coeffs()
    
            Dim str   As String
            Dim iAdj As Integer
    
             iAdj = 0 
            
            With ActiveCell 
                   str = .Value 
                   If Left(str, 1) = "-" Then iAdj = 1 
                   .Offset(1, 0) = Left(str, 9 + iAdj) 
                   .Offset(2, 0) = Mid(str, 12 + iAdj, 12)     
                   .Offset(3, 0) = Mid(str, 26 + iAdj, 12) 
           End With
    
     End Sub
    Last edited by RetiredGeek; 2012-12-10 at 14:09.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sdckapr
    Many thanks for the prompt reply as well as the tidy code. It works better and cleaner than what I had ginned up. I discovered that if I copy the entire trendline equation including the "=" (accounting for the added characters in the "left" and "mid" commands), the macro doesn't get the exponents and leading signs correct with a leading minus sign after the "=". If I leave out the "=" sign and carefully cut the equation, it works perfectly!
    Thanks
    Arjay

  7. #5
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Retiredgeek
    Thanks for the additional suggestion. A nice elegant addition.
    Arjay

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,201
    Thanks
    14
    Thanked 330 Times in 323 Posts
    Another option, instead of copying the trendline, is to use LINEST to calculate the coefficients. You then get them "live" if any values change...

    Steve

  9. #7
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Steve
    Yes, I usually use linest for the bulk of my work but I occasionally find it more convenient to just grab the regression equation from the chart for a quick check.
    Arjay

  10. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,201
    Thanks
    14
    Thanked 330 Times in 323 Posts
    One thing to be aware of is that the function you created is not generic and will depend on the type of equation and also the number of decimal points displayed.

    Steve

  11. #9
    New Lounger
    Join Date
    Mar 2002
    Location
    Nova Scotia, Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The LINEST route is the only reliable one. See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

Posting Permissions

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