# percentile

1. ### Fill cell colour according to percentile.

I have an excel sheet of students with their different subject scores for their tests. Row: Student Names Columns: Subject headers and their scores. (Math, Science, Literature. Is there a excel macro that can automatically help me colour the cells of those in the top 20% green, next 20% light...
2. ### Request Assistance: Percentile w/ Multiple Conditions

Hi all, I am racking my brain with trying to figure out how to calculate percentiles based on multiple conditions. I have scoured the internet and consistently come up against a formula similar to: =PERCENTILE(IF(A:A=A2,IF(B:B=B2)).9) which apparently will give me the percentile of the widgets...
3. ### 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...
4. ### 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...
5. ### 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...
6. ### 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...
7. ### 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...
8. ### 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...
9. ### 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!
10. ### 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
11. ### 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...
12. ### 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
13. ### 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 -...
14. ### 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...
15. ### 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...
16. ### 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...
17. ### 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"...
18. ### 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...
19. ### 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 ===...
20. ### 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...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back