+ Reply to Thread
Results 1 to 6 of 6

Average the bottom 95$ of values

  1. #1
    Registered User
    Join Date
    03-01-2016
    Location
    Fargo
    MS-Off Ver
    2013
    Posts
    3

    Average the bottom 95$ of values

    Hi, I am looking to create an average or trimmean formula that allows me to average the bottom 95% of my values. The values being turnaround time in days for often greater than 500 items.

    The number of values I look at from month to month changes but i want the formula to ignore the top 5% while generating the average and be able to account for the change in item count.

    To add to this I also need it to only calculate the average for those values only when it meets a criteria of "Y".

    I should also mention I would like to accomplish this without using a Pivot.

    Can anyone help with this or is this too complex for excel.
    Last edited by shauncie; 03-01-2016 at 06:26 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Average the bottom 95$ of values

    Hi, welcome to the forum

    Sounds fairly simple, I would use =AVERAGEIFS() function

    =AVERAGEIFS(data-range,Data-Range,"<="cell-with-95%-value-in-it,Criteria-Range,"Y")
    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

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Average the bottom 95$ of values

    In case you dont already have it, you can get the 95% value withthis...
    =PERCENTILE.EXC(B2:B25,0.95)

  4. #4
    Registered User
    Join Date
    03-01-2016
    Location
    Fargo
    MS-Off Ver
    2013
    Posts
    3

    Re: Average the bottom 95$ of values

    Hi, thanks for the suggestions so far.

    I do think I need to clarify that the values I'm trying to calculate on are not percentage values. They are simple numeric values of which I may have, for example, 500 of and what I am looking to do is generate an average ignoring the top 5% largest values out of the 500 in the list. So essentially I would want to ignore the 25 largest values out of the 500.

    If I understood your suggestions correctly I believe I did not make this too clear.

    Let me know if this changes anything.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Average the bottom 95$ of values

    So you are looking at the top 95% by quantity, not by value?

  6. #6
    Registered User
    Join Date
    03-01-2016
    Location
    Fargo
    MS-Off Ver
    2013
    Posts
    3

    Re: Average the bottom 95$ of values

    Sorry, replied a bit too quickly. Yes and no. I want the bottom 95% by quantity but by bottom 95% also includes value of the items being included.

    So if i had:
    10
    9
    25
    8
    3
    5
    5
    10
    2
    6

    I would for example want to leave off the largest value of 25 being the top 5% i would ignore and get the average of the remaining bottom 95%.
    Last edited by shauncie; 03-02-2016 at 03:31 PM.

+ 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. STDDev and Average of Top 10% and bottom
    By Jul Stev in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-01-2015, 06:39 AM
  2. Calculating the average(s) of top, middle and bottom quarter values
    By AndrejLav in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-19-2015, 10:30 PM
  3. drop top and bottom values before computing average
    By jimboryan in forum Excel General
    Replies: 2
    Last Post: 07-15-2014, 04:04 PM
  4. Array to Calc Bottom Average
    By Kraftyk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-10-2014, 02:48 PM
  5. need a formula to tell me the average bottom 50%
    By padcfc in forum Excel General
    Replies: 5
    Last Post: 09-16-2011, 09:40 AM
  6. Average Top Mid and Bottom
    By tforbes75 in forum Excel General
    Replies: 3
    Last Post: 09-15-2009, 04:49 PM
  7. [SOLVED] Average top 10 or bottom 10
    By LRS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-25-2005, 03:06 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