Search code examples
excelexcel-formulaoffset

OFFSET function

I have the following formula in a cell: 

=IF(Q12,ROUND(SUM(Q12:Q18)/7,2),"")

Now when I drag it down I want it to calculate SUM for Range Q19:Q25 i.e it should be; 

=IF(Q19,ROUND(SUM(Q19:Q25)/7,2),"")

I believe OFFSET is the function to add in the formula but I am not sure how to do it.

Solution

  • You are correct, OFFSET will do this job nicely, but in writing your answer, I have had to make a consideration to the fact that I don't know what offset your formula is to the sum range!

    This will do the job, and if your formula isnt in row 1, you'll need the change both of the row() - 1 statements to offset the formula back to 0 (i.e. if this formula starts in row 12, change row() - 1 to row() - 12

    =IF(OFFSET($Q$12,(ROW()-1)*7,0),ROUND(SUM(OFFSET($Q$12:$Q$18,(ROW()-1)*7,0))/7,2),"")

    Having said all of that, you have a conditional (=If(Q12) and a sum pointing to the same place, shouldn't you be checking Q12 for some truth, then summing? I don't know what your data set is though, so I could be talking rubbish.

    Hope this helps!