ok, here's what I did:
Assuming, that your data is in column "B"
and you're putting the averages in column "C"
I used column "D" as a index column to give me the starting row.
2
22
42
and copied it down.
Now, you can build a text string to describe the range by:
="B"&D2&":B"&D2+19
(which results in: "B2:B21")
Next, you need to tell Excel to interpret this as a range.
to do that, you can use the INDIRECT statement:
INDIRECT("B"&D2&":B"&D2+19)
Next, plug that into your SUM equation and you get:
=SUM(INDIRECT("B"&D2&":B"&D2+19))/20
you can then copy that down.
as alternative, you can also use:
=AVERAGE(INDIRECT("B"&D2&":B"&D2+19))
If you wanted to get REALLY tricky, and didn't want to use the "index" column.
you can use the row number to calculate the first row for your set.
=ROW() gives you the row number.
if you're starting in row 2, and you want the first number to be 2
=ROW()*20 gives you 40, so you have to subtract 38
copying that to row 3 results in 3*20-38 = 22
Now the ENDING row of your averages is +19, so -38+19 = 19,
the resulting formula, without using any "helper" columns would be:
=AVERAGE(INDIRECT("B"&ROW()*20-38&":B"&ROW()*20-19))
Now, keep in mind that this all depends on starting in row 2.
if you choose to start elsewhere, the formula will change...
hope these help,
Paul
________________________________
From: Bunyano <[email protected]>
To: MS EXCEL AND VBA MACROS <[email protected]>
Sent: Thu, April 22, 2010 2:52:21 PM
Subject: $$Excel-Macros$$ Help with Autofill Increment
Hi. I am trying to find a formula to calculate the average of the
first 20 values in a column, then the average of the 20 20 values and
then the average of the third 20 values etc.
I tried to do this by simply summing the first 20 values and dividing
them by 20: sum(B2:B21)/20. This works for the first value but when I
used autofill to populate the next formula I get sum(B3:B22)/20 NOT
sum(B22:B41)/20 which is what I want.
I'm sure there must be a straightforward way of achieving this but
I've not yet been able to work it out. Please help!
Thanks in advance.
Nick
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
