I have a column of 12,000+ numbers, both positive and negative, sorted from highest to lowest in an Excel spreadsheet.

Is there an easy way to go about dividing this range into deciles?
Mar 24, 2022 in Database 3,725 views

## 1 answer to this question.

1. Enter the following formula in cell B1: =PERCENTRANK(\$A\$1:\$A\$12000,A1,1), assuming your numbers are in cells A1 through A12000. The percent rank of the value in cell A1, reduced down to one decimal place, is calculated using the set of values in cells \$A\$1:\$A\$12000 (which is all you need to identify the decile).

2. Cells B2 through B12000 should all have the same formula as cell B1.

3. Determine the decile for the relevant value in column A using the values in column B. 0 designates values that are more than or equal to the 0th percentile but less than the 10th percentile, 0.1 designates values that are greater than or equal to the 10th percentile but less than the 20th percentile, and so on. There may or may not be a value that is assigned a PERCENTRANK of exactly 1 depending on the size of your set and whether or not there are duplicates.

If you're using Excel 2010, you might want to consider using the new PERCENTRANK.INC and PERCENTRANK.EXC functions, which are designed to replace PERCENTRANK.

I hope this information is useful.
• 22,970 points

## Excel - Find how many cells are in a range

For each cell in the column I ...READ MORE

## How can I select the first day of a month in SQL?

I only need to choose the given ...READ MORE

## How can I define a composite primary key in SQL?

A primary key is unique and it ...READ MORE

## How do I display a ratio in Excel in the format A:B?

Try this formula: =SUBSTITUTE(TEXT(A1/B1,"?/?"),"/",":") Result: A B ...READ MORE

## How to enter a series of numbers automatically in Excel

Excel, unlike other Microsoft Office programmes, does ...READ MORE

## How can we achieve excel ceiling function with significance value in SQL Server

If the underlying data types and rounding ...READ MORE