Quartile/Percentile/Rank help

chaos47

New Member
I have a collection of data where the lower the value, the better. When I use Quartile/Percentile, it shows higher values as having a higher rank. Any way to reverse the order, without changing the underlying data (ie. multiply all values by -1)?

Also, I have a graph of the data showing the 1st, 2nd (median) and 3rd quartile and then a particular data set (in this case, company results) over a number of years. Any way to automatically add the Percentrank as a data label for the specific company results? For example, the value 200 puts this company at the 65th percentile. I want the data label to show 65, not 200.

Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

cornflakegirl

Well-known Member
Welcome to the board.

With PERCENTILE, if you want the "top", ie the lowest, 3%, can you not just use the 97% percentile? Eg, if you put 3% in A1, and want the answer in B1, then
=PERCENTILE(YourRange,1-A1).

I'm afraid I don't really understand the second part of your question - can you give more details?

chaos47

New Member
follow-up on part 2:
I have a collection of annual revenue figures for forty companies over five years. I summarize them into quartiles and graph the 1st, 2nd and 3rd quartile (line graph), with the x-axis as years and y-axis as revenue. I then add a separate companies annual revenue line in the graph (so yr1:800, yr2:950, yr3:750, etc). As data labels, I want to identify on the graph the year 1 point as 52nd percentile, not \$800M, etc. I could do manually, but this graph is repeated for different data points more than 100 times in my proposed report.

cornflakegirl

Well-known Member
I think there are a couple of ways to do this.

1. Get a chart add-in that lets you do custom add-ins. Can't remember who does these - think j-walk may be one. Will have a search for you. I've never used these because work don't like me downloading stuff, but lots of people here rate them.

2. Create a dummy series that you plot on a secondary x-axis. Use the secondary axis for the data labels, but don't show the actual axis. This works well. Will try to find the thread where I've discussed it before...

chaos47

New Member
CFG, Thanks for the help. The XY Labeler solved my labelling problem. Still not sure about the "reverse order" issue (lower is better), but I'll take a look through the j-walk add-ins. Like you, I'll have a tough time getting them to approve the purchase of the add-in, but it's worth a shot.

cornflakegirl

Well-known Member

What exactly is the problem with reverse order? What can't you do that you want to do?

chaos47

New Member
Data set: DSO (days sales outstanding) data from numerous companies in my industry yields the following summary data that I would like to graph

25th 65 62 61 61
50th 76 75 70 64
75th 84 80 77 74
target 54 59 58 50
rank 08 08 20 11

The problem is that the 75th percentile data (84,80,77,74) is actually the 25th percentile, when ranked appropriately. Excel ranks the target at below 25, when it's actually around the 90th percentile. I can use "values in reverse order" on the axis and dummy the legend to flip the 75/25, but that doesn't solve my problem of calculating the PERCENTRANK of the target company correctly. I could almost see using (1-PERCENTRANK) as an approximation (no one would really know), and using the XY Labeler to identify the rankings, but the data set isn't completely linear, so that's not the true answer. Probably good enough for this assignment, but if there is a complete answer (essentially, a reverse percentile function), I'd prefer to learn/use that.

cornflakegirl

Well-known Member
Chaos - I think I'm missing something. I don't understand why 1 - PERCENTRANK isn't right.

Just had a play with the dataset from the Excel help file:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD></TD><TD></TD><TD>PCR</TD><TD>1- PCR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">13</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">12</TD><TD></TD><TD style="TEXT-ALIGN: right">89%</TD><TD style="TEXT-ALIGN: right">11%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">11</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C18</TD><TD>=PERCENTRANK(\$A\$17:\$A\$26,A18)</TD></TR><TR><TD>D18</TD><TD>=1-C18</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

For 12 in reverse order - there is 1 value that is "smaller" than it and 8 "bigger", so 1/(8+1) = 0.11 - which is the answer that 1 - PERCENTRANK gives (obviously, but just worked it through fully to check my reasoning!). What am I missing?

(BTW - used the sample data because I don't really get what yours are about. Is each column a company? The target is each company's target debt days? What is the rank?)

Last edited:

chaos47

New Member
Duh, yes, 1-Percentrank will work. Thanks for talking this out with me.

FYI, each column was a year (2004-2007), target represents one particular company (not in the data set) and rank is the percentrank of the target within that data set.

In summary, "values in reverse order", 1-percentrank and XY Labeler.

I owe you. Let me know where to send the thanks.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,095
Messages
5,835,363
Members
430,352
Latest member
xvidzoro

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.

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