+ Reply to Thread
Results 1 to 17 of 17

Averageif for the top 70,80,90% and based on a criteria

  1. #1
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Averageif for the top 70,80,90% and based on a criteria

    "Hi All,

    This has got me scratchig my head I;m trying to figure out a formula which will allow me to workout the average for the top 90,80&70% based on client

    In the example attached the client is in column A and the data to average is in B

    The forumla will have to take into consideration that the numbers wont be in order which makes it a little tricker, I have had a little mess around with the percentile function not sure if that is the right one to use.

    I hope I have explained it all properly

    Thanks "
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Averageif for the top 70,80,90% and based on a criteria

    this seems to be working:

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

    (array formula, ctrl+shift+enter)

    as in the attached.
    Attached Files Attached Files
    Last edited by simarui; 05-05-2015 at 09:13 AM. Reason: adding attachment
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito

  3. #3
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Averageif for the top 70,80,90% and based on a criteria

    Hi simarui,

    Thanks for having a look at this, it seems to work until I tranfer the formula I have put it in as an array but still doent work Im not sure if its any ideas

  4. #4
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Averageif for the top 70,80,90% and based on a criteria

    what do you mean "transfer the formula?" a few of those references are relative so that they will look at the specific % and user based on the table you uploaded. that formula is looking for client names in row 1 and a statement ending in the characters "90%" in column G, beginning in row 2. if any of those aspects has changed this formula will not work as desired.

  5. #5
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Averageif for the top 70,80,90% and based on a criteria

    Hi Simarui,

    I think its the PERCENTILE.INC function, this isnt coming up on mine as a formula, the only onces are =Percentile & =Percentrank do you think this will be the problem?

    I had to move the formula to another sheet as I couldnt upload real data.

    Thanks

  6. #6
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Averageif for the top 70,80,90% and based on a criteria

    i had to look it up to be sure, but it looks like percentile() and percentile.inc() are the same thing. percentile.exc() was added to excel 2010 and .inc is basically just there to specify "not exc..."
    http://www.real-statistics.com/descr...unction-excel/

    so yea just change those to percentile... when you say "move the formula to another sheet" is the data in that other sheet as well? this formula was written assuming your data and the formula are in the same sheet. if this is not the case you'll need to adjust the data references to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Where SheetName is the name of the sheet housing the data. also note, if the sheet with data has a space in it, like "Sheet Name", then you need to add a single quote to each end... 'Sheet Name'!$B$2:$B$50

  7. #7
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Averageif for the top 70,80,90% and based on a criteria

    Hi Simarui,

    I dont think I exaplined it properly, if you look at the orignal spreadsheet you sent me, for client 1 I was excepting to see 70% = 27.57 80%= 28.5 90% =29.83.

    I got the above figures by the below workings based only on client 1

    20 Data points top 70% = 14 data points the average of the smallest 14 data points is 27.57 80% = 16 data points so on so fourth, I hope this will of clarified a few things

    Thanks

  8. #8
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Averageif for the top 70,80,90% and based on a criteria

    oh... sounds like there were two points of confusion.

    so you want 70% of the data points, not the data points representing the 70th percentile...? i.e. if there are 10 points you want the bottom 7, not "all data points which fall above the value representing the top 30% threshold..."'

    that comment may seem confusing, but the percentile() formula is looking at the results and making a statistical determination which says "given your dataset, i expect X% of any and all results to fall below value Y". So for Client 1 for example, the 90th percentile is 43.1 - values falling above 43.1 represent the top 10% of expected results for all future trials for this client... i was averaging the top 10%. it sounds like what you actually want has nothing to do with "percentile" and also you really want to know the average of the lowest X% of values.

    i'm confusing myself further as i try to type this out...

    anyway, i think this is what you really wanted:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Averageif for the top 70,80,90% and based on a criteria

    H2=SUMPRODUCT(LARGE(($A$2:$A$50=H$1)*($B$2:$B$50),ROW(INDIRECT("1:"&INT(COUNTIF($A$2:$A$50,H$1)*$G2)))))/INT(COUNTIF($A$2:$A$50,H$1)*$G2)

    Try this in H2 and copy across
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  10. #10
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Averageif for the top 70,80,90% and based on a criteria

    Hi simarui thanks works a treat,

    Also thanks nflsales for the above formula, will give that one ago as well.

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Averageif for the top 70,80,90% and based on a criteria

    =SUMPRODUCT(LARGE(($A$2:$A$50=H$1)*($B$2:$B$50),ROW(INDIRECT(CEILING(COUNTIF($A$2:$A$50,H$1)*(1-$G4),1)+1&":"&COUNTIF($A$2:$A$50,H$1)))))/INT(COUNTIF($A$2:$A$50,H$1)*$G4)
    try this

  12. #12
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Averageif for the top 70,80,90% and based on a criteria

    Hi Simarui & nflsales,

    I just have one more crietera I forgot to add which is in column C wheather or not its complex or standard, would it be possible to ammend the formula I tried but couldnt get it to work.

    Thanks

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Averageif for the top 70,80,90% and based on a criteria

    Yes it is possible will you please attach a sample file with expected result

  14. #14
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Averageif for the top 70,80,90% and based on a criteria

    Hi nflsales,

    Please find attached a new example doc with the answer I expect, some answer are duplicated the reason is that since the example data is quite a small set 70% & 80% might be the same number.

    I hope the attached help, thanks again for looking into this.
    Attached Files Attached Files

  15. #15
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Averageif for the top 70,80,90% and based on a criteria

    See the attached format in colored cells
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    11-08-2011
    Location
    Leeds
    MS-Off Ver
    Excel 2010
    Posts
    279

    Re: Averageif for the top 70,80,90% and based on a criteria

    HI nflsales,

    Thanks for the attached this works fine, but when I move it to the larger data set it nearly crashes/carshes my excel, I'm not sure I know that the sumproduct can be a slow formula to calulcate.

    Do you know another way around this, Simarui way had no calculation time. I just cant think what part of the formula I have to change to include complex his formula is below {=AVERAGEIFS($B$2:$B$50,$A$2:$A$50,H$1,$B$2:$B$50,"<="&SMALL(IF($A$2:$A$50=H$1,$B$2:$B$50),RIGHT($G2,3)*COUNTIF($A$2:$A$50,H$1)))}

    If you cant think of a way around it, I would just like to thank you for your time and help.


    Cheers

  17. #17
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Averageif for the top 70,80,90% and based on a criteria

    See the attached file
    Attached Files Attached Files

+ 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. Multiple Criteria AverageIF
    By orev2 in forum Excel General
    Replies: 1
    Last Post: 08-19-2014, 01:18 PM
  2. averageif formula with multiple criteria
    By CPitta in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-08-2013, 12:56 PM
  3. AverageIf based on different criteria
    By caseman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2013, 02:18 PM
  4. Criteria question - AverageIF (if you please)
    By djalexr in forum Excel General
    Replies: 3
    Last Post: 01-14-2011, 08:00 AM
  5. AverageIf based on another columns criteria
    By AllenMead in forum Excel General
    Replies: 7
    Last Post: 09-06-2010, 12:46 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