Evaluating INDIRECT in an array in Excel

Question

I am trying to write a linear regression function that dynamically refers to columns, can process values #N/Aand will function as additional rows are added over time. Here is an example dataset:

Date        Value 1 Value 2
1/2/1991    #N/A    #N/A
2/4/2002    276.36  346.31
1/7/2003    252     350
1/21/2004   232     345.5
1/6/2005    257     368
2/1/2006    278.24  390.11
2/23/2007   #N/A    380.46
2/11/2008   326.34  383.04
2/12/2009   #N/A    399.9
2/17/2009   334.39  #N/A
1/29/2010   344.24  400.83
1/27/2011   342.88  404.52
2/7/2012    379     417.91
1/23/2013   #N/A    433.35

Here is the function that I have developed so far, based on this forum post . It calculates linear regression for a value of 1.

=TRANSPOSE(
  LINEST(
    N(
      OFFSET(
        INDIRECT("B2" & ":B" & COUNTA(B:B)),
          SMALL(
            IF(
              ISNUMBER(
                INDIRECT("A2:A" & COUNTA($A:$A)) *
                INDIRECT("B2" & ":B" & COUNTA(B:B))),
              ROW(INDIRECT("B2:B" & COUNTA(B:B))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
              COUNT(INDIRECT("B2:B" & COUNTA(B:B))))))), 0, 1)),
    N(
      OFFSET(
        INDIRECT("A2:A" & COUNTA($A:$A)),
          SMALL(
            IF(
              ISNUMBER(
                INDIRECT("A2:A" & COUNTA($A:$A)) *
                INDIRECT("B2:B" & COUNTA(B:B))),
              ROW(INDIRECT("B2:B" & COUNTA(B:B))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
              COUNT(INDIRECT("B2:B" & COUNTA(B:B))))))), 0, 1)),
    TRUE, FALSE))

, , 2 . INDIRECT B C. 40 , , ADDRESS, ROW COLUMN:

=TRANSPOSE(
  LINEST(
    N(
      OFFSET(
        INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2))),
        SMALL(
          IF(
            ISNUMBER(
              INDIRECT("A2:A" & COUNTA($A:$A)) *
              INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))),
            ROW(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))) - ROW(B2)),
          ROW(INDIRECT("1:" & MIN(
            COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
            COUNT(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))))))), 0, 1)),
    N(
      OFFSET(
        INDIRECT("A2:A" & COUNTA($A:$A)),
          SMALL(
            IF(
              ISNUMBER(
                INDIRECT("A2:A" & COUNTA($A:$A)) *
                INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))),
              ROW(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
              COUNT(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))))))), 0, 1)),
    TRUE, FALSE))

#REF!. , , , Excel COLUMN. , INDIRECT. :

:

INDIRECT("B2:B15")

:

INDIRECT({"$B$2:$B$15"})

#VALUE, . Excel , ?

Answer 1

SLOPE ? , SLOPE #N/A ( IFERROR ). SLOPE . , INTERCEPT SLOPE.

- ( CTRL + SHIFT + ENTER) . , (-dyanmic) :

=SLOPE(IFERROR(B2:B15,""),$A$2:$A$15)

, , INDEX COUNTA .

=SLOPE(IFERROR(B2:INDEX(B:B,COUNTA(B:B)),""),$A$2:INDEX($A:$A,COUNTA($A:$A)))

Table

, Table, , . .

- , , Table1. , COUNTA, .

=SLOPE(IFERROR(INDEX(Table1,,MATCH(M1,Table1[#Headers])),""),Table1[Date])

Answer 2

, , , ::

=LINEST(INDEX(B:B,N(IF(1,MODE.MULT(IF(ISNUMBER(B2:B15),{1,1}*ROW(B2:B15)))))),INDEX($A:$A,N(IF(1,MODE.MULT(IF(ISNUMBER(B2:B15),{1,1}*ROW(B2:B15)))))))

B2: B15 , , Jeeped .

Answer 3

INDIRECT function; , . , INDEX/MATCH.

=TRANSPOSE(
  LINEST(
    N(
      OFFSET(B2:INDEX(B:B, MATCH(1E+99,$A:$A )),
          SMALL(
            IF(
              ISNUMBER(
                $A2:INDEX($A:$A, MATCH(1E+99,$A:$A )) *
                B2:INDEX(B:B, MATCH(1E+99,$A:$A ))),
              ROW(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT($A2:INDEX($A:$A, MATCH(1E+99,$A:$A ))),
              COUNT(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))))))), 0, 1)),
    N(
      OFFSET(
        $A2:INDEX($A:$A, MATCH(1E+99,$A:$A )),
          SMALL(
            IF(
              ISNUMBER(
                $A2:INDEX($A:$A, MATCH(1E+99,$A:$A )) *
                B2:INDEX(B:B, MATCH(1E+99,$A:$A ))),
              ROW(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT($A2:INDEX($A:$A, MATCH(1E+99,$A:$A ))),
              COUNT(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))))))), 0, 1)),
    TRUE, FALSE))

, A , B C, D ..

Further replacement of the function may probably replace at least some functions of the OFFSET function for the corresponding INDEX function, but this formula seems to work well, as it is now.