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, 2022 in Database by Edureka
• 13,690 points
8,695 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, 2022 by gaurav
• 23,260 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, 2022 in Database by gaurav
• 23,260 points
690 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, 2022 in Database by Edureka
• 13,690 points
329 views
0 votes
0 answers

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

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

Aug 14, 2022 in Database by Kithuzzz
• 38,020 points
559 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, 2022 in Database by Vaani
• 7,070 points
1,353 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, 2022 in Database by gaurav
• 23,260 points
852 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, 2022 in Database by Edureka
• 13,690 points
678 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, 2022 in Database by gaurav
• 23,260 points
900 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, 2022 in Database by gaurav
• 23,260 points
11,078 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP