TREND and OFFSET

[gavs73]

Hi

I essentially have a large matrix of data, where each month an additional
column of data is added, and in effect I need the TREND array formula to
shuffle along by one to include the new column and exclude what was
previously the first column of the array. So that I have a rolling 12 month
trend.

I've tried to achieve this using TREND and OFFSET, however, when I press
Ctrl + Shift + Enter to set it as an array formula, the OFFSET effect seems
to be lost as it defaults to the first value.

Can TREND and OFFSET be used in this way ?, or could I use a macro to move
along the TREND ?, can a macro be used to create and move a
'Ctrl+Shift+Enter' style array ?

Thanks in advance for any help.

Kind Regards
Gav

 

[smartin]

Hi

I essentially have a large matrix of data, where each month an additional
column of data is added, and in effect I need the TREND array formula to
shuffle along by one to include the new column and exclude what was
previously the first column of the array. So that I have a rolling 12 month
trend.

I've tried to achieve this using TREND and OFFSET, however, when I press
Ctrl + Shift + Enter to set it as an array formula, the OFFSET effect seems
to be lost as it defaults to the first value.

Can TREND and OFFSET be used in this way ?, or could I use a macro to move
along the TREND ?, can a macro be used to create and move a
'Ctrl+Shift+Enter' style array ?

Thanks in advance for any help.

Kind Regards
Gav

You have the right idea. Use OFFSET to supply moving ranges to TREND.
The "movement" is effected by changing the column offset in accordance
with the COUNT of columns with values.

This solution is sensitive to where your data is, so adjust accordingly.

I assumed you have known x values in row 2, known y values in row 3, and
a non-numeric header in column A (i.e., data begins in column B):

=TREND(OFFSET(B3,0,COUNT(3:3)-12,1,12),OFFSET(B2,0,COUNT(2:2)-12,1,12))

Note this is /not/ an array formula.