I have an excel spreadsheet of results for the analysis of beams in a building.
For each beam I have 6 different loads for 6 different situations. These loads are not linearly related, sometime a beam is bending upwards and sometimes downwards, while other beams only bend downwards. The load cases are not related in any way.
There are over 150 different beams in the building so there is a quite a large data set. I would like to group these 150 beams into a set number of representative beams, lets say 10, that capture the worst case demands of that group. I would then be able to design for the 10 representative members and know that this design would be applicable for all beams in that group.
I can easily go through and assign limits for each group by hand, and then assign beams to each group. The issue is that one, this takes a lot of time, and two, the groups I create by hand are not that efficient. Often times a beam will be lumped with other beams with much higher demands due to how the groups were created.
Is there a way in excel to automatically create 10 groups from a set of data that will split the beams as efficiently as possible. The beams do not need to be split evenly between the groups, but it needs to try to limit the difference between the individual beam demands, and the demands of the representative beam.