Divide whole numbers across multiple cells with variable

L

Legacy 386498

Guest
Hi all,

I need to solve a problem of dividing whole numbers in excel based on these variable:

Company AA can only sale to company A and the quantity bought and sold is always the same. In this example 44 000. Company A1 can receive 6000 from AA1 and 9000 from AA2 or 15000 from AA1, etc. That doesn't matter.
Company BB can only sale to company B, but the quantity bought and sold is always different, so company always need to buy externally to compensate. In this example, 13 000 sold and 18 000 bought, so 5000 bought externally.
Company CC to FF can sale to any company from C to F. The goal here is for CC to sell has much as possible to C, DD to D, EE to E and FF to F. When not possible, sell from one to another and keeping the quantity bought and sold externally to a minimum. In this example, CC can sell 8000 to C and 2000 to D.

What can I do to solve this problem with excel? I've read online about excel solver, is there anything else? Is it the best solution for me?

Thanks in advance.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Capacity sold[/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]External[/TD]
[/TR]
[TR]
[TD]Capacity bought[/TD]
[TD][/TD]
[TD]15000[/TD]
[TD]10000[/TD]
[TD]19000[/TD]
[TD]10000[/TD]
[TD]8000[/TD]
[TD]8000[/TD]
[TD]7000[/TD]
[TD]20000[/TD]
[TD]7000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB1[/TD]
[TD]7000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB2[/TD]
[TD]6000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DD1[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE1[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FF1[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]External[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I'm not sure I understand the constraints.

You say the goal is for CC to sell as much as possible to C and when that's not possible, sell to someone else the minimum possible. But under what conditions is it not possible for CC to sell to C? And what do you mean "minimum"?
Also, company B bought 5,000 externally. Why 5,000? What are they trying to meet? What are the conditions? And does that action play into what companies CC:FF & C:F are doing?

Please tag me when you respond
 
Last edited:
Upvote 0
I'm not sure I understand the constraints.

You say the goal is for CC to sell as much as possible to C and when that's not possible, sell to someone else the minimum possible. But under what conditions is it not possible for CC to sell to C? And what do you mean "minimum"?
Also, company B bought 5,000 externally. Why 5,000? What are they trying to meet? What are the conditions? And does that action play into what companies CC:FF & C:F are doing?

Please tag me when you respond

Company B bought 5 000 externally, because B can only buy from BB. BB has 13 000 to sell (6 000 + 7 000) and we need to full B with 18 000 (10 000 + 8 000), so B will buy/transfer 13 000 from BB and 5 000 externally to max out the number they can buy (18 000). So they're trying to meet the max capacity bought (same for every company).

Company CC can sell 10 000 to C, but C can only buy 8 000, they don't have the capacity to buy more, so CC can still sell 2 000 to D, E and/or F. The goal here is for company AA to FF to sell everything to/full company A to F internally. When that's not possible, they will sell externally. Or the contrary can happen where A to F can buy more than company AA to FF can sell internally, so they will buy the difference externally. That's what I mean by keeping the quantity bought and sold externally to a minimum.

I hope this makes more sense, don't hesitate if something isn't clear.
 
Last edited by a moderator:
Upvote 0
I can't think of a way to solve this "problem" in excel. I feel like its as simple as saying "Has BB sold all of it's capacity to B?" if yes, then B buys the rest externally. I don't see there being anything complicated to solve. This should be built into the ordering software you use. That's my only thought.
 
Upvote 0
I can't think of a way to solve this "problem" in excel. I feel like its as simple as saying "Has BB sold all of it's capacity to B?" if yes, then B buys the rest externally. I don't see there being anything complicated to solve. This should be built into the ordering software you use. That's my only thought.

The problem is I don't have an ordering software, only excel. Thanks for taking the time 'tho.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top