How do you calculate the Quintile for groups of rows in Excel

0 votes

I found a partial answer to this question under How do you calculate the Quintile for every row in Excel?. I would like to derive the same quintile data for each row but I need the quintiles to be based on groups that are determined by a value in another column.

Oct 16 in Others by Kithuzzz
• 20,660 points
37 views

1 answer to this question.

0 votes

Use this formula:

=MAX(1,ROUNDUP(10*PERCENTRANK($C:$C,$C2,4),0))

To divide into whichever many groups you need, change the value 10; it now creates decile groupings. Change $C2 to the cell in the first row with the target value and autofill down, then change $C:$C to the column with the target range of values.

Your result will be a division of the population into groups, with 1 being the lowest value and everything higher representing a higher value.

answered Oct 17 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

How do I determine the size of my array in C?

How do I determine the size of ...READ MORE

May 1 in Others by Kichu
• 19,040 points
51 views
0 votes
1 answer
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
37 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
60 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
85 views
0 votes
1 answer

=CONCATENATE() function giving a #VALUE! error (EXCEL 2013)

You can use the following: =A1&","&B1 TEXTJOIN would also ...READ MORE

answered Nov 12 in Others by narikkadan
• 37,660 points
27 views
0 votes
1 answer

Camel case converter in an excel

Try this: =LOWER(LEFT(A1)) & MID(SUBSTITUTE(PROPER(A1),"_","") ...READ MORE

answered Nov 19 in Others by narikkadan
• 37,660 points
26 views
0 votes
1 answer

Microsoft Excel: Formula bar says "Calculations!" instead of the equation used

By right-clicking the Retirement Planner sheet tab ...READ MORE

answered 5 days ago in Others by narikkadan
• 37,660 points
13 views
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7 in Others by narikkadan
• 37,660 points
28 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