How to enter an array of non-adjacent cells as first argument in trend function?-CodePudding

I want to find the value for A3.

__|_A___
1 | 5
2 | 10
3 | blank
4 | 20

So in A3 I add this:

=trend( {A1,A2,A4}  ,  {1,2,4}  ,  {3}  )

That produces an error.

CodePudding user response：

Using INDEX:

=TREND(INDEX(A:A,{1,2,4}),{1,2,4},{3})

CodePudding user response：

You can also use CHOOSE if you want to use actual cell references:

=TREND(CHOOSE({1,2,3},A1,A2,A4),{1,2,4},{3})

CodePudding user response：

Using INDEX with the [area_num]

Formula in cell A3, where [area_num] is optional !

=TREND(INDEX((A1,A2,A4),,,{1,2,3}),{1,2,4},{3})

CodePudding user response：

This adds nothing to the previous answers, but if it was needed to have a generalized approach, this could work:

=LET( rng, A1:A4,
      targets, {3;5},
       fltr, NOT(ISBLANK( rng ) ),
       TREND( FILTER( rng, fltr ), FILTER( ROW( rng ), fltr ), targets ) )

where rng is the y knowns and targets are the expected outputs.

In any case, an interesting question with interesting answers - all of them.