Rate Consolidation

wcihelp

New Member
Joined
Jul 26, 2016
Messages
10
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?
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
please define "close enough" eg +- 0.5% (this might vary according to "size" of benefit)
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
groupR1R2R3R4R5R6R7R8R9
G1100010201040106110821104112611491172
G2106010921125115811931229126613041343
G3112011651211126013101363141714741533
G4118012391301136614341506158116601743
G5124013141393147715651659175918651976
G6130013911488159317041823195120882234
G7136014691586171318501998215823312517
G8142015481687183920042185238125962829
G9148016281791197021672384262228843173
G10154017091897210623382595288031973549
G11160017922007224825182820315835373962
G12166018762120239527073058345639054413
G13172019612235254829053312377543044906
G14178020472354270731133580411747355445
G15184021342476287233323865448352006032
G16190022232601304335604166487457026672
G17196023132729322038004484529162447367
G18202024042861340440514820573668268123
G19208024962995359443135176621174538944
G20214025893133379145875551671681279833
G212200268432743995487459467254885010797
G222260278034194206517363637826962611840
G2323202877356744235485680184341045812968
G2423802975371946485811726390791134914186
G2524403074387448816150774997641230215501
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,095,397
Messages
5,444,241
Members
405,276
Latest member
HerbertZ

This Week's Hot Topics

Top