UDF Challenge for Curve Fit Function
 

Does anyone have a suggestion of how I can enter the following array formula
into Excel in a much simpiler way?

=INDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),1)*Time^3+I NDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),2)*Time^2+IND EX(LINEST(TaveRNG,TimeRNG^{1,2,3}),3)*Time+INDEX(L INEST(TaveRNG,TimeRNG^{1,2,3}),4)

To explain:
LINEST(TaveRNG,TimeRNG^{1,2,3}) returns an array of coefficients {a,b,c,d}
for my curve fit
y = a*x^3 + b*x^2+c*x+d

I have a feeling that this could be entered into excel something like:
=SUM(LINEST(A1:A10,B1:B10^{1,2,3})*{B1^3,B1^2,B1,1 })
except I can't build an array between brackets { } with a reference to a
cell, this only works with constants.

Do I need to create a custom UDF to handle something like this?

UDF Challenge for Curve Fit Function
 

Array ente
=MMULT(LINEST($E$1:$E$16,$D$1:$D$16^{1,2,3},TRUE,F ALSE),TRANSPOSE(D1^{3,2,1,0}))
where E1:E16 contain the y values, D1:D16 the x values.

To array enter a formula complete entry not with the ENTER key but the
CTRL+SHIFT+ENTER combination.

Of course, you would be better off having XL calculate the LINEST result
only once. Enter the result of the LINEST in a range and then use an array
formula like
=MMULT($G$1:$J$1,TRANSPOSE(D1^{3,2,1,0}))
where G1:J1 contains the result of the LINEST formula.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Brad Carman" wrote:

Does anyone have a suggestion of how I can enter the following array formula
into Excel in a much simpiler way?

=INDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),1)*Time^3+I NDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),2)*Time^2+IND EX(LINEST(TaveRNG,TimeRNG^{1,2,3}),3)*Time+INDEX(L INEST(TaveRNG,TimeRNG^{1,2,3}),4)

To explain:
LINEST(TaveRNG,TimeRNG^{1,2,3}) returns an array of coefficients {a,b,c,d}
for my curve fit
y = a*x^3 + b*x^2+c*x+d

I have a feeling that this could be entered into excel something like:
=SUM(LINEST(A1:A10,B1:B10^{1,2,3})*{B1^3,B1^2,B1,1 })
except I can't build an array between brackets { } with a reference to a
cell, this only works with constants.

Do I need to create a custom UDF to handle something like this?

UDF Challenge for Curve Fit Function
 

Thanks for your help, this works great!!

One more question, is there anyway to write the TRANSPOSE part like

=STACK(TRANSPOSE( D1^{3,2,1} , 1 )

which would give an array that looks like {D1^3,D1^2,D1,1}. Reason being is
because when D1 is 0 then D1^0 does not give 1 but gives a #NUM! result.
Excel does not have a STACK function similar to MathCAD, but is there
anything similar? Or any suggestions on how to create a UDF stack function?
It would be nice to allow the UDF to accept an undefined number of arguments,
is there a way to do this in VBA?

"Tushar Mehta" wrote:

Array enter
=MMULT(LINEST($E$1:$E$16,$D$1:$D$16^{1,2,3},TRUE,F ALSE),TRANSPOSE(D1^{3,2,1,0}))
where E1:E16 contain the y values, D1:D16 the x values.

To array enter a formula complete entry not with the ENTER key but the
CTRL+SHIFT+ENTER combination.

Of course, you would be better off having XL calculate the LINEST result
only once. Enter the result of the LINEST in a range and then use an array
formula like
=MMULT($G$1:$J$1,TRANSPOSE(D1^{3,2,1,0}))
where G1:J1 contains the result of the LINEST formula.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Brad Carman" wrote:

Does anyone have a suggestion of how I can enter the following array formula
into Excel in a much simpiler way?

=INDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),1)*Time^3+I NDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),2)*Time^2+IND EX(LINEST(TaveRNG,TimeRNG^{1,2,3}),3)*Time+INDEX(L INEST(TaveRNG,TimeRNG^{1,2,3}),4)

To explain:
LINEST(TaveRNG,TimeRNG^{1,2,3}) returns an array of coefficients {a,b,c,d}
for my curve fit
y = a*x^3 + b*x^2+c*x+d

I have a feeling that this could be entered into excel something like:
=SUM(LINEST(A1:A10,B1:B10^{1,2,3})*{B1^3,B1^2,B1,1 })
except I can't build an array between brackets { } with a reference to a
cell, this only works with constants.

Do I need to create a custom UDF to handle something like this?

UDF Challenge for Curve Fit Function
 

Array enter =MMULT($G$2:$J$2,TRANSPOSE(IF(D1<0,D1^{3,2,1,0},{ 0,0,0,1})))
where G2:J2 contains the result of the LINEST function.

Or, you could simplify things with the regular formula
=$G$2*D1^3+$H$2*D1^2+$I$2*D1+$J$2
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Brad Carman" wrote:

Thanks for your help, this works great!!

One more question, is there anyway to write the TRANSPOSE part like

=STACK(TRANSPOSE( D1^{3,2,1} , 1 )

which would give an array that looks like {D1^3,D1^2,D1,1}. Reason being is
because when D1 is 0 then D1^0 does not give 1 but gives a #NUM! result.
Excel does not have a STACK function similar to MathCAD, but is there
anything similar? Or any suggestions on how to create a UDF stack function?
It would be nice to allow the UDF to accept an undefined number of arguments,
is there a way to do this in VBA?

"Tushar Mehta" wrote:

Array enter
=MMULT(LINEST($E$1:$E$16,$D$1:$D$16^{1,2,3},TRUE,F ALSE),TRANSPOSE(D1^{3,2,1,0}))
where E1:E16 contain the y values, D1:D16 the x values.

To array enter a formula complete entry not with the ENTER key but the
CTRL+SHIFT+ENTER combination.

Of course, you would be better off having XL calculate the LINEST result
only once. Enter the result of the LINEST in a range and then use an array
formula like
=MMULT($G$1:$J$1,TRANSPOSE(D1^{3,2,1,0}))
where G1:J1 contains the result of the LINEST formula.

--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Brad Carman" wrote:

Does anyone have a suggestion of how I can enter the following array formula
into Excel in a much simpiler way?

=INDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),1)*Time^3+I NDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),2)*Time^2+IND EX(LINEST(TaveRNG,TimeRNG^{1,2,3}),3)*Time+INDEX(L INEST(TaveRNG,TimeRNG^{1,2,3}),4)

To explain:
LINEST(TaveRNG,TimeRNG^{1,2,3}) returns an array of coefficients {a,b,c,d}
for my curve fit
y = a*x^3 + b*x^2+c*x+d

I have a feeling that this could be entered into excel something like:
=SUM(LINEST(A1:A10,B1:B10^{1,2,3})*{B1^3,B1^2,B1,1 })
except I can't build an array between brackets { } with a reference to a
cell, this only works with constants.

Do I need to create a custom UDF to handle something like this?