UDF Challenge for Curve Fit Function

[Brad Carman]

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+INDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),2)*Time^2+INDEX(LINEST(TaveRNG,TimeRNG^{1,2,3}),3)*Time+INDEX(LINEST(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?

 

[Tushar Mehta]

Array ente
=MMULT(LINEST($E$1:$E$16,$D$1:$D$16^{1,2,3},TRUE,FALSE),TRANSPOSE(D1^{3,2,1,0}))
where E1:E16 contain the y values, D116 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]

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]

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