+ Reply to Thread
Results 1 to 5 of 5

Average of Dynamic Range

  1. #1
    Registered User
    Join Date
    03-04-2021
    Location
    Indiana
    MS-Off Ver
    Google Workspace
    Posts
    3

    Average of Dynamic Range

    I need to calculate the average of that last 20 values that aren't a ".". Since the number of "." in the range can change as the data is updated, the formula need to be dynamic as it may need to evaluate over just 20 rows if there are no "." or more rows if there are.

    Here is a sample in Sheets (Since I'm not allowed to post links yet, I'm putting the Sheet link in quotes): "docs.google.com/spreadsheets/d/15ywYQSkNqMD8mUMY4iTduN32yxImxWBKhTSamQ2c6fE/edit?usp=sharing"

    Feel free to edit.

    No helper cells.

    Thank you!

  2. #2
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: Average of Dynamic Range

    There's probably better solutions but this seems to give the proper answer:

    =AVERAGE(QUERY(A1:B,"select B order by B desc limit 20"))

  3. #3
    Registered User
    Join Date
    03-04-2021
    Location
    Indiana
    MS-Off Ver
    Google Workspace
    Posts
    3

    Re: Average of Dynamic Range

    Thanks for your reply Flyboy. The query function you've used is completely new to me, so I'm research that now...looks to be quite powerful! That said, the answer provided is incorrect. It is calculating 21.5 and the answer should be 10.5.

    It seems to be sorting column B in descending order and taking the average of the largest 20 values in column B. Maybe you were intending that it sort Column A (date) in descending order and take the average of Column B. My concern with that is that the "."s would still be an issue as this would essentially be the same as what is already there.

  4. #4
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: Average of Dynamic Range

    Sorry about my first attempt. This should work with the data laid out as you have it:

    =AVERAGE(QUERY(B1:B,"select B where B is not null limit 20"))

  5. #5
    Registered User
    Join Date
    03-04-2021
    Location
    Indiana
    MS-Off Ver
    Google Workspace
    Posts
    3

    Re: Average of Dynamic Range

    Thank you, that works perfectly!

+ 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. help with average over a dynamic range
    By djc225 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-17-2019, 08:24 AM
  2. AVERAGE formula for dynamic range
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2019, 10:46 AM
  3. [SOLVED] average formula for dynamic range
    By rossg in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-01-2014, 07:41 AM
  4. Dynamic Range average
    By rjhe22 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-12-2013, 03:42 PM
  5. Excel 2007 : Dynamic range/average issue?
    By gannon_w in forum Excel General
    Replies: 5
    Last Post: 02-26-2012, 10:42 PM
  6. Dynamic range in average formula
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-09-2011, 02:08 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