98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Skipping cells in autoflled formulae
Skipping cells in autoflled formulae
Resolved · High Priority · Version 2007
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
Mon 15 Mar 2010: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Using basic functions without doing formulasWhen you highlight figures Autocalc tells you the total in the bottom right of the screen, but if you right click on the sum it will give you some basic functions. The functions are Min, Max, Average, Sum, Count, and Count Nums. |
