+ Reply to Thread
Results 1 to 5 of 5

redistribute numbers using solver

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    Baghdad, Iraq
    MS-Off Ver
    2007
    Posts
    3

    Unhappy 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
    Attached Files Attached Files
    Last edited by ahmed bassam; 07-16-2019 at 05:53 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,736

    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 :-)
    Last edited by Kaper; 07-16-2019 at 07:52 AM. Reason: OP asked to remove the attachment
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    07-16-2019
    Location
    Baghdad, Iraq
    MS-Off Ver
    2007
    Posts
    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
    Last edited by AliGW; 07-16-2019 at 07:04 AM. Reason: Bold is for emphasis only, not whole posts.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,736

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-16-2019
    Location
    Baghdad, Iraq
    MS-Off Ver
    2007
    Posts
    3

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1