Divide Numbers into Deciles

[Bobaree]

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>

 

[Andrew Poulsom]

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

 

[Bobaree]

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]

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

 

[Bobaree]

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]

Where's the result of the PERCENTRANK formula there?

 

[Bobaree]

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]

Does this return what you would expect?

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

 

[Bobaree]

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?

 

[Andrew Poulsom]

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.