Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linest function for curve | Excel Discussion (Misc queries) | |||
challenge! javascript function into excel function | Excel Worksheet Functions | |||
curve! function | Excel Worksheet Functions | |||
MATCH FUNCTION?...challenge | Excel Worksheet Functions | |||
A COUNTIF function Challenge | Excel Worksheet Functions |