+ Reply to Thread
Results 1 to 1 of 1

Average n top values with data chosen by mutliple criteria - Google sheets

  1. #1
    Registered User
    Join Date
    04-03-2012
    Location
    Columbia, SC
    MS-Off Ver
    Excel 2010
    Posts
    1

    Average n top values with data chosen by mutliple criteria - Google sheets

    I have a data which looks something like this (given below). This sheet is evergrowing with data added on a weekly basis.

    A B C D
    Name Stat1 Stat2 Data
    Cint 0 2 234
    Roger 3 5 262
    Flash 3 4 190
    Roger 3 5 272
    Zach 2 3 269
    Zach 2 5 272

    I have another sheet with all the names and criteria for stat 1 and stat 2 in a cell lets say stat 1 = A1 and Stat 2 = B1

    I want to write a formula that will look at stat1 and stat 2 criteria for each name and bring in the average of top 5 values.


    The closest I have come so far skips the first line in the filter. So always my top value gets ignored.


    =daverage(sortn(filter('Sheet2'!A1:D1000,('Sheet!A1:A1000=A5),('Sheet2'!B1:B1000<=A1),('Sheet2'!C1:C1000>=B1)),5,0,4,false),4,Z1:Z2)

    A5 = Name to be looked for
    A1 = stat 1
    B1 = Stat 2

    Z1:Z2 - I could not figure out how to use the criteria in daverage but choosing any random value for criteria (blank cells)

    With this formula for example name Roger has top values 300, 290, 280, 270, 260 for which the average is 280. I am getting average as 275 as it skips the first number 300 (probably thinks that it is a header)

    What am I doing wrong. Any help is really appreciated.
    Last edited by 6StringJazzer; 01-28-2020 at 09:25 PM. Reason: Moved from Excel Formulas & Functions

+ 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. Sheets - How to average the top 25% of values based on criteria
    By BuyMeVinyl in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 07-20-2019, 08:22 AM
  2. Replies: 4
    Last Post: 04-06-2017, 03:42 PM
  3. 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
  4. Replies: 0
    Last Post: 12-03-2014, 05:10 PM
  5. Average weekday revenue (Google Sheets)
    By djarcadian in forum Excel General
    Replies: 5
    Last Post: 08-07-2014, 05:00 PM
  6. Average of values across 5 sheets with two criteria.
    By imranrasool in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2014, 08:34 AM
  7. adding values to chosen cells from different sheets
    By danielgp in forum Excel General
    Replies: 2
    Last Post: 01-30-2013, 07:59 AM

Tags for this Thread

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