+ Reply to Thread
Results 1 to 4 of 4

Identify Top/Bottom 10% using an equation

Hybrid View

  1. #1
    Registered User
    Join Date
    10-26-2017
    Location
    America
    MS-Off Ver
    2013
    Posts
    2

    Identify Top/Bottom 10% using an equation

    Hi,

    I need to be able to use an equation to identify the top/bottom 10% by month and year. I need an equation that looks at raw data and goes ok here are all the pieces for Feb FY18 of these 100 cells here are the 80 that you should count.

    Every month will have a different top/bottom 10%.

    For example, in April FY18, there are 37 lines. Rounding up i need to identify the top/bottom 4 lines.
    My goal is to be able to do a month over month pivot, averaging category 1, and filtering out the top/bottom 10%

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,450

    Re: Identify Top/Bottom 10% using an equation

    Before doing the programming part, can you explain the statistics part? What do you mean by "top/bottom 10%"? Are you looking for the 10th and 90th percentiles (can be calculated using one of the PERCENTILE.xxx() functions)? Are we looking for some "mean +/- standard deviation" kind of calculation? Something else? Tell us the statistics of how you intend to identify the top/bottom 10%, and we can help you program that into Excel.

    For the specific example given (April's data), the 10th and 90th percentile seems to capture the boundaries. PERCENTILE(A2:A38,0.1)=1.6 and PERCENTILE(A2:A38,0.9)=39.6 which correctly separates the top 4/bottom 4.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-26-2017
    Location
    America
    MS-Off Ver
    2013
    Posts
    2

    Re: Identify Top/Bottom 10% using an equation

    The top/bottom 10% of the total number of lines for the month/year.
    For example in April, there are 37 lines of data. 10% of 37 is 3.7. Sorting Category 1 from smallest to largest. I would then remove the 4 smallest and the 4 largest.

    I need to be able to have the equation go in a data set, here are all the items for this month. There are x number in this month. 10% of that number is y. Determine which are the smallest/largest and identify the y of that group

    Category 1 FY Month Count
    1 FY18 April N
    1 FY18 April Y
    2 FY18 April Y
    2 FY18 April Y
    3 FY18 April Y
    4 FY18 April Y
    10 FY18 April Y
    90 FY18 April Y
    91 FY18 April Y
    100 FY18 April N

    10 lines of data x 10% = 1 Remove the smallest/Largest by the qty 1
    So the average goes from 30.4 in Category 1 to 25.375

    I want to identify the outliers of the average by taking the total number of lines for that month x 10% and not including the smallest/largest numbers by qty monthx10%

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,450

    Re: Identify Top/Bottom 10% using an equation

    That seems like a good description of percentile, so try one of the PERCENTILE() functions and see if it works.

+ 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. Change variables within an equation without the equation forgetting the previous variable
    By GarethmMorgan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-18-2018, 01:48 PM
  2. [SOLVED] Bottom Command Button does not take the user to the bottom of the ListBox
    By JJFletcher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2017, 03:21 PM
  3. Identify top 3, bottom 3 and intermediate values using IF function
    By nataliebenjamin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2012, 03:28 PM
  4. Replies: 3
    Last Post: 03-06-2011, 01:38 PM
  5. Shared Top and Bottom Border - Bottom not printing
    By bick421 in forum Excel General
    Replies: 0
    Last Post: 01-07-2009, 03:47 PM
  6. Insert Column w/ Equation - Fill to bottom
    By mkerstei in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2006, 12:35 AM
  7. Replies: 1
    Last Post: 04-09-2006, 04:30 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