+ Reply to Thread
Results 1 to 2 of 2

Finding the average of top and bottom 10% of a dataset

  1. #1
    Registered User
    Join Date
    06-06-2020
    Location
    Memphis, TN.
    MS-Off Ver
    Excel, Word and Powerpoint
    Posts
    1

    Exclamation Finding the average of top and bottom 10% of a dataset

    Hello,

    I have a large dataset and I am trying to find the average of the top 20% and bottom 20% for each city. I am attaching the sample data and you can see under columns F and G the formulas that I tried to use to find this however it will not work.

    The formulas I am using are below:
    For average of the top 20% for a particular city in the list =AVERAGEIFS($A:$A,$D2,$B:$B,$B:$B,LARGE($B:$B,ROW(INDIRECT("1:20"))))
    For average of the bottom 20% for a particular city in the list =AVERAGEIFS($A:$A,$D2,$B:$B,$B:$B,SMALL($B:$B,ROW(INDIRECT("1:20"))))

    When I run the formulas like this by itself then I get a #SPILL error message, and when I do Ctrl+Shift+Enter then I get a #VALUE error message.

    Any light you can shed on how to do this correctly is greatly appreciated!!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,899

    Re: Finding the average of top and bottom 10% of a dataset

    almost there. 20% (in this case) is top 3.2 values (15*.2)
    so maybe this?
    =AVERAGEIF($B$2:$B$16,">="&LARGE($B$2:$B$16,COUNT(B:B)*0.2))
    change LARGE to SMALL for the bottom range
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Calculate average sum from a dataset?
    By QuantEdge in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-02-2017, 04:24 PM
  2. Finding the last value recorded in a dataset
    By rburkholder in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-26-2017, 09:46 AM
  3. Getting an average when dataset contains < or > in
    By barneyrubble1965 in forum Excel General
    Replies: 2
    Last Post: 11-26-2014, 11:12 AM
  4. Replies: 5
    Last Post: 10-29-2014, 08:11 AM
  5. How to calculate average in a large dataset
    By dolle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-27-2013, 01:08 PM
  6. [SOLVED] Finding ALL CAPS in large dataset
    By renee705 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2012, 02:35 PM
  7. Finding first and bottomlast years in large dataset
    By laurafv in forum Excel General
    Replies: 2
    Last Post: 02-28-2011, 09:55 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