?
Solved

Excel AutoFill

Posted on 2003-02-26
8
Medium Priority
?
416 Views
Last Modified: 2008-02-01
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
Comment
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
8 Comments
 
LVL 5

Accepted Solution

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

by:mjwilliamson
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

by:bullethead
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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 5

Expert Comment

by:bullethead
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

by:acebass
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

by:mjwilliamson
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

by:mjwilliamson
ID: 8507952
Took a little longer than expected.

Sorry for the delay

Thanks
Mark
0
 
LVL 5

Expert Comment

by:bullethead
ID: 8508022
Thanks again Mark
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

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.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question