Parsing trendline equation

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

Retiredgeek
Thanks for the additional suggestion. A nice elegant addition.
Arjay

Reply | Quote

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

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

The LINEST route is the only reliable one. See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

Reply | Quote