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 1,189 views

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.

• 63,720 points

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

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

How to lock data in excel sheet using POI, leaving cells without any data / the rest of the sheet unlocked

If the cells in a row have ...READ MORE

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

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

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

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

Automate compound annual growth rate (CAGR) calculation

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

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

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

Camel case converter in an excel

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

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

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