W Wendy Akers May 28, 2010 #1 I add a column every week to my work sheet and want the formula to always calculate the current 5 column range.
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range.
G Glenn May 28, 2010 #2 Wendy said: I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Click to expand... Add a column WHERE? What "formula"? Where is the "current 5 column range"?
Wendy said: I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Click to expand... Add a column WHERE? What "formula"? Where is the "current 5 column range"?
A Ashish Mathur May 29, 2010 #3 Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,-5,1,5)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,-5,1,5)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
