Results 1 to 9 of 9
Thread: Parsing trendline equation

20121210, 10:35 #1
 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

20121210, 12:01 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 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
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
arjay13 (20121210)

20121210, 15:05 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 6,697
 Thanks
 219
 Thanked 894 Times in 819 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; 20121210 at 15:09.

20121210, 15:13 #4
 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

20121210, 15:22 #5
 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

20121210, 15:23 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 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

20121210, 16:32 #7
 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

20121211, 07:19 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 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

20121213, 08:43 #9
 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