Results 1 to 3 of 3

Thread: Average (2000)

  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average (2000)

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39> I want to average a series of numbers but I only want to average 5 numbers up in the column. When I add another number, I only want the previous 4 numbers plus the new number I am adding in. How would I do this????

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000)

    Assuming your column of numbers starts in A1 and has no blanks except after the last number, you can use this formula to average the last five numbers in the column:

    =AVERAGE(OFFSET($A$1,COUNT($A:$A)-5,0,5,1))

    HTH

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Location
    Anchorage, Alaska, USA
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000)

    One of the neat thing about Excel (and probably its competitors) is the relative referencing for equations makes this more or less simple ...

    Assuming you have your numbers starting in Row 1, Column A, and continuing down that column. If you type in =average(a1:a5) into Row 5, Column B, and then copy that formula into Row 6, Column B, Row 7, etc. you'll get what you need --- a running-average of five the "current" value plus the previous 4.

    This "solution" has the advantage that you can have missing values ... which might be important say in an application were the values are daily readings of something ... that might have a missing value sometimes ... the result will (as long at the corresponding value in Column A is blank) be an average of fewer than 5 values (which may or may not be what you want).

    Hope this helps.

Posting Permissions

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