+ Reply to Thread
Results 1 to 4 of 4

Averaging last 6 numbers of a set of numbers

  1. #1
    Registered User
    Join Date
    06-09-2014
    Posts
    6

    Averaging last 6 numbers of a set of numbers

    Hello all

    Infrequent visitor but you guys have helped me immensely in the past so I am ever hopeful. I have a set of numbers below which will keep being added to one number at a time. I need to set up a formula so it constantly updates the overall average of the last 6 numbers every time I update. For instance the average of the last 6 of the following numbers would be 12/6 = 2

    Home Goals (FT)
    0
    1
    2
    2
    2
    1
    2
    0
    3
    3

    But what is the formula so it constantly updates the last 6 numbers average clever people every time I add one?

    Many thanks for your help,,,as always it is much appreciated.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Averaging last 6 numbers of a set of numbers

    With your range beginning in Cell A1 downwards use

    =AVERAGE(INDEX(A:A,COUNT(A:A)-5):INDEX(A:A,COUNT(A:A)))

    Adjust ranges accordingly

    Cells have to be contiguous and have numbers though
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    06-09-2014
    Posts
    6

    Re: Averaging last 6 numbers of a set of numbers

    Great thanks...works great. Thanks for the very swift response. I love this place !

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: Averaging last 6 numbers of a set of numbers

    Hi GoalsNerd,

    How about having your own UDF (User Defined Function) for the problem. Find it attached. It looks like:
    Please Login or Register  to view this content.
    Test it a few times as your example <> 12.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Averaging numbers that are 12 rows apart
    By GeographyLucy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2013, 05:57 AM
  2. Averaging Numbers
    By fearonc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-23-2013, 01:41 PM
  3. Averaging Numbers
    By fearonc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-03-2013, 04:09 PM
  4. Conditionally averaging a set of numbers
    By MMaher in forum Excel General
    Replies: 0
    Last Post: 04-02-2010, 02:45 PM
  5. Averaging only some numbers
    By tantank79 in forum Excel General
    Replies: 2
    Last Post: 12-23-2008, 05:35 PM
  6. Averaging numbers
    By ShannonE in forum Excel General
    Replies: 2
    Last Post: 10-01-2008, 05:18 PM
  7. Averaging different numbers of cells
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 AM
  8. Averaging Numbers when 2 numbers in one cell
    By Ourania in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-12-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1