# LINESTIF - Can you help me create an array formula to perform this function without VBA?

1. ## LINESTIF - Can you help me create an array formula to perform this function without VBA?

I would like to create a version of linest that only performs the analysis on a subset of the x's and y's that match a certain "if" statement. I have tried a number of the usual methods of doing something like this with array formulas, to no avail. See attached file for examples of a couple of the things I have tried, and for an example of what the solution is supposed to look like.

Please note that the final version of this will have about 5,000 rows and about 5 columns of "x's" (i.e. multiple regression). I know there are some solutions that are technically feasible (like creating a secondary dynamically sorted table), but a solution which is efficient with many rows would be ideal.

2. ## Re: LINESTIF - Can you help me create an array formula to perform this function without VB

An "ordinary" array function would still return an array the same size as the original data columns, you need a version that will completely exclude those values - you can do that with OFFSET and SMALL, i.e. like this

=LINEST(N(OFFSET(\$B\$4,SMALL(IF(\$A\$4:\$A\$200="A",ROW(\$A\$4:\$A\$200)-ROW(\$A\$4)),ROW(INDIRECT("1:"&COUNTIF(\$A\$4:\$A\$200,"A")))),0)),N(OFFSET(\$C\$4,SMALL(IF(\$A\$4:\$A\$200="A",ROW(\$A\$4:\$A\$200)-ROW(\$A\$4)),ROW(INDIRECT("1:"&COUNTIF(\$A\$4:\$A\$200,"A")))),0)),TRUE,TRUE)

3. ## Re: LINESTIF - Can you help me create an array formula to perform this function without VB

Wow. That worked, thank you daddylonglegs!

Two quick questions:
(1) Do you think it's possible to implement this with dynamic named ranges for the x's and y's using a similar structure to what you did here? This may have the benefit of allowing me to keep the complexity hidden away from the user inside the named ranges dialog.
(2) Can you walk me through what is happening here? In particular:
• What is the use of the N function? I have seen other formulas use this, but I don't have a concept of why.
• I notice that this function combines columns of different length. For example, "\$A\$4:\$A\$200" is a column with 197 rows, whereas the portion of this formula that says 'ROW(INDIRECT("1:"&COUNTIF(\$A\$4:\$A\$200,"A")))' is a column with about 50 rows (exact length depending on the results of the countif formula). How does Excel handle these differently-sized arrays?

I've reattached the workbook, now with this solution (modified to accept any general input from the dropdown menu in the top left corner of the workbook, and to correct it so that 'y' is the first argument and 'x' is the second).

4. ## Re: LINESTIF - Can you help me create an array formula to perform this function without VB

I don't think you can use a named range for the whole OFFSET part but try replacing the SMALL function with a named range, e.g. define a name Row_List as

=SMALL(IF(\$A\$4:\$A\$200=\$B\$1,ROW(\$A\$4:\$A\$200)-ROW(\$A\$4)),ROW(INDIRECT("1:"&COUNTIF(\$A\$4:\$A\$200,\$B\$1))))

then you can use this version

=LINEST(N(OFFSET(\$B\$4,Row_List,0)),N(OFFSET(\$C\$4,Row_List,0)),TRUE,TRUE)

To explain.....

The COUNTIF function counts the "A"s in column A, and if there are 11 of them, for instance then this part

ROW(INDIRECT("1:"&COUNTIF(\$A\$4:\$A\$200,\$B\$1)))

....just returns an array of integers 1 to that number, so with 11 that would be

{1;2;3;4;5;6;7;8;9;10;11}

That array forms the 2nd argument of SMALL so you now get

=SMALL(IF(\$A\$4:\$A\$200=\$B\$1,ROW(\$A\$4:\$A\$200)-ROW(\$A\$4)),{1;2;3;4;5;6;7;8;9;10;11})

....and the IF function returns the relative row numbers of the range (row 4 being zero, row 5 being 1, row 6 being 2 etc. so that might be like this with 11 rows

=SMALL({0;FALSE;FALSE;FALSE;FALSE;5;...........196;FALSE},{1;2;3;4;5;6;7;8;9;10;11})

so that just returns the relative row numbers you need (with nothing in there for the rows that don't qualify, e.g.

={0;5;17;45;67;100;102;130;160;163;196}

i.e. an array of only the relative row numbers of the "A" rows, so in OFFSET that gives you this for x values

OFFSET(\$B\$4,Row_List,0) = OFFSET(\$B\$4,{0;5;17;45;67;100;102;130;160;163;196},0)

which returns an "array of ranges" (although each range is actually only a single cell)

N function is used to "de-reference" those ranges and give you just the numeric values

5. ## Re: LINESTIF - Can you help me create an array formula to perform this function without VB

Brilliant, thank you! I was actually surprised to find that I was able to make the named range including the offset portion. I made named ranges called "ys" and "xs". Here was the code I used for "ys":
=N(OFFSET(\$C\$4,SMALL(IF(\$A\$4:\$A\$200="A",ROW(\$A\$4:\$A\$200)-ROW(\$A\$4)),ROW(INDIRECT("1:"&COUNTIF(\$A\$4:\$A\$200,"A")))),0))

This named range cannot be used as is for a data validation list, but it worked just fine for the linest formula: =linest(ys,xs,true,true)

Very grateful,
Jon

6. ## Re: LINESTIF - Can you help me create an array formula to perform this function without VB

Brilliant, thank you! I was actually surprised to find that I was able to make the named range including the offset portion. I made named ranges called "ys" and "xs". Here was the code I used for "ys":
=N(OFFSET(\$C\$4,SMALL(IF(\$A\$4:\$A\$200="A",ROW(\$A\$4:\$A\$200)-ROW(\$A\$4)),ROW(INDIRECT("1:"&COUNTIF(\$A\$4:\$A\$200,"A")))),0))

I had never entered an array formula as a named range before, and I wasn't sure how to do the equivalent of "ctrl-shift-enter" in the Name Manager, but it turns out not to be necessary.

This named range cannot be used as is for a data validation list, but it worked just fine for the linest formula: =linest(ys,xs,true,true)

Very grateful,
Jon

There are currently 1 users browsing this thread. (0 members and 1 guests)