How to pull data from an exponential trendline chart automatically to a cell.

[duken58]

I have an exponential trendline in a chart using two rows of data. Per the mini-sheet or the attached picture (the only place to see my graph), the orange column is my X axis and my green column is my Y axis. The exponential trendline gives me an equation of y = 249.16e-0.133x & R² = 0.9954. I want to be able to have a cell in the sheet automatically pull my a value (249.16 from the previous chart equation) from the chart formula. Is there a way to do this? Our sheet is setup so whenever we change the date of the sheet it changes our graph equation. Whatever this a value changes to we currently have to manually type into our cell for the rest of our spreadsheet to auto adjust based on the new values. Or does anyone know how excel calculates the exponential trendline equation to where I could have it typed in a cell and pull the data from the cell if it's easy than the chart? Thanks for any help. Just trying to automate the process.

CAL2001HP Grey 2022.xlsx
	A	B	C	D	E	F	G
12	1		0.00		468.00		258.51
13	2		0.50		428.10		236.47
14	3		1.00		396.10		218.80
15	4		1.50		366.90		202.67
16	5		2.00		342.30		189.08
17	6		2.50		317.10		175.16
18	7		3.00		297.10		164.11
19	8		3.50		277.90		153.51
20	9		4.00		259.90		143.56
21	10		4.50		245.70		135.72
22	11		5.00		230.80		127.49
23	12		5.50		218.40		120.64
24	13		6.00		206.20		113.90
25	14		6.50		196.26		108.41
26
27			y = a * ebx
28			a=	249.1645
29			b=	-0.133
200-0

Cell Formulas
Range	Formula
G12:G25	G12	=E12*(EXP(-0.6931*((DATEVALUE($F$5)-DATEVALUE($F$4))/365)/30.1))

 

[BSALV]

I did that once with VBA, read the label which i previous changed in scientific format with 9 digits

              s =Sheets("sheet1").ChartObjects("GrafiekTest").Chart.FullSeriesCollection(tr).Trendlines(1).DataLabel.Text
               s = Replace(Replace(Replace(Replace(s, "+ ", "+"), "- ", "-"), Chr(11), " "), " =", "=")
               sp = Split(s)
               ReDim a(UBound(sp), 2)
               For i = 0 To UBound(sp)
                    a(i, 0) = sp(i)
                    sp1 = Split(sp(i), "E")
                    If UBound(sp1) = 1 Then
                         a(i, 1) = "'" & sp1(0)
                         a(i, 2) = Mid(sp1(1), 1, 3)
                    End If
               Next
               .Range("K72").Resize(UBound(a) + 1, 3) = a

Your label 'll be a little bit different as it's an exponential curve.
The question is how much different ?

forum.xlsb
	K	L	M	N
72	y=
73	-1,392178736E-09x2	-1,392178736	-9	-1,39218E-09
74	+2,368814671E-03x	+2,368814671	-3	0,002368815
75	+1,453428082E+04	+1,453428082	4	14534,28082
76	R²=
77	9,967066166E-01	9,967066166	-1	0,996706617
Sheet1

Cell Formulas
Range	Formula
N73:N75,N77	N73	=+L73*POWER(10,M73)

 

[BSALV]

if you want to do the real stuff : Using LINEST for non-linear curve fitting

 

[BSALV]

Sub Exponentional_Trend()
     Set sh = Sheets("blad2")                                   'your sheet
     Set chrt = sh.ChartObjects(1).Chart                        'your chart
     Set sr = chrt.FullSeriesCollection(1)                      'the wanted serie and trendline
     With sr.Trendlines(1).DataLabel                            'the datalabel
          .NumberFormat = "0,000000000E+00"                     'extra digits
          s = .Text                                             'read label
     End With

     s = Replace(Replace(Replace(Replace(Replace(Replace(Replace(s, "+ ", "+"), "- ", "-"), Chr(11), " "), " =", "="), "e", " ", , , 0), ",", "."), "x", "")     'modify a little
     sp = Filter(Filter(Split(s), "R", 0), "y", 0)              'split on the spaces and get rid of annoying parts
     MsgBox Join(sp, vbLf)
     sh.Range("Q1").Resize(UBound(sp) + 1) = Application.Transpose(sp)     'your 3 parts
End Sub