Reputation: 2962

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

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.

Upvotes: 3

Answers (4)

Reputation: 3322

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.

Upvotes: 2

Reputation: 27338

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})

Upvotes: 3

Reputation: 152585

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})

Upvotes: 4

Reputation: 50007

Using INDEX:

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

Upvotes: 3