Excel AutoFill

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
mjwilliamsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bulletheadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mjwilliamsonAuthor Commented:
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
bulletheadCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

bulletheadCommented:
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
acebassCommented:
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
mjwilliamsonAuthor Commented:
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
mjwilliamsonAuthor Commented:
Took a little longer than expected.

Sorry for the delay

Thanks
Mark
0
bulletheadCommented:
Thanks again Mark
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.