Excel Array Formula Series #13: LINEST function

Hi,

I have set up regression in excel using the linest array function which automaticly updates whenever the data it is references changes. Currently the formula in K3 =
=LINEST(K8:K50,M8:N50,TRUE,TRUE) .

Only the Y range in column K changes and then the formula automaticly recalculates the regression parameters. However sometimes the range of valid data changes with each new update so currently K8 to K50 contains valid values and so the formula works. But if the range of valid data now ends at say K45, and the remaining rows (K46- K450) contains #DIV/0!. In this case the formula does not work and I need to go in manualy into K3 and reselect the range to get it to work (in the baove case to =LINEST(K8:K45,M8:N45,TRUE,TRUE)

Is there anyway I can get the formula to update the range (the number of rows to use) in the calculation automaticly so I don't have to go into the formula in K3 and change the range manually everytime the data changes? Basicly I like to use all rows in column K (from K8 to K50) where there is a values and ignore cells with #DIV/0!

I'm guessing the easiest way to do this would be to set up a dynamic range of some sort that feeds into the linest array function so that the end row reference for columns K and M & N changes depending on where the first #DIV/0! occurs in column K.

Hope someone can help.

Thanks,

FellowExcellor

Hi,

I am trying to replicate Example 3:
http://office.microsoft.com/en-us/ex...005209155.aspx

I entered LINEST as an array using CTRL-SHIFT-ENTER and it only returned the single result and did not include the regression statistics.

I am using Excel 2010.

Any suggestions on why it is not working?

Thanks

Hi all, I've noticed that when I graph an array of data, then fit a 4th order polynomial curve and add a trendline + equation to the graph, the coefficients are different to those returned from the following LINEST array formula:

{=LINEST(A1:A10,B1:B10^{1,2,3,4})}

Can anyone see what the problem is here? Recently my work PC was upgraded and so excel was reinstalled. I have older spreadsheets with the same formula and everything is correct. I simply replicated the formula but it is giving different numbers to the graph output this time. Has there been any change to excel 2007 in the past 2yrs that might lead to this error?

I am using The Indirect Function to construct a linest formula of a variable array size.
Code:

="=linest("&"R"&TEXT(INDIRECT("R3C23",FALSE)+RC31,0)&"C"&TEXT(RC[-2],0)&":
"&"R"&TEXT(INDIRECT("c31",FALSE),0)&"C3"&",R"&TEXT(INDIRECT("R3C23",FALSE)
+RC31,0)&"C4;R"&TEXT(INDIRECT("rc31",FALSE),0)&"C"&TEXT(R25C22+3,0)&",TRUE,FALSE)"

My problem is how to automatically convert the resulting text sting to an actual formula which calculates linest. It is at this point that I am stuck. Perhaps this could be done using a more complex single formula or perhaps it requires a Macro of some sort?


Help as always is appreciated.

abe

Hi,

I have a list of names in column B, separated by categories. Each category name is followed by list of names. Each category has different # of names under it.

For eg. a category name in cell B4 is followed by 6 names (B5:B10); then follows a blank cell (B11); then 2nd category name in a cell (B12) followed by 4 names (B13:B16); and so on and so forth.

I want to fill the column A with numbers against the names (in column B) only and not against the blank cells and category names.

I can fill the series using + sign or "=A5+1" which is not what I am looking for. When I add a name in any of the categories or delete a name from a category, the fill series has to be updated completly, from below the changed row, manually.

I tried the following array formula -

{=IF(AND(COUNTIF(B6,"*")>0,COUNTIF(B5,"*")>0),(IF(COUNTIF(B4,"*")>0,B5+1,B3+1)),"")}

This formula returns 1 in A5 but each subsequent cell in column A is giving #VALUE! except the cells in column A that have category name or blank cell in column B.

So it is doing one part i.e. not numbering cells against category names and blank cells but it is not adding the way I want them to.

Would greatly appreciate if someone can help me with this.

Thanks.

I am new here so this question may have already been asked. I am working with the linest function

=INDEX(LINEST(AK6:AK16,Z6:Z16),1)

in creating a slope across a series of numbers. I want to define the number of points in this line by a separate cell. right now there are 11 points defining the slope. The only thing I can think of doing is creating a really long if clause saying

=INDEX(LINEST(IF(A1=11,AK6:AK16,IF (A1=10,AK6:AK15,if etc...),IF(A1=11,Z6:Z16,if(A1 =10 Z6:Z15, If etc...),1)

Continuing the string for all the numbers 1-34. I would like to define the number in A1 and have it change the number of points accordinly without need this long if clause.

Basically is there an easier way to do this?

Is there a limitation to the LINEST formula?

I am trying to write a LINEST formula that only considers a sub-set of cells in a cell-range using an array formula. With other formulas like AVERAGE or SUM, you can put an IF statement inside the formula to make it a conditional array. I get a #VALUE error when I try the following formula.

EXAMPLE:

Col B Col C Col D Col E

Yvar Xvar1 Xvar2 "condition" 6.06 2.68 4.16 1 20.18 0.39 26.30 1 -4.91 -6.45 -0.30 2 7.24 9.14 9.80 1 -17.99 -11.98 -0.67 2 -11.45 -12.92 2.85 2 0.67 -0.90 1.06 1 3.65 -3.61 5.53 1 4.53 10.05 -2.20 2 4.54 7.08 9.48 2 =LINEST(IF(E2:E11=1,B2:B11),IF(E2:E11=1,C2:D11),TRUE,TRUE)
press ctrl+shift+enter to make array formula

The above formula gives me a #VALUE error. What am I doing wrong? I know that array formulas work with SLOPE and INTERCEPT but I can't use those, because I need to do multiple regression and return the ANOVA stats that LINEST provides.

I haven't been able to find anything that addresses this with my searches online. I know that I should just use a stats program to do this, but I don't know how to program in R or SAS or any of that stuff. Please help! Or tell me its impossible.

Hello,

I need to use LINEST to calcuate the slope for multiple regression, and convert the model to Java/HTML for the web. Problem is, the software will not support arrays. What options should I consider to generate the slope coefficients via LINEST function and avoiding using an array?