Shift range of cells to calculate formula by fixed number
How can I shift the range of cells I want to calculate an average from?
For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10)
And next I want to get the average of cells A11 to A20, so
=AVERAGE(A11:A20)
But I don't want to manually change the data or delete rows all the time.
Is it possible to do something like:
=average(A1+10:A10+10) ??
Thanks a lot.
Last edited by prody; 03-10-2009 at 11:47 PM. Reason: Wrong number, grammar
Well, there are tricks you can use referring to ROWS and or COLUMNS. Tell me what cell holds your first formula and then what cell will hold the next one and we can show you some methods to adjust range values as you copy the cell from one place to another.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
B1: =average(A1:A10)
B2: =average(A11:A20)
Thx
Put this formula in B1 and copy down:
=AVERAGE(INDIRECT("A"&ROW()*10-9&":A"&ROW()*10))
Thanks a lot JBeaucaire, I understand how it works. Never heard of this indirect before.Originally Posted by JBeaucaire
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks