Creating deciles

sand112281

Board Regular
Joined
Aug 6, 2004
Messages
75
What is the easiest way to create deciles in excel for ranked data. I would prefer to do it w/o having to use VBA code.

thanks

mike
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,000
Office Version
  1. 365
How would you like to decile? Equal buckets (i.e. each decile has 1,000 accounts)? Or equal value (i.e. each decile has a market value of $100MM, so that there are tons of accounts in decile 1 and a handful of decile 10 powerhouses)?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Deciles: "Rank" data into ten groups of equal frequencies.

So Sort the data. Then: count the number of data items, divide that number by 10. Next group the sorted data into another location based on the count divided by 10. Do this nine more times to get a total of ten location groups of sorted data each containing about the same number of data elements.
 

sand112281

Board Regular
Joined
Aug 6, 2004
Messages
75
i would prefer whatever fit the data the best. but since that would vary too much equal buckets would be fine.
 

jexcel3

Board Regular
Joined
Nov 24, 2007
Messages
153
I am trying to find VBA code that would create decile ranks (equal bucket style) based on the Excel's 2003 rank function. Does any one have any ideas?

j
 

jexcel3

Board Regular
Joined
Nov 24, 2007
Messages
153
Here is a prior post that i started with:
[post]281553 [/post]

Data is in col A, Excel's rank function is in col B and in col C is the rank value/divided by the max value in col B. This will make the excel rank standard. Does anyone know to automate the bin values so i can apply a similar approach to the Barry Katcher's link above?

Code:
Data	Rank	n/max
11.20	1	0.0027
3.48	2	0.0054
3.43	3	0.0081
3.39	4	0.0108
2.76	5	0.0135
2.67	6	0.0162
2.46	7	0.0189
2.43	8	0.0216
2.38	9	0.0243
2.26	10	0.0270
2.18	11	0.0296
2.02	12	0.0323
1.89	13	0.0350
1.84	14	0.0377
1.51	15	0.0404
1.38	16	0.0431
1.25	17	0.0458
1.23	18	0.0485
1.22	19	0.0512
1.21	20	0.0539
1.14	21	0.0566
1.10	22	0.0593
1.08	23	0.0620
1.05	24	0.0647
1.05	24	0.0647
1.05	26	0.0701
0.92	27	0.0728
0.90	28	0.0755
0.88	29	0.0782
0.87	30	0.0809
0.83	31	0.0836
0.76	32	0.0863
0.75	33	0.0889
0.74	34	0.0916
0.70	35	0.0943
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,588
Messages
5,637,275
Members
416,963
Latest member
samfuge

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