Disribute a group of numbers among four cells
 

I have a group of numbers that need to be distributed into four groups so
that the sum of the numbers in each of the four groups is as equal as
possible.

E.G., if I had this group of numbers: 26, 45, 99, 33, 45.5, 66.67, 15,
110.15 & 76.33

how would I break these 9 numbers into 4 groups so that each of the groups
had a sum as close as possible to the other groups?

Disribute a group of numbers among four cells
 

Hi
You basically want to add the largest and smallest values together. If the
data is in column B type the numbers 1, 2, 3, 4, 5 in column D
In E1 type =LARGE($B$1:$B$9,D1)+SMALL($B$1:$B$9,D1) and copy down to line 4.

In E5 you want the median value =MEDIAN(B1:B9) you can add this to the
smallest sum row 4

Regards
Peter

"LeodaQ" wrote:

I have a group of numbers that need to be distributed into four groups so
that the sum of the numbers in each of the four groups is as equal as
possible.

E.G., if I had this group of numbers: 26, 45, 99, 33, 45.5, 66.67, 15,
110.15 & 76.33

how would I break these 9 numbers into 4 groups so that each of the groups
had a sum as close as possible to the other groups?

Disribute a group of numbers among four cells
 

Thanks for the reply, but don't think that is what I was looking for. I'm
sorry if I didn't make myself clearer. Maybe if I explain my situation, it
will be better.

I am in sales, and four of us at the store order carpets that earn a spiff
at a rate of 1 point per square yard. Each order is seperate and can be
assigned to ONLY one salesperson and ONLY in the full amount of the order.

Currently, we have nineteen orders for the following amounts:

96.44
166.67
169.78
171.00
193.55
333.34
333.34
345.23
500.01
500.01
533.01
666.68
666.68
690.11
714.78
775.33
834.12
883.89
1380.24

I need to distribute these orders among the four salespeople to end up with
four groups of orders whose totals are as equal as possible (i.e., so that
each salesperson has about the same amount of spiff points as the others).

Is there any way to do what I want?

Thanks, in advance, for any advice you can give.



"PeterAtherton" wrote:

Hi
You basically want to add the largest and smallest values together. If the
data is in column B type the numbers 1, 2, 3, 4, 5 in column D
In E1 type =LARGE($B$1:$B$9,D1)+SMALL($B$1:$B$9,D1) and copy down to line 4.

In E5 you want the median value =MEDIAN(B1:B9) you can add this to the
smallest sum row 4

Regards
Peter

"LeodaQ" wrote:

I have a group of numbers that need to be distributed into four groups so
that the sum of the numbers in each of the four groups is as equal as
possible.

E.G., if I had this group of numbers: 26, 45, 99, 33, 45.5, 66.67, 15,
110.15 & 76.33

how would I break these 9 numbers into 4 groups so that each of the groups
had a sum as close as possible to the other groups?

Disribute a group of numbers among four cells
 

Leo
I thought that my answer would be too simple. Set the data out like this:

Totals Total 1 Total 2 Total 3 Total 4 Check Sum
9954.21 2290.8 2574.02 3131.15 1958.24 9954.21
Orders A B C D
96.44 96.44
166.67 166.67
169.78 169.78
171 171
193.55 193.55 0 0 0

A2 has the formula Sum(a4:a500) and copy across to column E. F2 has formula
sum(b2:e2)

enter the first 4 records for the sales team A - D. In Row 8 we have these
formulas

B8:
=IF(AND(SUM($B$4:B7)<SUM($C$4:C7),SUM($B$4:B7)<SUM ($D$4:D7),SUM($B$4:B7)<SUM($E$4:E7)),$A8,0)

c8:
=IF(AND(SUM($C$4:C7)<SUM($B$4:B7),SUM($C$4:C7)<SUM ($D$4:D7),SUM($C$4:C7)<SUM($E$4:E7)),$A8,0)

D8:
=IF(AND(SUM($D$4:D7)<SUM($B$4:B7),SUM($D$4:D7)<SUM ($C$4:C7),SUM($D$4:D7)<SUM($E$4:E7)),$A8,0)

E8:
=IF(AND(SUM($E$4:E7)<SUM($B$4:B7),SUM($E$4:E7)<SUM ($C$4:C7),SUM($E$4:E7)<SUM($D$4:D7)),$A8,0)

The zeros can be hidden; Tools, OPtions,View and click the display zeros
checkbox.

Doesn't look too good but it works.

Regards
Peter

"leodaq" wrote:

Thanks for the reply, but don't think that is what I was looking for. I'm
sorry if I didn't make myself clearer. Maybe if I explain my situation, it
will be better.

I am in sales, and four of us at the store order carpets that earn a spiff
at a rate of 1 point per square yard. Each order is seperate and can be
assigned to ONLY one salesperson and ONLY in the full amount of the order.

Currently, we have nineteen orders for the following amounts:

96.44
166.67
169.78
171.00
193.55
333.34
333.34
345.23
500.01
500.01
533.01
666.68
666.68
690.11
714.78
775.33
834.12
883.89
1380.24

I need to distribute these orders among the four salespeople to end up with
four groups of orders whose totals are as equal as possible (i.e., so that
each salesperson has about the same amount of spiff points as the others).

Is there any way to do what I want?

Thanks, in advance, for any advice you can give.



"PeterAtherton" wrote:

Hi
You basically want to add the largest and smallest values together. If the
data is in column B type the numbers 1, 2, 3, 4, 5 in column D
In E1 type =LARGE($B$1:$B$9,D1)+SMALL($B$1:$B$9,D1) and copy down to line 4.

In E5 you want the median value =MEDIAN(B1:B9) you can add this to the
smallest sum row 4

Regards
Peter

"LeodaQ" wrote:

I have a group of numbers that need to be distributed into four groups so
that the sum of the numbers in each of the four groups is as equal as
possible.

E.G., if I had this group of numbers: 26, 45, 99, 33, 45.5, 66.67, 15,
110.15 & 76.33

how would I break these 9 numbers into 4 groups so that each of the groups
had a sum as close as possible to the other groups?

Disribute a group of numbers among four cells
 

Oops
Then copy the formulas down past your data

Peter

"PeterAtherton" wrote:

Leo
I thought that my answer would be too simple. Set the data out like this:

Totals Total 1 Total 2 Total 3 Total 4 Check Sum
9954.21 2290.8 2574.02 3131.15 1958.24 9954.21
Orders A B C D
96.44 96.44
166.67 166.67
169.78 169.78
171 171
193.55 193.55 0 0 0

A2 has the formula Sum(a4:a500) and copy across to column E. F2 has formula
sum(b2:e2)

enter the first 4 records for the sales team A - D. In Row 8 we have these
formulas

B8:
=IF(AND(SUM($B$4:B7)<SUM($C$4:C7),SUM($B$4:B7)<SUM ($D$4:D7),SUM($B$4:B7)<SUM($E$4:E7)),$A8,0)

c8:
=IF(AND(SUM($C$4:C7)<SUM($B$4:B7),SUM($C$4:C7)<SUM ($D$4:D7),SUM($C$4:C7)<SUM($E$4:E7)),$A8,0)

D8:
=IF(AND(SUM($D$4:D7)<SUM($B$4:B7),SUM($D$4:D7)<SUM ($C$4:C7),SUM($D$4:D7)<SUM($E$4:E7)),$A8,0)

E8:
=IF(AND(SUM($E$4:E7)<SUM($B$4:B7),SUM($E$4:E7)<SUM ($C$4:C7),SUM($E$4:E7)<SUM($D$4:D7)),$A8,0)

The zeros can be hidden; Tools, OPtions,View and click the display zeros
checkbox.

Doesn't look too good but it works.

Regards
Peter

"leodaq" wrote:

Thanks for the reply, but don't think that is what I was looking for. I'm
sorry if I didn't make myself clearer. Maybe if I explain my situation, it
will be better.

I am in sales, and four of us at the store order carpets that earn a spiff
at a rate of 1 point per square yard. Each order is seperate and can be
assigned to ONLY one salesperson and ONLY in the full amount of the order.

Currently, we have nineteen orders for the following amounts:

96.44
166.67
169.78
171.00
193.55
333.34
333.34
345.23
500.01
500.01
533.01
666.68
666.68
690.11
714.78
775.33
834.12
883.89
1380.24

I need to distribute these orders among the four salespeople to end up with
four groups of orders whose totals are as equal as possible (i.e., so that
each salesperson has about the same amount of spiff points as the others).

Is there any way to do what I want?

Thanks, in advance, for any advice you can give.



"PeterAtherton" wrote:

Hi
You basically want to add the largest and smallest values together. If the
data is in column B type the numbers 1, 2, 3, 4, 5 in column D
In E1 type =LARGE($B$1:$B$9,D1)+SMALL($B$1:$B$9,D1) and copy down to line 4.

In E5 you want the median value =MEDIAN(B1:B9) you can add this to the
smallest sum row 4

Regards
Peter

"LeodaQ" wrote:

I have a group of numbers that need to be distributed into four groups so
that the sum of the numbers in each of the four groups is as equal as
possible.

E.G., if I had this group of numbers: 26, 45, 99, 33, 45.5, 66.67, 15,
110.15 & 76.33

how would I break these 9 numbers into 4 groups so that each of the groups
had a sum as close as possible to the other groups?