Getting the sum of rows.

[changeable]

if i have a Data stored in Column A with A1 = Column Label, and data are
placed in A2:A100
now, i want to get the:
average of A1:A5 and store it in A105
average of A6:A10 and store it in A106
average of A11:A15 and store it in A107 and so on.

At first i tried to record this macro by doing the following:
I tried to assign A105 with "=average(A1:A5)" and A106 with"=A6:A10"
then i select A105:A106 and drag it down. However the result is not
what i expected as the A107 will have the value "=average(A2:A6) and
not average of A11:A15. Therefore, i cant record it.

So, how can i achieve this using a macro? Thanks

 

[Bob Phillips]

Here is some code

Dim i As Long

For i = 1 To 100 Step 5
Range("A" & 105 + i \ 5).Formula = "=SUM(A" & i & ":A" & i + 4 & ")"
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim May]

Bob:
What does the "\" mean in your code -- is it sorta looks like an If Mod=0
thing?
TIA,
Jim

Here is some code

Dim i As Long

For i = 1 To 100 Step 5
Range("A" & 105 + i \ 5).Formula = "=SUM(A" & i & ":A" & i + 4 & ")"
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim May]

Never mind Bob -- I found your previous answer to this (below from within
Google)...

Message 9 in thread
From: Bob Phillips ([email protected])
Subject: Re: Strange RGB Value

View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2003-04-18 13:33:01 PST

Phobos,
FYI
r = Int(rgbValue / 65536)
can be better written as
r = rgbValue \65536
as \ is an integer division operator (saves typing<g>)
Regards

Bob