Solved

# Excel AutoFill

Posted on 2003-02-26
Medium Priority
416 Views
Hi

I have data in the following column format

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 .......128

I need to calculate sliding averages a sums of the data
in each column e.g.

1-5 6-10 11-15

I have tried using autofill after inputing 1-5 and 6-10
but it seems to pick up the next as being 7-11, 8-12 etc

any ideas

Thanks
0
Question by:mjwilliamson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4
• 3

LVL 5

Accepted Solution

bullethead earned 80 total points
ID: 8024415
Okay, if I understand you right you have a bunch of data in 128 columns, with sums at the bottom of each column.  You then need to work out a sliding average of groups of 5 of those sums.

If I'm right, then the best way I can think of would be to have a row of the starting columns for each range, i.e:

1 6 11 16 ... etc.

And then a formula that picks up the correct columns from there, i.e. if they'reon row 102 and the averages are on row 100:

=AVERAGE(INDIRECT("R100C"&A102 &":R100C"&A102+4,FALSE))

You'll have to change that to the appropriate row numbers, of course.

If i've misunderstood you please ignore this!
0

Author Comment

ID: 8024496
OK, example data!
B       C       D       E       F       G       H       I
1     2     3     4     5     6     7     8
J       K       L       M       N       O       P
9     10     11     12     13     14     15

189     132     106     114     120     98     112     111     96     102     102     93     95     72     80
185     128     136     116     123     117     117     96     93     104     88     81     71     79     75
181     143     130     114     126     112     104     104     102     85     83     76     77     65     73
188     140     122     123     125     121     118     107     95     101     86     83     84     73     69
184     145     124     121     120     110     106     108     111     110     90     89     84     76     73
185     135     125     108     105     117     105     109     110     89     104     93     94     88     81
184     138     120     114     117     110     105     114     96     111     105     90     86     71     70
180     135     126     117     109     113     115     101     109     112     89     85     94     81     81

from here i need to take 1-5, 6-10, 11-15 .....
I need to take the sum of the entire range 1-5, first
to last row.

I havetried entering the formula
as

=SUM(B6:F56), =SUM(G6:K56), =SUM(L6:P56)

and then doing autofill on all three cells.

the result is the next cell has formula

=SUM(E6:I56)

so it keeps the span of 5 columns but does not jump
to the correct starting place.

I hope this makes it clearer.

there must be a simple way of doing this in excel!
0

LVL 5

Expert Comment

ID: 8024541
If you do what I described, but put the numbers in row 58 and start at 2, i.e.:

2, 7, 12 ...etc

and change the formula to:

=SUM(INDIRECT("R1C"&A58 &":R5600C"&A58+4,FALSE))

That should work I think.

Although I agree there must be a more elegant way!
0

LVL 5

Expert Comment

ID: 8024557
It might be easier to use named ranges.  For example, if you named the first 'block' of data (B6:F56) "rng1" and the second block (G6:K56) "rng2" and so on, you could use:

=SUM(INDIRECT("rng"&COLUMN()))

and that would fill across okay.
0

LVL 1

Expert Comment

ID: 8034586
Hi,

You could try this:

Sum each column on row 62(your choice)
sum(a1:a60) sum(b1:b60)

on row 63(your choice)at column 'e'
sum(a62:e62)
copy this and paste it at every 5th column, you can then hide row 62 and Bobs your uncle.

Pete
0

Author Comment

ID: 8072653
I haven't forgotten about this question, I'm just a bit busy just now, so when i get a chance to try the
suggestions I'll get back to you guys.

Cheers
Mark
0

Author Comment

ID: 8507952
Took a little longer than expected.

Sorry for the delay

Thanks
Mark
0

LVL 5

Expert Comment

ID: 8508022
Thanks again Mark
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service â€“ as well as a whole lot more.
This month, Experts Exchangeâ€™s free Course of the Month is focused on CompTIA IT Fundamentals.