CODEWITHSUNDEEP
excelexcel-formulaoffset
Stupid_Intern
Stupid_Intern

Reputation: 3460

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.

Upvotes: 0

Views: 569

Answers (2)

Andy Wynn
Andy Wynn

Reputation: 1273

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!

Upvotes: 3

pnuts
pnuts

Reputation: 59495

Taking only the SUM part of your formula and assuming placed in Row1 please try: 

=SUM(OFFSET(Q$12,7*(ROW()-1),,7,1))

Upvotes: 2

Related Questions