+ Reply to Thread
Results 1 to 7 of 7

Google Sheets: formula for best 5 of last 10 [MOVED]

  1. #1
    Registered User
    Join Date
    08-25-2018
    Location
    wisconsin
    MS-Off Ver
    unknown
    Posts
    4

    Google Sheets: formula for best 5 of last 10 [MOVED]

    I run a golf league and am looking for a formula that will average the best 5 of the last 10 scores for a person but if I add more scores it will average it off of the best 5 of the last 10 scores in a row

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: formula for best 5 of last 10

    Take a look at the LARGE function, like this:

    =AVERAGE(LARGE(range,1),LARGE(range,2),LARGE(range,3) …

    and so on.

    Hope this helps.

    Pete

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: formula for best 5 of last 10

    Using this sample data in A1:C22
    Please Login or Register  to view this content.
    This regular formula, copied across to the right, calculates the average of the smallest 5 scores of the last 10 scores.
    If there are less than 10 scores the result is N/A
    Please Login or Register  to view this content.
    That formula is durable against missing scores.

    In that example, these are the results: 70.6 and 70.8

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    08-25-2018
    Location
    wisconsin
    MS-Off Ver
    unknown
    Posts
    4

    Re: formula for best 5 of last 10

    Ron I can't seem to view your formula

  5. #5
    Registered User
    Join Date
    08-25-2018
    Location
    wisconsin
    MS-Off Ver
    unknown
    Posts
    4

    Re: formula for best 5 of last 10

    and can I do it with a row?

  6. #6
    Registered User
    Join Date
    08-25-2018
    Location
    wisconsin
    MS-Off Ver
    unknown
    Posts
    4

    Re: formula for best 5 of last 10

    Will it work for a google drive spreadsheet?

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: formula for best 5 of last 10

    Not sure about Google docs.
    If it does work then you should be able to use it for rows.
    In A1:O3
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

+ 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. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  2. Google Sheets: Formula I can use with multi conditions
    By CullyM in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 07-12-2018, 01:06 PM
  3. Data validation and formula - Google Sheets
    By Human2014 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 8
    Last Post: 04-05-2017, 10:56 AM
  4. Converting google sheets formula to excel
    By coldeskimo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2016, 08:41 AM
  5. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  6. Google Sheets Array Formula Structure
    By redline24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2015, 04:00 PM
  7. Basic Sum Formula in Google Sheets
    By lesoies in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 08-27-2013, 04:05 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