+ Reply to Thread
Results 1 to 8 of 8

Calculate Percentile Based on Two Parameters in a Table

  1. 03-25-2024, 05:20 PM #1
    mklein4902
    mklein4902 is offline
    Registered User
    Join Date
    03-25-2024
    Location
    Seattle, WA
    MS-Off Ver
    2021
    Posts
    3

    Calculate Percentile Based on Two Parameters in a Table

    Hello.

    I have a Table titled "Raw Data". There are two columns in this table, Category and Sales. I want to calculate the 25th percentile of a specific ID only where sales is >0.

    I have the following:

    =PERCENTILE.INC(IF(AND(RawData[SALES]>0,RawData[Category]=A4),RawData[t52w_ops]),H3)

    Where A4 = unique category and H3 = 0.25

    This formula consistently returns 0. If I remove the AND statement and only calculate based on category, it works fine, but includes the 0's. I cannot delete the 0 rows from my dataset. Any help is appreciated. Thanks!
    Reply With QuoteRegister To Reply
  2. 03-25-2024, 05:21 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Calculate Percentile Based on Two Parameters in a Table

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-25-2024, 05:36 PM #3
    mklein4902
    mklein4902 is offline
    Registered User
    Join Date
    03-25-2024
    Location
    Seattle, WA
    MS-Off Ver
    2021
    Posts
    3

    Re: Calculate Percentile Based on Two Parameters in a Table

    Thanks. I uploaded a sample.
    Reply With QuoteRegister To Reply
  4. 03-25-2024, 05:40 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Calculate Percentile Based on Two Parameters in a Table

    Nope - nothing attached. Try again, please, and remember to SAVE.
    Reply With QuoteRegister To Reply
  5. 03-25-2024, 05:42 PM #5
    mklein4902
    mklein4902 is offline
    Registered User
    Join Date
    03-25-2024
    Location
    Seattle, WA
    MS-Off Ver
    2021
    Posts
    3

    Re: Calculate Percentile Based on Two Parameters in a Table

    Uploading a sample, I hope!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-28-2024, 10:14 AM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Calculate Percentile Based on Two Parameters in a Table

    I added another column, without 0''s, which is populated using: =IF([@Sales]>0,[@Sales],""), to the RawData table.
    I then modified the formula in column B to read: =PERCENTILE.INC(IF(RawData[Category]=A4,RawData[without 0''s]),B$3)
    Although I cannot find documentation to support this, it seems that the PERCENTILE.INC function ignores blank cells.
    Compare the result in cell B4 on Sheet2 to cell H2 on Sheet1 which gets the percentile of cells F2:F14 which are Category1 sales amounts excluding the zeros that have been extracted from the RawData table.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  7. 03-28-2024, 11:06 AM #7
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,059

    Re: Calculate Percentile Based on Two Parameters in a Table

    Spill formula:
    =PERCENTILE(FILTER(RawData[Sales],(RawData[Sales]<>0)*(RawData[Category]=$A4)),B$3)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-28-2024, 11:36 AM #8
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Calculate Percentile Based on Two Parameters in a Table

    Another solutions.

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

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate weighted percentile
    By BWV10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2021, 05:46 PM
  2. [SOLVED] Calculate 10th percentile of a range
    By billj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-03-2016, 10:19 PM
  3. [SOLVED] Pull out col. header from table based on two input parameters to search for in the table.
    By pcrewlok in forum Excel General
    Replies: 3
    Last Post: 08-13-2015, 10:07 PM
  4. Calculate the percentile an array determined by an IF
    By penfold1992 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2015, 08:15 AM
  5. [SOLVED] Locating a row in a table of data based on specified parameters
    By Marcos Aristotelous in forum Excel General
    Replies: 4
    Last Post: 01-20-2014, 04:37 PM
  6. Excel 2007 : How to auto calculate a percentile
    By stevetothink in forum Excel General
    Replies: 5
    Last Post: 01-18-2012, 01:48 PM
  7. Macro to calculate what percentile a value is in
    By Toneranger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2011, 03:20 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1