+ Reply to Thread
Results 1 to 10 of 10

STDDev and Average of Top 10% and bottom

  1. #1
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    STDDev and Average of Top 10% and bottom

    Hi Guys,

    Need your expertise on creating the formula in attached file, have been playing with averageif but no result due to multiple criteria , hope someone can enlighten me on this problem.

    Attached file below contain the sample and remark which makes it easier to understand rather than me explaining it here. Please feel free to ask me any question if anything unclear

    Thanks in advance for the help.

    Top and Bottom 10%.xlsb

  2. #2
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: STDDev and Average of Top 10% and bottom

    Hi,
    I have seen your attachment..... the data is not as you described.....
    You can use following formulas.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Lokesh Kumar
    Stay Hungry.. Stay Foolish..
    _________________________________________________________
    Please Click STAR to Add Reputation if my/someone's answer helped!

  3. #3
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: STDDev and Average of Top 10% and bottom

    Hi Lokesh,

    Thank you very much for spending your time on my issue, appreciate it .

    Sorry but i don't get what you mean by my data is not as i described? the original average formula return the results that i want, but i can't do it manually to every criteria.

    Do you mind to explain which part of my data is wrong? Thanks for your patience

  4. #4
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: STDDev and Average of Top 10% and bottom

    Hey,

    Tier 1 with "A" Mark have 20 members, 10% will be 2 members, the top and bottom will be determine by their amount
    sorry my mistake I thought this will also applicable for "B" "C" "D"....
    However top 10% and bottom 10% can be possible but 80% middle I am not sure.......

  5. #5
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: STDDev and Average of Top 10% and bottom

    I see..do you mind to help on the top and bottom 10% first? i hope someone else can come up with the middle 80% later on if its possible. Thankss

  6. #6
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: STDDev and Average of Top 10% and bottom

    Hi,
    Check it....Top and Bottom 10% (1).xlsb

    These formulas are CSE press (CTR+Shift+Enter) while entering into the cell.

  7. #7
    Forum Contributor
    Join Date
    06-26-2013
    Location
    Makati
    MS-Off Ver
    Excel 2010
    Posts
    138

    Re: STDDev and Average of Top 10% and bottom

    Hi Lokesh,

    I think those formula only apply to one criteria which contain 20 members, but each criteria has different member counts , especially in my big data 1 criteria can contain more than 50k members...

  8. #8
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: STDDev and Average of Top 10% and bottom

    Quote Originally Posted by Jul Stev View Post
    Hi Lokesh,

    I think those formula only apply to one criteria which contain 20 members, but each criteria has different member counts , especially in my big data 1 criteria can contain more than 50k members...
    No you check it......this will not happen.....
    Please try and if you have any issue let me know.....

  9. #9
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: STDDev and Average of Top 10% and bottom

    But remember.......
    These formulas are CSE press (CTR+Shift+Enter) while entering into the cell.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: STDDev and Average of Top 10% and bottom

    Hi there. this is rather more involved than LokeshKumar's formulas. Call this a "work-in-progress". It's OK for category A1 (i.e. I've checked it). Can you take a look at it & see if it's OK for the rest. To get it to work, the data first had to be sorted by Tier, Mark & Amount.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 05-01-2015 at 07:06 AM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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. Var & VarP, StdDev & StdDevP
    By DaveyB in forum Excel General
    Replies: 15
    Last Post: 01-01-2012, 02:44 PM
  2. 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
  3. Average Top Mid and Bottom
    By tforbes75 in forum Excel General
    Replies: 3
    Last Post: 09-15-2009, 04:49 PM
  4. Calculating StdDev combined w/ If function
    By Paul987 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2008, 03:08 PM
  5. [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