Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

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
0
mjwilliamson
Asked:
mjwilliamson
  • 4
  • 3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now