# Rate Consolidation

#### wcihelp

##### New Member
I am working on consolidating several different employee groups. They are separated by different benefit rates. I have 25 different groups, each with 9 different rates. I am looking to consolidate the 25 groups if possible by comparing similarites in the rates. Is there a formula that can help me identify which groups are close enough to each other in terms of rates for consolidation?

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### oldbrewer

##### Well-known Member
please define "close enough" eg +- 0.5% (this might vary according to "size" of benefit)

#### wcihelp

##### New Member
Hi, I would say +- 2% would be ideal. Thanks!

#### oldbrewer

##### Well-known Member
 group R1 R2 R3 R4 R5 R6 R7 R8 R9 G1 1000 1020 1040 1061 1082 1104 1126 1149 1172 G2 1060 1092 1125 1158 1193 1229 1266 1304 1343 G3 1120 1165 1211 1260 1310 1363 1417 1474 1533 G4 1180 1239 1301 1366 1434 1506 1581 1660 1743 G5 1240 1314 1393 1477 1565 1659 1759 1865 1976 G6 1300 1391 1488 1593 1704 1823 1951 2088 2234 G7 1360 1469 1586 1713 1850 1998 2158 2331 2517 G8 1420 1548 1687 1839 2004 2185 2381 2596 2829 G9 1480 1628 1791 1970 2167 2384 2622 2884 3173 G10 1540 1709 1897 2106 2338 2595 2880 3197 3549 G11 1600 1792 2007 2248 2518 2820 3158 3537 3962 G12 1660 1876 2120 2395 2707 3058 3456 3905 4413 G13 1720 1961 2235 2548 2905 3312 3775 4304 4906 G14 1780 2047 2354 2707 3113 3580 4117 4735 5445 G15 1840 2134 2476 2872 3332 3865 4483 5200 6032 G16 1900 2223 2601 3043 3560 4166 4874 5702 6672 G17 1960 2313 2729 3220 3800 4484 5291 6244 7367 G18 2020 2404 2861 3404 4051 4820 5736 6826 8123 G19 2080 2496 2995 3594 4313 5176 6211 7453 8944 G20 2140 2589 3133 3791 4587 5551 6716 8127 9833 G21 2200 2684 3274 3995 4874 5946 7254 8850 10797 G22 2260 2780 3419 4206 5173 6363 7826 9626 11840 G23 2320 2877 3567 4423 5485 6801 8434 10458 12968 G24 2380 2975 3719 4648 5811 7263 9079 11349 14186 G25 2440 3074 3874 4881 6150 7749 9764 12302 15501 how could you rationalise groups with the above starting points and rate increments ?

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

Replies
3
Views
199
Replies
0
Views
167
Replies
4
Views
292
Replies
8
Views
299
Replies
3
Views
125

1,129,563
Messages
5,637,077
Members
416,956
Latest member
mitzhaki

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back