# redistribute numbers using solver

1. ## redistribute numbers using solver

Hi all,,
iam new member here and i really would ur assistance with a problem that i faced at my work, i searched a lot and couldn't find anything that may help me, here's my problem :
help.jpg
"u can see the attachment"

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.
thanks all
best regards

2. ## Re: redistribute numbers using solver

IMHO for sample data you presented there is no solution where <=10 requirement could be met.
As the sum is 200 you would need exactly sum = 10 in each group. And obviously there is no chance to find "a match" for
xxx 8.6
because 1.4 could not be made of any combination of smallest values:
yyy 1.3
zzz 0.8
aaa 0.7
bbb 0.2

In solver I'd support your approach of minimizing squared differences sum-10 only, I'd remove condition os number of groups fulfilling <=10 requirement.

For the data provided, solver can find a solutions (just one of possible, not necesserily reallly the best one) for 21 groups, but of course there could be data with sum being 200 and many more grups needed - like case of 11 5.2 and 28 5.1 values :-)

3. ## Re: redistribute numbers using solver

Thank you very much for replaying Mr. Kaper ,, another question if you let me,
i'am trying to get the closest value to 10, ok if one group gets more then 10 (10.5 , 10.4 ) but i need all other groups to be closest to 10 ,,
in that case solver didn't really help me even if i change the condition of if ,, i need something, formula or any guide that would help me to solve this
thank again
my best regards
Ahmed.B

4. ## Re: redistribute numbers using solver

For Solver it is very important (and sometimes cruicial) to have a good starting point.

You could try for instance with sorting input data descending, then pre-assign numbers 1 to 19 to all 19 biggest numbers and assign 20 to all other (see Initial Setup sheet). Then you can run solver.
Results are not perfect.
So may be even more pre assignmet would be usefull.
See sheet Manual. I assigned 1-8 to all 10 values and excluded them from solver.
Then I've taken the 9th biggest (ee 8.6) and noticed we have a place for 1.4. Unfortunately, there is no such resulk, so I assigned 9 to (h2 1.3).
the 10th biggest is (mm 8.1) so we have a place for 1.9. Again, no such value, so i wrote 10 next to (nn 1.6) and (a1 0.2) = 1.8 in total. And so on until 20, where I assigned 20 to all remaining values

Now Solver has a good starting point and can quicker provide us with better results.

See attached file

5. ## Re: redistribute numbers using solver

Thanks alot Mr.Kaper that is the best solution for now, but i will keep trying to find something that will make it easier and i will let you know if i find something.
My Best Regards for you
Ahmed.B

There are currently 1 users browsing this thread. (0 members and 1 guests)