Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2009
    Location
    The Villages, Florida, USA
    Posts
    35
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Average of recent entries

    I have a spread sheet, Excel, with golf scores. I would like to calculate the average of the ten most recent entries without varying the formula, i.e., how do I have the formula recognize the last ten entries?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Sully,

    Here's one approach.

    Assuming your dates are in A and Scores in B
    Two rows below your last score in B enter this formula:
    =AVERAGE(INDIRECT(ADDRESS(ROW()-1,2)&":"&ADDRESS(ROW()-11,2)))
    Now just remember to always place your cursor in the blank row between the last score and average and insert a new row there when you want to add another date/score pair.
    Running Average.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    An alternate to RG's formula is a User Defined Function (UDF):

    Enter in the Total Average row (B21) the formula =AverageScore()

    You can insert as many date/scores rows as you like, the code will adjust to calculate 10 most recent.

    HTH,
    Maud

    AverageGolfScore1.png

    Place in a standard module:
    Code:
    Public Function AverageScore()
    Application.Volatile
    Dim rng As Range
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Set rng = Range(Cells(LastRow - 10, 2), Cells(LastRow - 1, 2))
    AverageScore = WorksheetFunction.Round(WorksheetFunction.Average(rng), 1)
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2014-03-30 at 10:46. Reason: Change code for last 10 scores

Posting Permissions

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