+ Reply to Thread
Results 1 to 5 of 5

Limit an array based on criteria (percentile if another variable returns true)

  1. #1
    Registered User
    Join Date
    10-20-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    3

    Limit an array based on criteria (percentile if another variable returns true)

    I'm trying to calculate a percentile, limited by a second variable.

    i.e., I want to return the 0.8 percentile value for B:B, but only calculated among those values for which A=red.

    A..........B
    red.......5
    red.......6
    red.......4
    red.......5
    blue.....8
    blue.....9
    blue.....7
    blue.....8
    blue.....9


    I imagine that there is a common way to apply a criteria to the array that's defined in the PERCENTILE function - but I could really use to be pointed in the right direction!

    Thanks in advance!
    Last edited by pajarvey; 10-20-2011 at 06:58 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Limit an array based on criteria (percentile if another variable returns true)

    Try an "array formula" like this

    =PERCENTILE(IF(A2:A10="red",B2:B10),0.8)

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-20-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Limit an array based on criteria (percentile if another variable returns true)

    Wonderful! Thank you!

  4. #4
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: Limit an array based on criteria (percentile if another variable returns true)

    same question only if i want to get the 0.8 percentile value for B:B, but only calculated among those values for which A=red and C = Yes.

    A..........B.......C
    red.......5.......Yes
    red.......6......No
    red.......4......No
    red.......5......Yes
    blue.....8.......Yes
    blue.....9.......No
    blue.....7.......Yes
    blue.....8.......No
    blue.....9......No

  5. #5
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: Limit an array based on criteria (percentile if another variable returns true)

    need a nested array

    PERCENTILE(IF(A2:A10="red",IF(C2:C10="Yes",B2:B10)),0.8)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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