Is there any way to access the trendline parameters in cell expressions?

[JenniferMurphy]

If I have a chart with a trendline, is there any way that I can use the trendline parameters in cell expressions?

The chart on the right plots the Conf column on the Y axis against the #Reviews column on the X axis. I found that a power series trendline matches almost perfectly. Now I want to use that equation to find the value for Conf based on other #Reviews values. To do that, I need the parameters of the power equation (coefficient = 1.2421 & exponent = -0.498).

I don't see any way to access those parameters in a cell equation. Is there any way?

Suppose I want

 

[awoohaw]

I saw a recent video by My Online Training Hub recently that used the LINEST function to return the trendline co-efficients.
this is what I get when I use that with your data. I hope this gives some help:

I think the first value is the slope and the 2nd is the y intercept.

Book1
	A	B	C	D
1	Rtg	#Reviews	Rank	Conf
2	5	9467	1	0.0132
3	3	9101	2	0.0130
4	4	8499	3	0.0136
5	5	744	4	0.0445
6	4	444	5	0.0608
7	3	427	6	0.0625
8	5	88	7	0.1328
9	4	56	8	0.1673
10	3	39	9	0.2045
11	4	9	10	0.4081
12	5	4	11	0.6270
13	3	2	12	0.8727
14
15	=LINEST(D2:D13,B2:B13)
16	-3.30607E-05	0.297899387
JenniferMurphy

Cell Formulas
Range	Formula
A15	A15	=FORMULATEXT(A16)
A16:B16	A16	=LINEST(D2:D13,B2:B13)
Dynamic array formulas.

 

[awoohaw]

There is also a LOGEST function for exponental charts . and maybe that will work with logarithms too.

 

[JenniferMurphy]

I saw a recent video by My Online Training Hub recently that used the LINEST function to return the trendline co-efficients.

The LINEST function fits a linear equation to the data. My data closely fits a power equation. A linear equation is not useful. I added a linear trendline to my chart. It shows the same parameters as you reported. I need the parameters for a power equation trendline.

PS: The linear trendline on the chart is not a straight line, because the X axis is logarithmic.

 

[awoohaw]

The LINEST function fits a linear equation to the data. My data closely fits a power equation. A linear equation is not useful. I added a linear trendline to my chart. It shows the same parameters as you reported. I need the parameters for a power equation trendline.

View attachment 91393

PS: The linear trendline on the chart is not a straight line, because the X axis is logarithmic.

yes, i figured that. The LOGEST may work better for you.

 

[AhoyNC]

Maybe this will help. The link below shows how to calculate a power trendline in Excel (formula is near the bottom of the page).

Excel trendline types, equations and formulas

The tutorial describes all trendline types available in Excel: linear, exponential, logarithmic, polynomial, power, and moving average. Learn how to display a trendline equation in a chart and make a formula to find the slope of trendline and y-intercept.

www.ablebits.com

Book1
	A	B	C	D	E	F	G
1	Rtg	#Reviews	Rank	Conf
2	5	9467	1	0.0132			1.242679332
3	3	9101	2	0.013			-0.498242034
4	4	8499	3	0.0136
5	5	744	4	0.0445
6	4	444	5	0.0608
7	3	427	6	0.0625
8	5	88	7	0.1328
9	4	56	8	0.1673
10	3	39	9	0.2045
11	4	9	10	0.4081
12	5	4	11	0.627
13	3	2	12	0.8727
Sheet2

Cell Formulas
Range	Formula
G2	G2	=EXP(INDEX(LINEST(LN(D2:D13),LN(B2:B13),,),1,2))
G3	G3	=INDEX(LINEST(LN(D2:D13),LN(B2:B13),,),1)

 

[JenniferMurphy]

Maybe this will help. The link below shows how to calculate a power trendline in Excel (formula is near the bottom of the page).

Excel trendline types, equations and formulas

The tutorial describes all trendline types available in Excel: linear, exponential, logarithmic, polynomial, power, and moving average. Learn how to display a trendline equation in a chart and make a formula to find the slope of trendline and y-intercept.

www.ablebits.com

That works perfectly. Thanks.

I will never remember that expression, so I'd like to put in a UDF. Is there a way that I can use those expressions in VBA without putting application.worksheet in front of each function call?

 

[AhoyNC]

My knowledge of VBA is limited. Hopefully someone else can help you with a UDF.