How can I calculate deciles with a range of 12 000 cells in excel

0 votes
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 in Database by Edureka
• 13,640 points
147 views

1 answer to this question.

0 votes
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.
answered Mar 25 by gaurav
• 13,560 points

Related Questions In Database

0 votes
1 answer
0 votes
1 answer

how do I calculate discount with if function in excel

Notes about the release; Frameworks to aim ...READ MORE

answered Mar 25 in Database by gaurav
• 13,560 points
48 views
0 votes
0 answers

Excel - Find how many cells are in a range

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

Apr 7 in Database by Edureka
• 13,640 points
19 views
0 votes
1 answer

How can I define a composite primary key in SQL?

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

answered Feb 21 in Database by Vaani
• 7,020 points
78 views
0 votes
1 answer

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

answered Apr 1 in Database by gaurav
• 13,560 points
33 views
0 votes
1 answer

How to enter a series of numbers automatically in Excel

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

answered Apr 4 in Database by Edureka
• 13,640 points
25 views
0 votes
1 answer

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

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

answered Apr 4 in Database by gaurav
• 13,560 points
46 views
0 votes
0 answers

How to split a string of text in excel based on a given word?

I have a list of combinations of ...READ MORE

Apr 4 in Database by gaurav
• 13,560 points
49 views
0 votes
1 answer
0 votes
1 answer

how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE

answered Feb 23 in Database by gaurav
• 13,560 points
643 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP