Macros/Formulas
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macros/Formulas

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm trying to create a macro that will average (5) numbers, ie..A1:A5 and have that number shown in B5. Then it will go down the next (5) numbers, ie..A6:A10 and have that number shown in B10. So on and so on and so on for thousands of rows.

    Thank for any assistance.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:

    Dim counter As Long
    For i = 1 To Int(ActiveSheet.UsedRange.Columns(1).Rows.Count / 5)
    counter = counter + 6
    Range("a" & counter) = Application.WorksheetFunction.Average(Range("A" & counter - 5), Range("A" & counter - 1))
    Next

    Should work, but I didn't test it, so post any problems.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 12:13, wrongful death wrote:
    I'm trying to create a macro that will average (5) numbers, ie..A1:A5 and have that number shown in B5. Then it will go down the next (5) numbers, ie..A6:A10 and have that number shown in B10. So on and so on and so on for thousands of rows.

    Thank for any assistance.
    Hi,

    Quick and easy way without a macro:
    In B5, please the following formula

    =IF(MOD(ROW(),5)<>0,"",AVERAGE(A1:A5))

    and copy down the column

    With a macro:
    ----------------------
    Sub average_five()
    Dim lastrow As Long, x As Long

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For x = 5 To lastrow Step 5
    Cells(x, 2) = "=AVERAGE(R[-4]C[-1]:RC[-1])"
    Next x

    End Sub
    ----------------------------

    HTH,
    Jay

  4. #4
    New Member wrongman's Avatar
    Join Date
    Aug 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macros/Formulas

    I learn that solution now,it seems complicated to me,but I believe it could help me finished my job with excel quickly


  5. #5
    New Member
    Join Date
    Nov 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macros/Formulas

      
    Hey this worked well. I tried doing it and got it . You too try.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com