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, 2022 in Others by Kithuzzz
• 28,520 points
138 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, 2022 by narikkadan
• 52,760 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, 2022 in Others by Kichu
• 19,050 points
85 views
0 votes
1 answer
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 52,760 points
210 views
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, 2022 in Others by narikkadan
• 52,760 points
127 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

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

answered Oct 7, 2022 in Others by narikkadan
• 52,760 points
274 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, 2022 in Others by narikkadan
• 52,760 points
68 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, 2022 in Others by narikkadan
• 52,760 points
254 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 Nov 26, 2022 in Others by narikkadan
• 52,760 points
54 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, 2022 in Others by narikkadan
• 52,760 points
99 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 52,760 points
108 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