Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating only non-empty cells... | Excel Worksheet Functions | |||
Formula to return a value if any numbers in a group meet a criteri | Excel Worksheet Functions | |||
How to format cells to recognize numbers like 1.1.1? | Excel Discussion (Misc queries) | |||
question about removing text from cells, leaving numbers | Excel Discussion (Misc queries) | |||
check if 2 cells are equal but only if they contain numbers not i. | Excel Worksheet Functions |