Hi all,
I am looking to ignore columns when using offset formula.
The formula i am using is =AVERAGE(OFFSET(R5,1,-($C$21+1)*1,1,3))
Cell C21 has a number which specifies how many columns to count back from the original reference cell of R5. Then I am looking to average 3 cells from the new reference point.
My issue is that as the columns progress (R, S, T, U, V etc etc) there are some that I want to ignore in all calculations. So when i reach column AC, I want to ignore the next 6 columns (AC:AH), and when the original reference column is then to the right of those cells (AI, AJ, AK etc etc), count back C21 ignoring those 6.
So to use the example above - if the original reference cell is in AK5, and cell C21 specifies a count-back on columns of 8, I want the final reference cell to be W5, which would exclude columns AC:AH.
I just don't know how to tell offset formula to ignore columns.
Any help would be appreciated.
Thanks