How to ignore columns using offset formula? (Moving reference cell)

  • 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

  • Re: How to ignore columns using offset formula? (Moving reference cell)


    So is your offset going to the right or the left? You said it counts back, which to me reads as in going left. But then you mention reaching columns AC which would be going right from R5.


    Does C21 hold negative or positive value?


    If you are trying to go right and skip columns AC:AH then you can use this formula...


    =AVERAGE(OFFSET(R5,1,IF($C$21 > 9,$C$21+7,$C$21+1),1,3))


    If your offset needs to offset to the left, then you will never reach AC.

    One idiot throws a stone in the well, and it takes a hundred wise men to get it out.