Trend() worksheet arguments

[carlslack]

Hi,

Is it possible to reference cells in non-adjacent columns
as arguments to TREND()?


Instead of:
=TREND({5,10},{2000,2060},2030)

I'd like to use:
=TREND((A7,Q7),{2000,2060},G10)

It seem like TREND will only accept contiguous cell
reference ranges (ex A7:Q7), which the data in my
worksheet is not set up for...

Thanks in advance for any help!

-CS

 

[Harlan Grove]

Is it possible to reference cells in non-adjacent columns
as arguments to TREND()?

Instead of:
=TREND({5,10},{2000,2060},2030)

I'd like to use:
=TREND((A7,Q7),{2000,2060},G10)

It seem like TREND will only accept contiguous cell
reference ranges (ex A7:Q7), which the data in my
worksheet is not set up for...

...

No. You need trickery. The simplest way to create an array of numbers from a
group of nonadjacent cells is to use something like either

N(INDIRECT({"A7","Q7"}))

or

N(OFFSET(A7,0,{0,16},1,1))

Note that the enclosing N() function call is *NECESSARY*. Note that

N(INDEX((A7,Q7),1,1,{1,2}))

doesn't work as an argument to other functions, though it'd give the same result
as the previous two formulas when entered into a 2-column by 1-row range.

 

[carlslack]

Thanks, Harlan.

After reading your reply, I think I'll just avoid
TREND() altogether...

Regards,
-C