Split Values into Multiple Equal Groups / Ranks
Assign Column Values into Groups, with Group 1 having Largest Values & Group 5 Lowest Values
Column Values are divided equally between Groups, each group is ranked as per column values, Group 1 having Largest Values & Group 5 Lowest Values.
Column G values are assigned groups in column I , as per corresponding Criteria. Cell K2 defines the number of Groups into which column values are divided into.
Column I assigns Column G values into Groups, divided equally between the Minimum and Maximum column G values. Enter formula in cell I2 and copy down.
———————————————————————————————–
Column I assigns Column G values into Groups, divided equally between the Minimum and Maximum column G values.
Cell I2 formula:
=IF(G2=””,””, MATCH(TRUE, INDEX(G2>= MAX(G$2:G$25)- (MAX(G$2:G$25)- MIN(G$2:G$25))/ K$2*ROW( INDEX(A:A, 1):INDEX( A:A, K$2)) ,), 0))
Column I formula:
1. Assign Column G values into Groups, divided equally between the Minimum and Maximum column G values.
2. Groups are divided into 5 equal gaps / value ranges, between the Minimum and Maximum column G values, and each column G value is classified in its respective group.
3. Classification of column G values with a gap / range of 3.98 each: Group 5: >=7 & <10.98; Group 4: >=10.98 & <14.96; Group 3: >=14.96 & <18.94; Group 2: >=18.94 & <22.92; Group 1: >=22.92 & <=26.90. This means that there may be uneven values in a specific group and a specific group may have no value also.
4. Enter formula (Non-Array) in cell I2 and copy down. Cell I2 formula refers cell K2 value for the number of groups within which column G values are to be divided.
Explanation – Cell I2 formula (Non-Array formula):
1) ROW(INDEX(A:A, 1):INDEX(A:A, K$2)): returns the array – {1;2;3; 4;5} – this determines the number of groups into which column G values are divided into.
2) MAX(G$2:G$25)- (MAX(G$2:G$25)- MIN( G$2:G$25))/K$2*ROW( INDEX(A:A, 1):INDEX(A:A, K$2)): this returns the lower value range for each of the 5 groups where the 5 array values appear in reverse order – {22.92;18.94; 14.96; 10.98;7}. These are the lower value of the 5 ranges / groups in reverse order – group 1 to group 5).
3) G2>= MAX(G$2:G$25)- (MAX( G$2:G$25)- MIN( G$2:G$25))/K$2*ROW( INDEX(A:A, 1):INDEX( A:A, K$2)): returns TRUE where cell G2 value is greater than or equal to the array values in 2) above – {TRUE;TRUE; TRUE;TRUE; TRUE} – this indicates that cell G2 is greater than or equal to all the 5 array values. INDEX function is used with this to keep the formula as Non-Array. This is the lookup_array argument of the MATCH function as explained below.
4) MATCH(TRUE, INDEX( G2>= MAX( G$2:G$25)- (MAX( G$2:G$25)- MIN( G$2:G$25))/K$2*ROW( INDEX( A:A, 1):INDEX(A:A, K$2)),), 0): MATCH function is used with the lookup_value of TRUE and the lookup_array in 3) above with match_type as zero to return an exact match of the 1st occurrence of TRUE in the lookup_array. This determines the group number by returning the position of the first range / group which cell G2 value surpasses or is equal to. Cell G2 value of 24.8 is greater than the 1st array value of 22.92 (which is the lower range of group no 1) and hence cell G2 formula returns 1.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Column Values are divided equally between Groups, each group is ranked as per column values, Group 1 having Largest Values & Group 5 Lowest Values.
Column A values are assigned groups in column B; column D values are assigned groups in column E, column G values are assigned groups in columns H / I , as per corresponding Criteria.
Cell K2 defines the number of Groups into which column values are divided into, and formulas in columns B / E / I refer this cell while column H formula uses absolute value to define the number of groups.
Column B: Assigns Column A Values (having Distinct Values – No Duplicates) into Equal Groups. Column A has only numbers, which are distinct values (no duplicates), +ives or -ives, variable no of rows, & inbetween blank cells (which will return a blank). Divide column A values into 5 (per cell K2 value) equal groups per their rank (1,2,3,4,5); if number of values are not divisible by 5, then the last group ie. group 5, will have more values than the other groups (from 1 to 4) which will have same number of values. To illustrate: if column A has 15 values then each of the 5 groups will have 3 values, if column A has 16 values then groups 1 to 4 will have 3 values each & group 5 will have 4 values, for 17 values groups 1 to 4 will have 3 values each & group 5 will have 5 values – note that the largest 3 values will be in group 1, the next largest 3 values will be in group 2, and so on. Enter formula (Non-Array) in cell B2 and copy down.
Column E: Assigns Column D Values (values can be repeated – Duplicates allowed) into Equal Groups. Column D has values which may be repeated (ie. duplicate), all other assumptions are same as for Column A. Identical values will be considered in the order of their occurrence so that the value occurring before (ie. in lower row number) will be considered Larger than the same value occurring in a higher row number (ie. lower down). Enter formula as an Array formula (Ctrl+Shift+Enter) in cell E2 & copy down.
Column H: Assigns Column G values into Groups, divided equally between the Minimum and Maximum column G values. Groups are divided into 5 equal gaps / value ranges, between the Minimum and Maximum column G values, and each column G value is classified in its respective group. Cell H2 formula is similar to cell I2 formula (as illustrated above) except that cell H2 has an absolute value for group numbers whereas cell I2 formula refers cell K2 value for the number of groups within which column G values are to be divided.