Fri 5 Mar 2010, 16:46Delegate Pete said...
Pete has attended:
Access Intermediate course
Excel Advanced course
Skipping cells in autoflled formulae
Hi,
Imagine, in cells A1:A24
I have numbers 1 to 20.
Now in B1:B5 I have want to have a formula that sums A1:A4, A5:A8, A9:A12, A13:A16, A17:A20, A21:A24.
If I put '=SUM(A1:A4)' in cell B1, and drag the formula down, or copy & paste, it won't work for obvious reasons.
How should I re-write that SUM formula, so it knows to 'slip' down 4 cells, rather than 1?
I need this so regularly, and for laaaarge volumes of data, !
with best wishes,
Pete
Mon 8 Mar 2010, 11:36Trainer Anthony said...
RE: Skipping cells in autoflled formulae
Hi Pete, thanks for your query. Make sure your data is in Column A and put this formula in cell B1 and autofill down:
=SUM(OFFSET(A$1, ((ROWS(A$1:A1)-1)*4),0,4))
Let me know that this works OK.
All the best,
Anthony
