asked on

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

ASKER CERTIFIED SOLUTION

bullethead

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mjwilliamson

ASKER

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!

bullethead

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!

bullethead

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.

acebass

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

mjwilliamson

ASKER

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

mjwilliamson

ASKER

Took a little longer than expected.

Sorry for the delay

Thanks
Mark

bullethead

Thanks again Mark