1. L

    Getting an average of the data from a percentile.

    Hello, I would like to get the bid cost for each percentile of search volume (What is the 70th percentile bid cost for search volume? ( At 3325.8 search volume what is the bid cost?). Is there a way to use a formula for instead of doing it step by step like below. Have 5k lines of data to work...
  2. J

    Percentile IF entire column ignoring blanks and zeros

    Hello, I'm trying to calculate percentiles for different groups in column A using random value data in column B, but ignoring blanks and zeros. I've searched around, but everywhere I've seen shows ranges of data, and the formula can't quite get me what I'm looking for. And while my example only...
  3. M

    Locate the sales figure closest to the 80th and 90th percentile

    Hi Everyone, I work for a retailer and want to know how many weeks it took each store to reach 80% and 90% of their highest recorded weekly sales. To find the 80% and 90% mark for each store I used the '=percentile' function. My data is laid out like the example below. The grey side is the...
  4. X

    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...
  5. E

    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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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!
  13. 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
  14. 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...
  15. 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
  16. 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 -...
  17. 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...
  18. 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...
  19. 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...
  20. 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"...

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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