1. C

    creating percentile bins at weekly level for daily data

    hello BI experts, have started to play around with power pivot and power BI over the last few days. Particular case here is to create bins (measure or column, not sure!) that reflect the weekly deciles for data which is given at a daily level. There is a lot of literature written on percentilex...
  2. B

    average ifs/ percentile

    all having some issues with average ifs logic. my formula of "average if" / "percentile" works fine {=AVERAGE(IF('Data sample'!$E$11:$E$211>PERCENTILE('Data sample'!$E$11:$E$211,0.9),'Data sample'!$E$11:$E$211))} HOWEVER, when i try to add "averageifs", i cant seem to make it work...
  3. D

    Averageifs on Duplicate Columns

    I have been searching around the boards without any luck. I am trying to find an average of a series of values based on column headers F4:EU4 where the rows names in A5:A37 are unique. I have tried =averageifs(index($F$5:$EU$37,,match("Mutual Fund Name",$A5:$A37)),$F$4:$EU$4,"Peer Group...
  4. C

    Return Percentile instead of Percentrank?

    I have a columns of scores and I want to return the percentile of the score within the column. For example, of the universe of 281 qualifying players, Cody Bellinger has the highest wOBA, at .555. He is definitely in the 99th percentile of qualifying players. Using the PERCENTILE function...
  5. A

    Excel - vary content of table based on filter for table?

    Hi - I have a table: <style type="text/css"><!--br {mso-data-placement:same-cell;}--></style> <colgroup><col style="width: 60px"><col width="111"><col width="102"><col width="102"><col width="102"><col width="102"><col width="102"><col width="102"><col width="102"><col width="102"><col...
  6. D

    Help with Averageif and Percentile

    Bare with me on explaning this. Trying to figure out how to combine averageif and percentile with certain criteria. Id like to average the numbers only in the 90th percentile, for that certain name, based on a names column, where the same names occur more then once. Ive tried...
  7. E

    75th percentile of filtered data

    I have a set of data in an excel spreadsheet that I have filtered. I would like to find the 75th percentile of some of the data, but I do not want to pickup the “hidden/filtered” rows in the data set.Does anyone know a formula that circumvents this? Thank you!
  8. A

    PERCENTILE with IF condition?

    I am trying to calculate percentiles using an if clause. For exI. want to calculate the 5th percentile weight for all of the women age 19. I am not sure how to write this formula out, also not sure what ctrl shift enter does for arrays? any help appreciated
  9. D

    How to get percentile of a given #'s between two other #'s

    Can't figure out how to use PERCENTILE (or any other Excel function) to get a percent figure that represents just where between two defined #'s a 3rd # falls. For example, if: A1 = 5.2 A2 = 5.9 And I want Excel to tell me what where, in percentile terms, the number 5.3 falls between 5.2 and...
  10. B

    percentiles on cells with numbers above x

    I have the below coloumns 'A' 'B' 1 10 2 17 3 20 4 25 5 24 6 70 7 80 8 100 9 30 10 40 I want to find the 30% percentile of column A, but only on the rows where column 'B' is above 40. Help much appreciated. thanks
  11. S

    Seniority Progression Calculator

    Hello MrExcel world! I am new to the forum. I wanted to get some advice on a project I am working on. The intent is to calculate current seniority percentile and then also forecast percentile change based on known retirement date. The data will look something like this: EmpId - Hire Date -...
  12. Z

    I have to be overthinking this percentile formula

    It's not that complicated. I want to have a field a user can put a number, and my formula calculate the percentile of that input based on a minimum and maximum range, everything in the middle included obviously. Ex. User puts in 543 Min range is 298 Max is 1094 What is the percentile within...
  13. F

    Aggregate with OR argument within

    Hello everyone, What is working: 25th Percentile for 1 criteria -AGGREGATE(18,6,$C$1:$C$10/($B$1:$B$10="Green"),0.25) 25th Percentile for 2 criteria - AGGREGATE(18,6,$C$1:$C$10/(($B$1:$B$10="Green")*($A$1:$A$10="January")),0.25) What I am trying to do: Select January or March for the month...
  14. F

    Percentile 80 in Dax (Excel 2013 Version)

    I have this sort of table below and I'd like to calculate percentile 80 to "qtd/cons" for each category in "Col1" as a calculated column. Col1 qtd cons qtd/cons <tbody style="border: 0px...
  15. M

    give to the top 30% 3 point, to the midle 30% 2 points and to the lowest 40% 1 point while ignoring Zeros in the account

    I have a table of about 435 rows (and growing a bit). I have values in the column AJ and some are "0" which affect the percentile (even as nested...with the {}) This is my best guess, but as said, the percentile is still not correct, because of the "zeros"...
  16. J

    90th percentile with conditions

    Hi, I need to find out the 90th percentile of a set of times but with conditions. I have rows with data that need to be considered in the percentile. The first column has the incident numbers (there are duplicate values because there are different staff showing up at different times for the same...
  17. A

    Formula that searches for item name and calculates 75th percentile of price for all quantity?

    Hello, I am trying to pull data from another large spreadsheet (thousands of rows) which is currently sorted to look something like this; A === B === C === D Item === Quantity === Price === Running Total Qty. Apple === 50 === $2.00 === 50 Apple === 100 === $2.25 === 150 Apple === 75 ===...
  18. A

    Formula that searches for item name and calculates 75th percentile of another variable?

    Hello, I am trying to pull data from another large spreadsheet (thousands of rows) which is currently sorted to look something like this; A === B === C === D Item === Quantity === Price === Running Total Qty. Apple === 50 === $2.00 === 50 Apple === 100 === $2.25 === 150 Apple === 75 === $2.75...
  19. rjplante

    Calculate Percentile on Filtered Data

    I have a large table with many columns. I would like to filter the table first using VBA and then calculate the percentile of a single column using VBA to store the data in a different workbook. I have cobbled together how I think it should work using other sections of my code, I just need help...
  20. P


    Hey all Can we do something like percentileifs the way we do it with averageifs? Like do the percentile to only cells which a certain condition is met in another column. Thank you

Some videos you may like

This Week's Hot Topics