+ Reply to Thread
Results 1 to 3 of 3

Need formula for dynamic averaging of certain values and range

  1. #1
    Registered User
    Join Date
    05-23-2019
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    49

    Need formula for dynamic averaging of certain values and range

    Hi All,

    I have a dynamic list of dollar amounts where each day, a new amount gets added to the list.

    I would like to get a formula that extracts the highest dollar amount each 5 day period beginning from day 1, then average those high's together, and excluding any 5 day period that does not have a full 5 days yet.


    The result I am trying to automate based on the attached spreadsheet is:

    The high from Days 1-5=$1875
    The High from days 6-10=$2030
    Average = $1,952.50

    The High from days 11-15 would get averaged in for the total of all 3 five-day periods once those values are added to the chart.

    Thanks in advance for the help-
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Need formula for dynamic averaging of certain values and range

    If you don't mind to use helper column.
    You may use this formula in every 5 rows

    D10
    =IF(COUNT(C6:C10)=5,MAX(C6:C10),"x")

    Then use this formula for higest-everage of every 5 days.

    =AVERAGEIF(D:D,">0")

    Regards.
    Attached Files Attached Files

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Need formula for dynamic averaging of certain values and range

    Another solution without helper:
    In H10:

    Please Login or Register  to view this content.
    With data starts from row 6 to row 2000
    Quang PT

+ 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. A formula to sum unique values within a dynamic range
    By SoyBasedCheese in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2016, 06:23 AM
  2. [SOLVED] Formula to eliminate duplicates in Rank values in a dynamic range
    By bungaree in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-14-2016, 08:23 PM
  3. [SOLVED] Troubleshoot: averaging a dynamic range of cells across a row
    By chococ in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-17-2014, 02:49 PM
  4. [SOLVED] Averaging values that move within a range
    By hasanqz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2013, 07:03 PM
  5. Averaging a range with missing values
    By mdavid800 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2011, 11:51 AM
  6. Dynamic Range of Values from Formula
    By excel328 in forum Excel General
    Replies: 7
    Last Post: 07-18-2010, 08:35 PM
  7. Replies: 0
    Last Post: 08-29-2005, 07:45 AM

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