Excel formula to divide numbers into equal group values?

Joined
Dec 1, 2014
Messages
1,166
Location
California
I have a list of 133 widgets (for simplicity sake). Each widget has a unique name.

Each widget is also assigned a number from 4 to 14000, but some widgets do have duplicate, assigned numbers.

I want to assign all of the 133 widgets to one (1) of twelve (12) groups, named Group 1, Group 2, and so on. However, I want the groups to be (approximately), numerically balanced, based upon the sum of the individual numbers that have been pre-assigned to each widget.

The total value of my widgets is 70,228. I'm trying to figure out how to tell Excel to create 12 groups and have the numeric value of each group approximately 5,852.

I hope this makes sense.

I can't seem to find an Excel formula to do this. Maybe someone has an idea for a formula or knows of one?

Thank you,
Ed
 
Joined
Jun 26, 2003
Messages
12,681
Location
Illinois
I don't know about a formula. I'd probably brute force it.

Enter your data

Sort it.
I'd put the first 13 numbers in the first bucket. The next 10 buckets would get 12 numbers. Each bucket contains the collection of the next 12 higher values.

If you know the sum of all 133 values, seek to build sets that are 8% of the sum if possible.

Most sets will have 11, the last will have 12 as you use two from the bucket of the smallest numbers for the last set and that will be 133 total numbers.

But that's brute force and not a formula.
Not sure that will be close enough for what you are trying to accomplish.

Probably need a function to do this as I doubt there is a built in formula.

But I don't use Excel on a daily basis, which is why I would look at some brute force method.
 
Joined
Jun 22, 2022
Messages
429
What's the basis or criterion for appearing in a specific group? If it doesn't matter I would brute force it and forget about it.
 
Joined
Jun 26, 2003
Messages
12,681
Location
Illinois
I missed the TV of your widgets.
If you have widgets with a value of 14k and you are trying to get groups to be around 5.8k in value, you are going to have some groups that are more than 2x that value with a single item of value 14k.

Seems you are asking for something impossible. Or you have criteria that are at odds one with another.
Or some groups will have a single item and others may have two dozen.

I was assuming the groups where a similar number of items and total value.
 

Ed_Flecko

Thread starter
Joined
Dec 1, 2014
Messages
1,166
Location
California
What did you ever come up with? Inquiring minds want to know.
Nothing at all...right now.

I have a friend who has his Masters Degree in Finance and makes a living by using Excel for analyzing real estate investment portfolios and opportunities. In short, he's a whiz at Excel so when I have a little time, I'll pose this question of him.

Ed
 
Top