Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TREND and OFFSET
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TREND and OFFSET
gavs73 wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=TREND | Excel Discussion (Misc queries) | |||
TREND help | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Using Trend | Excel Discussion (Misc queries) | |||
trend | Excel Discussion (Misc queries) |