Numbers apportionment equally

[alialrawi]

Dear ِAll,
Kindly your support to do below in excel :
When we want to weekly upgrade / downgrade capacities we must change all sources and this takes much time to finish .
I suggest create a tool to manage this process by entering all capacities and this tool will be balancing the capacities each source 10G.
In case if not acceptable to balance the tool must be given an error.

like this example below :
Capture.JPG

[AliGW]

Welcome to the forum.

What exactly is your question?

[alialrawi]

Hi AliGW ,
my question :
how i can to create formulas and functions to do my work
for ex:
i have 10 sources
each source must be 10 gigabyte
each source have diffrent numbers
For clarification :
AA
5
2
3
------
10

now i working manually
I want any formulas to do that
Thanks

[MrShorty]

The problem feels incompletely defined to me, so it is difficult to propose an algorithm. If I understand the problem, you want to have 10 "gadgets". Each "gadget" is composed of up to three "widgets", and these "widgets" must add up to 10 G for each "gadget". What can you tell us about the widgets that make up the gadgets?

On the surface, it feels like either a "solve a system of equations" kind of problem, or a subset sum (which reduces to knapsack) kind of problem. I would probably start this by deciding which kind of problem this is, then research the strategies and algorithms that go into solving that kind of problem.

[alialrawi]

Dear
In the first post i attached photo can you see it may be help to Understand my problem
thanks

[MrShorty]

I can just barely read the screenshot, but it does not really tell me anything about the widgets. I can see that widgets range in size from 0.2 (the h2 widget in the HH block) to 10 (b1, i1, j1). I see nothing that tells me why you chose 1 size 10 widget for some cases or why you chose 2 widgets or 3 widgets in other cases. Algorithms do exist for these kinds of problems, but we must know enough about your problem in order to talk about implementation.

[alialrawi]

Dear
My case is :
I have multi sources and each source must 10
So each source have multi items , must total of items 10
In my ex
AA have 3 items classified :
2
5
3
Total equel 10
I do that manually
I want fourmal in excel to do this process

[MrShorty]

In your screenshot for AA, you chose only two widgets: 2.5 and 7.5. In post #7, you chose 3 widgets 2, 5, and 3. Is there a reason behind these different choices, or does it suggest any valid choice will work?

I doubt this will be suitable, but could we just choose random numbers? As far as generating one, two, or three random numbers that add up to 10, this works:

B2 =ROUND(RAND()*10,1)
B3 =ROUND(RAND()*(10-B2),1)
B4 =MAX(10-SUM(B2:B3),0)

That will generate up to three random numbers that add up to 10. I doubt that is the kind of algorithm you want, but it maybe gives a starting place to describe why it does not work.

[alialrawi]

My dear can you give me your email

[MrShorty]

That should not be necessary. Since these resource allocation type questions are a common computer science question, a public discussion of your specific problem will help others in the future to understand how these algorithms work.

[alialrawi]

☺️ Sorry no problem i just wanted to see how i work ,
So that you closer to the subject
How you can solve this example :
If you have :-
1.block 1
2,5,3

2.block 2
1.5,0.5,8

3.block 3
10

4.block 4
10

5.block 5
8,2

6.block 6
10

7.block 7
10

8.block 8
0.8,0.2,4.5,1.5,3

9.block 9
10

10.block 10
2,8

Now i want to increase
1.in block 2
0.5 increase 0.5 to be 1
2.in block 8
0.2 increase 0.3 to be 0.5
3.in block 10
2 increase 0.5 to be 2.5

Now how you can rearrange all blocks but note must each block equal =10

[MrShorty]

In this scenario, I see you:

1) Start with each block as described. Each block already adds up to 10.
2) Take the minimum item from certain blocks (but not every block) and add 0.5 to that item. How do you choose which blocks to change and which to leave unchanged? Do your formulas need to be able to make this decision?
3) Now we need to adjust one or more of the other items in the changed blocks so that they still add up to 10. Which of the other items will you change? For a block like 10, there is only one other item to change, so the 0.5 must be taken from that one. But for the others, where you have up to 5 items in a block, how will you choose which one to take the 0.5 from?

How complete is this description of the method? What am I not understanding?

[alialrawi]

Dear MrShorty
can you see the following post it's exactly same problem


As you see I need some kind of formula that automatically redistribute numbers in the blue columns without changing the value of the number (only changing positions) so the totals of all columns will be (<= 10 ) or near , and if i increased on of the number's and there's no way to distribute equally for each group to be 10, it should tell me you have to add another group ,, i tried to use solver and it didn't help me a lot and i will attache the workbook that i have working on.

many thanks for your patience

[MrShorty]

Your link to the other post did not come through. However, I can now see new elements of the problem that have not been described before. In posts 11 and 12, we were talking about simply adding and subtracting 0.5 from chosen items in each group. Now we are talking about having a list of available items to choose from, and we need to choose items from the list that add up to 10. As I mentioned in post #4, these problem are more of a "subset sum"/"knapsack" type of problem (https://en.wikipedia.org/wiki/Subset_sum_problem ). In your case, your example shows 36 items that need to be distributed to 20 groups where each group adds up to 10 (or no more than 10). Not only will it be a subset sum type of problem, but I expect it will have characteristics of a multidimensional subset sum problem. I also don't think it will reduce down to a simple (or even complex) single cell formula.

As explained in the wikipedia article, Subset sum/knapsack type problems tend to be NP-hard/NP complete -- which means that a rigorous solution can only be found by trying every possible combination and choosing which combination best fits the selection criteria. When the problem expands to multiple dimensions, the size of the problem becomes very large very quickly.

Without taking to time to fully develop the solution, I envision an algorithm similar to the algorithm described in the Wikipedia article.
1) Sort the list in descending order. Take the top items that are exactly 10 and assign them to groups.
2) With the 10's removed, start with the next largest item, and figure out all combinations of the smallest items at the bottom of the list that combine with the top one to add up to less then 10.
3) Repeat for the next largest item, and so on until you are convinced that you have enough combinations to provide an adequate solution.
4) Analyze all of the different sums and choose which combinations to assign to groups.

I've attached a spreadsheet that shows how I see this algorithm working (start in P13). I don't know if I would actually do all of this in the spreadsheet, or if I would move to a more symbolic programming language (VBA, C, python, other). Considering the amount of effort needed to develop this from scratch, it might be worth some effort to see if there is a preprogrammed utility out there. Even if it requires a paid license, you might be better off to pay for a turnkey solution rather than go to the effort to develop your own.

There's still a lot of development left to get a final, fully automated solution. How far are you willing to pursue this?