# Parsing trendline equation

• 2012-12-10, 10:35
arjay13
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
• 2012-12-10, 12:01
sdckapr
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
• 2012-12-10, 15:05
RetiredGeek
Now that Steve did the heavy lifting...

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:
• 2012-12-10, 15:13
arjay13
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
• 2012-12-10, 15:22
arjay13
Retiredgeek
Arjay
• 2012-12-10, 15:23
sdckapr
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
• 2012-12-10, 16:32
arjay13
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
• 2012-12-11, 07:19
sdckapr
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
• 2012-12-13, 08:43
bliengme
The LINEST route is the only reliable one. See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm