# Divide Numbers into Deciles

#### Bobaree

##### New Member
Column D contains decimal numbers which are the summation of 6 other columns. (The 6 other columns are percent rank calculations.) Does anyone know how to divide the numbers in column D into deciles and show the results in column C?

I found the information below which looks like it would do the trick, but I don't know how to incorporate it into my project. In addition to not knowing how to incorporate the code, I have reservations about using it since the results in column D are already percentranks. In other words, I'm not sure about applying the percentrank formula against those numbers that are already percentranks.

Can anyone help? I will be glad to post my current spreadsheet if that will help.

This is the information I found:
I have written a user defined function that will look down a range and calculate which decilerank for a number within the range sits.<o></o>
Copy the code to a module in your spreadsheet and call it as you normally would a function.<o></o>
' This User Defined Function can be called to calculate which Decile a single cell
' falls within a larger range of cells<o></o>

Function PERCENTILE_TO_DECILE(DataRange, RefCell)<o></o>
'DECILE_RANK(The Range of data you are interested in, The data cell that you want to know the decile of)
'Declares the function that can be called in the spreadsheet cell - enter '=DECILE_RANK(A5:A50,A5)'
'to use regularly paste it to your PERSONAL.xls workbook and reference it via =PERSONAL.XLS!decile_rank(A5:A50,A5)<o></o>

'Remove quote on line below if you want to Automatically update the function when the worksheet is recalculated
'Application.Volatile True<o></o>

'Using the percentile worksheet function calculate where the 10th, 20th etc percentile of the reference range are<o></o>
DEC1 = Application.WorksheetFunction.Percentile(DataRange, 0.1)
DEC2 = Application.WorksheetFunction.Percentile(DataRange, 0.2)
DEC3 = Application.WorksheetFunction.Percentile(DataRange, 0.3)
DEC4 = Application.WorksheetFunction.Percentile(DataRange, 0.4)
DEC5 = Application.WorksheetFunction.Percentile(DataRange, 0.5)
DEC6 = Application.WorksheetFunction.Percentile(DataRange, 0.6)
DEC7 = Application.WorksheetFunction.Percentile(DataRange, 0.7)
DEC8 = Application.WorksheetFunction.Percentile(DataRange, 0.8)
DEC9 = Application.WorksheetFunction.Percentile(DataRange, 0.9)<o></o>

' Calculate the Decile rank that the reference cell value sits within<o></o>

If (RefCell <= DEC1) Then DECILE_RANK = 1
If (RefCell > DEC1) And (RefCell <= DEC2) Then DECILE_RANK = 2
If (RefCell > DEC2) And (RefCell <= DEC3) Then DECILE_RANK = 3
If (RefCell > DEC3) And (RefCell <= DEC4) Then DECILE_RANK = 4
If (RefCell > DEC4) And (RefCell <= DEC5) Then DECILE_RANK = 5
If (RefCell > DEC5) And (RefCell <= DEC6) Then DECILE_RANK = 6
If (RefCell > DEC6) And (RefCell <= DEC7) Then DECILE_RANK = 7
If (RefCell > DEC7) And (RefCell <= DEC8) Then DECILE_RANK = 8
If (RefCell > DEC8) And (RefCell <= DEC9) Then DECILE_RANK = 9
If (RefCell > DEC9) Then DECILE_RANK = 10<o></o>

'If you want to check that there is an empty cell value in the 'Ref Cell" reference cell then remove the quotes from the 6 lines below<o></o>

'ErrorSum = 0
'If Len(RefCell) = 0 Then ErrorSum = ErrorSum + 1
'For Each Cell In DataRange
'If Len(Cell) = 0 Then ErrorSum = ErrorSum + 1
'Next
'If ErrorSum > 0 Then MsgBox ("There is an empty cell in the lookup ranges for the decile function")<o></o>

End Function<o></o>

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Andrew Poulsom

##### MrExcel MVP
What do you mean by "divide the numbers in column D into deciles" exactly?

#### Bobaree

##### New Member
If column D was 100 cells from the first number to the last, I would expect 10 groups of 10 numbers each. If 200 cells, I would expect 10 groups with 20 numbers each, etc. There would be some variation since the number of cells would not normally fall into increments of 10 (i.e. 163 cells, etc.) I'm glad to see you again; Thanks for your interest.

#### Andrew Poulsom

##### MrExcel MVP
Can you post some sample data with the expected results please?

#### Bobaree

##### New Member
 Company Ticker Decile Sum Yield Payout Ratio 12m Percentile P/Book Percentile P/EPS Percentile P/Sales Percentile EV/EBITDA Percentile P/CFPS Shareholder Yield Price/Book P/EPS P/Sales EV/EBITDA P/CFPS Shrhld Yld Industry AbbVie Inc ABBV 1.084 3 61.8 0.021 0.324 0.132 0.172 0.132 0.303 16.88 21.3 4.57 14.6 250.9 2.7 0803 - Biotechnology & Drugs Agilent Technologies Inc. A 1.347 0.9 24 0.405 0.081 0.314 0.203 0.102 0.242 3.41 28.1 2.79 14.1 288.2 2.4 1024 - Electronic Instruments & Controls Allstate Corporation, The ALL 4.667 1.8 20.5 0.889 0.899 0.859 0.738 0.384 0.898 1.26 12 0.76 7.8 117.7 9.1 0715 - Insurance (Property & Casualty) Aon PLC AON 2.214 1.2 18.8 0.415 0.293 0.455 0.081 0.263 0.707 3.36 21.6 2.18 17.6 173.4 6 0712 - Insurance (Miscellaneous) Apache Corporation APA 3.013 1 27.4 0.91 0.233 0.344 0.97 0.344 0.212 1.17 22.7 2.6 4.9 134.9 2.3 0609 - Oil & Gas Operations Apple Inc. AAPL 2.619 1.9 28.5 0.213 0.627 0.223 0.627 0.051 0.878 5.04 16.4 3.42 9.6 422.2 8.4 1003 - Communications Equipment Applied Materials, Inc. AMAT 1.708 1.8 55.6 0.364 0.061 0.283 0.192 0.768 0.04 3.6 31.2 3.17 14.3 30.4 0.6 1033 - Semiconductors AT&T Inc. T 5.042 5.3 53.4 0.728 0.97 0.677 0.96 0.819 0.888 1.95 10.1 1.38 5.1 26.7 8.6 0915 - Communications Services Automatic Data Processing ADP 1.548 2.3 47.5 0.152 0.132 0.263 0.334 0.324 0.343 5.98 26.8 3.27 12.2 141.5 3.1 0909 - Business Services Baker Hughes Incorporated BHI 2.557 1 20.8 0.768 0.192 0.697 0.627 0.031 0.242 1.65 23.7 1.28 9.6 756.4 2.4 0612 - Oil Well Services & Equipment Bank of America Corp BAC 2.84 1.2 6.2 0.99 0.142 0.273 0.011 0.99 0.434 0.76 26 3.2 53 3.3 3.6 0727 - Regional Banks Bank of Nova Scotia (USA), The BNS 2.487 3.6 46.1 0.708 0.788 0.112 0.576 0.162 0.141 2.07 13.7 4.66 10.1 223.8 1.8 0727 - Regional Banks Blackstone Group L.P., The BX 3.284 5.3 52.9 0.495 0.879 0.364 0.728 0.728 0.09 2.92 12.4 2.55 8.5 35.2 1.4 0718 - Investment Services Bristol-Myers Squibb Co BMY 1.294 2.9 87.1 0.182 0.061 0.071 0.051 0.738 0.191 5.42 31.2 5.13 21.3 34.1 2.1 0803 - Biotechnology & Drugs Brookfield Asset Management In BAM 3.255 1.3 15.1 0.778 0.92 0.637 0.213 0.637 0.07 1.59 11.2 1.49 14 46.3 1.3 0718 - Investment Services Canadian Imperial Bank of Comm CM 3.236 3.9 49.4 0.607 0.809 0.213 0.708 0.314 0.585 2.49 13.4 3.58 8.8 142.9 4.6 0724 - Money Center Banks Capital One Financial Corp. COF 3.981 1.5 16.2 0.92 0.93 0.394 0.364 0.889 0.484 1.05 11.1 2.45 11.8 22.8 3.9 0727 - Regional Banks Cardinal Health Inc CAH 3.082 1.9 36.5 0.324 0.293 0.97 0.485 0.839 0.171 3.89 21.6 0.27 10.8 26.1 1.9 0803 - Biotechnology & Drugs Caterpillar Inc. CAT 3.559 2.6 40.8 0.455 0.516 0.728 0.435 0.708 0.717 3.22 17.9 1.22 11.1 37.5 6.2 0206 - Construction & Agricultural Machinery CBS Corporation CBS 2.537 1 15 0.334 0.394 0.405 0.364 0.253 0.787 3.83 20 2.41 11.8 177.9 7.4 0906 - Broadcasting & Cable TV Chesapeake Energy Corporation CHK 3.488 1.3 44.3 0.849 0.041 0.809 0.829 0.91 0.05 1.3 34.7 0.87 6.7 22.2 0.7 0609 - Oil & Gas Operations Citigroup Inc C 2.709 0.1 1.3 0.99 0.536 0.384 0.061 0.718 0.02 0.76 17.3 2.49 18 37.4 0.3 0727 - Regional Banks Cna Financial Corp CNA 4.032 2.6 66.2 0.97 0.94 0.758 0.718 0.374 0.272 0.79 10.6 1.03 8.7 123.4 2.5 0715 - Insurance (Property & Casualty) Coca-Cola Company, The KO 1.739 3 60.3 0.192 0.243 0.172 0.132 0.475 0.525 5.3 22 3.91 15.7 83.9 4.2 0506 - Beverages (Non-Alcoholic) Colgate-Palmolive Company CL 1.143 2.2 57.9 0.011 0.122 0.233 0.091 0.192 0.494 33.28 27 3.39 17.2 208.3 4 0521 - Personal & Household Products ConocoPhillips COP 4.113 3.6 37.1 0.738 0.879 0.566 0.93 0.657 0.343 1.79 12.4 1.74 5.4 44.6 3.1 0609 - Oil & Gas Operations Costco Wholesale Corporation COST 1.922 1.2 28.5 0.253 0.112 0.94 0.344 0.243 0.03 4.51 27.1 0.49 12 189.5 0.5 0963 - Retail (Specialty Non-Apparel) CVS Caremark Corporation CVS 3.224 1.4 24.3 0.627 0.435 0.889 0.384 0.142 0.747 2.42 19.4 0.7 11.6 240.1 6.5 0954 - Retail (Drugs) Delta Air Lines, Inc. DAL 3.739 0.9 1.9 0.516 0.99 0.809 0.788 0.455 0.181 2.78 3.2 0.87 7.4 86 2 1106 - Airline Discover Financial Services DFS 3.689 1.5 16.5 0.556 0.889 0.162 0.536 0.829 0.717 2.67 12.2 3.95 10.6 26.5 6.2 0703 - Consumer Financial Services Emerson Electric Co. EMR 2.71 2.7 47.2 0.273 0.506 0.546 0.516 0.213 0.656 4.18 18.1 1.84 10.7 195.8 5.1 1030 - Scientific & Technical Instruments Estee Lauder Companies Inc EL 1.235 1 25.1 0.112 0.162 0.334 0.263 0.152 0.212 7.58 24.9 2.66 13 231.2 2.3 0521 - Personal & Household Products Exxon Mobil Corporation XOM 3.96 2.8 32.9 0.657 0.859 0.778 0.879 0.091 0.696 2.34 12.5 1 6 289.7 5.9 0609 - Oil & Gas Operations Franklin Resources, Inc. BEN 2.931 0.9 12.4 0.465 0.637 0.152 0.637 0.849 0.191 3.21 15.9 4.21 9.3 25.9 2.1 0718 - Investment Services General Mills, Inc. GIS 2.306 3.1 53.4 0.223 0.465 0.556 0.122 0.112 0.828 4.96 18.7 1.81 15.9 277.4 8.1 0515 - Food Processing Halliburton Company HAL 3.285 0.9 17.1 0.314 0.384 0.526 0.566 0.576 0.919 3.92 20.2 1.87 10.3 60.2 9.4 0612 - Oil Well Services & Equipment Hess Corp. HES 5.174 1 8.8 0.889 0.849 0.667 0.92 0.92 0.929 1.26 12.6 1.43 5.8 21.7 9.9 0609 - Oil & Gas Operations Hewlett-Packard Company HPQ 4.386 1.7 27.7 0.627 0.778 0.92 0.859 0.586 0.616 2.42 13.9 0.61 6.5 59.4 4.8 1006 - Computer Hardware Home Depot, Inc., The HD 2.668 2.1 40.8 0.061 0.273 0.627 0.556 0.293 0.858 10.7 21.7 1.52 10.5 154.3 8.2 0960 - Retail (Home Improvement) Honeywell International Inc. HON 2.588 1.9 34 0.293 0.455 0.516 0.415 0.697 0.212 4.01 18.8 1.88 11.3 37.8 2.3 1030 - Scientific & Technical Instruments Huntsman Corporation HUN 3.022 1.8 43.5 0.475 0.213 0.93 0.556 0.788 0.06 3.05 23 0.58 10.5 28.3 0.9 0106 - Chemicals - Plastics and Rubbers Illinois Tool Works Inc. ITW 3.325 2.2 24.9 0.263 0.263 0.384 0.516 0.93 0.969 4.25 21.9 2.49 10.7 14.7 12.2 0415 - Auto & Truck Parts International Paper Co IP 2.749 3 66.8 0.526 0.203 0.889 0.293 0.081 0.757 2.71 23.6 0.7 12.7 294.5 6.8 0133 - Paper & Paper Products Kellogg Company K 2.83 3 36.1 0.142 0.849 0.576 0.677 0.021 0.565 6.15 12.6 1.58 9 803.8 4.4 0515 - Food Processing Kimberly Clark Corp KMB 2.274 3.1 58.8 0.081 0.425 0.495 0.374 0.223 0.676 8.86 19.5 1.93 11.7 194.2 5.4 0521 - Personal & Household Products Kohl's Corporation KSS 4.538 2.6 35.9 0.697 0.718 0.91 0.839 0.445 0.929 2.09 14.6 0.64 6.6 88.7 9.9 0951 - Retail (Department & Discount) Kraft Foods Group Inc KRFT 2.548 3.7 38.7 0.132 0.738 0.516 0.465 0.273 0.424 6.23 14.2 1.88 11 168.3 3.5 0515 - Food Processing Las Vegas Sands Corp. LVS 1.922 2.9 52.3 0.102 0.304 0.182 0.142 0.546 0.646 7.67 21.4 3.76 15 72.9 5 0912 - Casinos & Gaming Lockheed Martin Corporation LMT 2.882 3 51.5 0.051 0.506 0.708 0.677 0.415 0.525 12.85 18.1 1.24 9 95.7 4.2 0203 - Aerospace and Defense Loews Corporation L 2.325 0.6 37.3 0.96 0.152 0.748 0.071 0.304 0.09 0.84 25.6 1.11 17.7 143.5 1.4 0715 - Insurance (Property & Casualty) Macy's, Inc. M 3.871 2 25.9 0.344 0.647 0.849 0.809 0.394 0.828 3.82 15.3 0.79 7.2 116.5 8.1 0951 - Retail (Department & Discount) Magna International Inc. (USA) MGA 4.334 1.3 18.5 0.586 0.677 0.899 0.768 0.617 0.787 2.55 15.1 0.68 7.7 53 7.4 0415 - Auto & Truck Parts Manulife Financial Corporation MFC 4.033 2.8 24.1 0.809 0.95 0.768 0.92 0.465 0.121 1.47 10.5 1.02 5.8 84.5 1.7 0709 - Insurance (Life) Marathon Oil Corporation MRO 4.406 2.1 20 0.839 0.617 0.465 0.96 0.748 0.777 1.36 16.6 2.01 5.1 31 6.9 0606 - Oil & Gas - Integrated

<COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6802" width=186><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=8 width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=6 width=64><COL style="WIDTH: 170pt; mso-width-source: userset; mso-width-alt: 8301" width=227><TBODY>
</TBODY>
Let me know if you want to see the formulas. Bobaree

#### Andrew Poulsom

##### MrExcel MVP
Where's the result of the PERCENTRANK formula there?

#### Bobaree

##### New Member
Column A contains "Company". Columns G-L contain the percentranks of the 6 columns to their right. Those 6 columns to the right contain the "hard" numbers that are converted into percentranks in G-L. Then, G-L are summed in Column D. Column D contains the data that I would hope to divide up into deciles.

 Company Ticker Decile Sum Yield Payout Ratio 12m Percentile P/Book Percentile P/EPS Percentile P/Sales Percentile EV/EBITDA Percentile P/CFPS Shareholder Yield Price/Book P/EPS P/Sales EV/EBITDA P/CFPS Shrhld Yld Industry AbbVie Inc ABBV =SUM(G3:L3) 3 61.8 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M3) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N3) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O3) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P3) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q3) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R3) 16.88 21.3 4.57 14.6 250.9 2.7 0803 - Biotechnology & Drugs Agilent Technologies Inc. A =SUM(G4:L4) 0.9 24 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M4) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N4) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O4) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P4) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q4) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R4) 3.41 28.1 2.79 14.1 288.2 2.4 1024 - Electronic Instruments & Controls Allstate Corporation, The ALL =SUM(G5:L5) 1.8 20.5 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M5) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N5) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O5) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P5) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q5) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R5) 1.26 12 0.76 7.8 117.7 9.1 0715 - Insurance (Property & Casualty) Aon PLC AON =SUM(G6:L6) 1.2 18.8 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M6) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N6) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O6) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P6) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q6) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R6) 3.36 21.6 2.18 17.6 173.4 6 0712 - Insurance (Miscellaneous) Apache Corporation APA =SUM(G7:L7) 1 27.4 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M7) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N7) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O7) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P7) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q7) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R7) 1.17 22.7 2.6 4.9 134.9 2.3 0609 - Oil & Gas Operations Apple Inc. AAPL =SUM(G8:L8) 1.9 28.5 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M8) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N8) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O8) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P8) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q8) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R8) 5.04 16.4 3.42 9.6 422.2 8.4 1003 - Communications Equipment Applied Materials, Inc. AMAT =SUM(G9:L9) 1.8 55.6 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M9) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N9) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O9) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P9) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q9) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R9) 3.6 31.2 3.17 14.3 30.4 0.6 1033 - Semiconductors AT&T Inc. T =SUM(G10:L10) 5.3 53.4 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M10) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N10) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O10) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P10) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q10) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R10) 1.95 10.1 1.38 5.1 26.7 8.6 0915 - Communications Services Automatic Data Processing ADP =SUM(G11:L11) 2.3 47.5 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M11) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N11) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O11) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P11) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q11) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R11) 5.98 26.8 3.27 12.2 141.5 3.1 0909 - Business Services Baker Hughes Incorporated BHI =SUM(G12:L12) 1 20.8 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M12) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N12) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O12) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P12) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q12) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R12) 1.65 23.7 1.28 9.6 756.4 2.4 0612 - Oil Well Services & Equipment Bank of America Corp BAC =SUM(G13:L13) 1.2 6.2 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M13) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N13) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O13) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P13) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q13) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R13) 0.76 26 3.2 53 3.3 3.6 0727 - Regional Banks Bank of Nova Scotia (USA), The BNS =SUM(G14:L14) 3.6 46.1 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M14) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N14) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O14) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P14) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q14) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R14) 2.07 13.7 4.66 10.1 223.8 1.8 0727 - Regional Banks Blackstone Group L.P., The BX =SUM(G15:L15) 5.3 52.9 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M15) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N15) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O15) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P15) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q15) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R15) 2.92 12.4 2.55 8.5 35.2 1.4 0718 - Investment Services Bristol-Myers Squibb Co BMY =SUM(G16:L16) 2.9 87.1 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M16) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N16) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O16) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P16) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q16) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R16) 5.42 31.2 5.13 21.3 34.1 2.1 0803 - Biotechnology & Drugs Brookfield Asset Management In BAM =SUM(G17:L17) 1.3 15.1 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M17) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N17) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O17) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P17) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q17) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R17) 1.59 11.2 1.49 14 46.3 1.3 0718 - Investment Services Canadian Imperial Bank of Comm CM =SUM(G18:L18) 3.9 49.4 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M18) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N18) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O18) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P18) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q18) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R18) 2.49 13.4 3.58 8.8 142.9 4.6 0724 - Money Center Banks Capital One Financial Corp. COF =SUM(G19:L19) 1.5 16.2 =1-PERCENTRANK.EXC(\$M\$3:\$M\$100,M19) =1-PERCENTRANK.EXC(\$N\$3:\$N\$100,N19) =1-PERCENTRANK.EXC(\$O\$3:\$O\$100,O19) =1-PERCENTRANK.EXC(\$P\$3:\$P\$100,P19) =1-PERCENTRANK.EXC(\$Q\$3:\$Q\$100,Q19) =PERCENTRANK.EXC(\$R\$3:\$R\$100,R19) 1.05 11.1 2.45 11.8 22.8 3.9 0727 - Regional Banks

<COLGROUP><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 3364" width=184><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 804" width=44><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 950" width=52><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 1572" width=86><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 621" width=34><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 731" width=40><COL style="WIDTH: 155pt; mso-width-source: userset; mso-width-alt: 3766" width=206><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 3547" span=2 width=194><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 3657" width=200><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 3693" width=202><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 3510" width=192><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 914" width=50><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 768" width=42><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 841" width=46><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 768" width=42><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 950" width=52><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 987" width=54><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 3657" width=200><TBODY>
</TBODY>

#### Andrew Poulsom

##### MrExcel MVP
Does this return what you would expect?

=MAX(1,CEILING(PERCENTRANK(D\$2:D\$55,D2)*10,1))

#### Bobaree

##### New Member
I think it does or is getting very close. Can it be written to do the ranking to the last row rather than a set number, and can it be set so that the highest numbers go into decile 1? Thanks for your help. The formula seems so much simpler than what I was trying to work with. Bobaree

By the way, when I try to sort the decile column from high to low or low to high, the numbers don't seem to be sorting the entire spreadsheet, but rather jumping around. Can that be fixed?

Last edited:

#### Andrew Poulsom

##### MrExcel MVP
To reverse the order and use the last row:

=11-MAX(1,CEILING(PERCENTRANK(D\$2:INDEX(D:D,MATCH(9.99999999999999E+307,D:D)),D2)*10,1))

I had no problem sorting by the column containing that formula. Make sure that you select all the data.

Replies
6
Views
481

1,191,165
Messages
5,985,034
Members
439,935
Latest member
Monty238

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