Quartile/Percentile/Rank help

[chaos47]

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.

 

[cornflakegirl]

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]

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]

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

 

[cornflakegirl]

Thread is http://www.mrexcel.com/forum/showthread.php?t=309527&highlight=secondary+axis

It mentions the add-ins and the non add-in method.

 

[chaos47]

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]

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

 

[chaos47]

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.

Thanks for all your help.

 

[cornflakegirl]

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?)

 

[chaos47]

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.