LINEST function for exponential regression with one fixed parameter

[gifariz]

Hi all. This question is more about mathematical problem than excel, but, anyway..
So, I have a data to do exponential regression using LINEST function.
Suppose the trendline would be y = a*e^bx, I can get the parameters by (please see screenshot for the data):
a=EXP(INDEX(LINEST(LN(B2:B5),A2:A5),1,2))
b=INDEX(LINEST(LN(B2:B5),A2:A5),1,1)

But I need to set 'a' parameter as fixed value, in this case as a=y(0)=563.21.
Is it possible to do this? How should I modify the y values before inputting to LINEST function?
Thank you in advance.

 

[KRice]

This is what I would do in Excel...based on the form of your regression model, subtract ln(a) from your y values, then exercise the constraint option in LINEST to make the intercept 0. Then after the regression, shift the a' regression result by ln(a).

MrExcel_20220315.xlsx
	A	B	C	D	E	F	G
1	x	y	y_regression	ymod		Regression of form y(x)=a*exp(b*x)
2	0	563.21	533.1517	563.2100		a	533.151651
3	31.5	321.67	329.2628	340.0775		b	-0.01529999
4	66.5	170.76	192.7428	194.1520
5	98.1	129.99	118.8517	117.0453		Regression to obtain ymod by constraining "a"
6						a_fixed	563.21
7						a'	563.21
8						b'	-0.0160152
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=$G$2*EXP($G$3*$A2)
D2:D5	D2	=$G$7*EXP($G$8*$A2)
G2	G2	=EXP(INDEX(LINEST(LN($B$2:$B$5),$A$2:$A$5),1,2))
G3	G3	=INDEX(LINEST(LN(B2:B5),A2:A5),1,1)
G7	G7	=EXP(INDEX(LINEST(LN(B2:B5)-LN(G6),A2:A5,FALSE),1,2)+LN(G6))
G8	G8	=INDEX(LINEST(LN(B2:B5)-LN(G6),A2:A5,FALSE),1,1)

A comparison of the original data, baseline non-constrained regression result, and constrained regression results:

 

[gifariz]

This is what I would do in Excel...based on the form of your regression model, subtract ln(a) from your y values, then exercise the constraint option in LINEST to make the intercept 0. Then after the regression, shift the a' regression result by ln(a).

MrExcel_20220315.xlsx
	A	B	C	D	E	F	G
1	x	y	y_regression	ymod		Regression of form y(x)=a*exp(b*x)
2	0	563.21	533.1517	563.2100		a	533.151651
3	31.5	321.67	329.2628	340.0775		b	-0.01529999
4	66.5	170.76	192.7428	194.1520
5	98.1	129.99	118.8517	117.0453		Regression to obtain ymod by constraining "a"
6						a_fixed	563.21
7						a'	563.21
8						b'	-0.0160152
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=$G$2*EXP($G$3*$A2)
D2:D5	D2	=$G$7*EXP($G$8*$A2)
G2	G2	=EXP(INDEX(LINEST(LN($B$2:$B$5),$A$2:$A$5),1,2))
G3	G3	=INDEX(LINEST(LN(B2:B5),A2:A5),1,1)
G7	G7	=EXP(INDEX(LINEST(LN(B2:B5)-LN(G6),A2:A5,FALSE),1,2)+LN(G6))
G8	G8	=INDEX(LINEST(LN(B2:B5)-LN(G6),A2:A5,FALSE),1,1)

A comparison of the original data, baseline non-constrained regression result, and constrained regression results:
View attachment 60345

Wow that's really perfect. Thank you very much sir.

 

[KRice]

I’m happy to help.