+ Reply to Thread
Results 1 to 14 of 14

Trying to find best combination of holes from possible 48 choices

  1. #1
    Registered User
    Join Date
    09-11-2020
    Location
    Allentown, PA
    MS-Off Ver
    2010
    Posts
    4

    Trying to find best combination of holes from possible 48 choices

    Hi,
    I am a new member here. I use excel but not to the extend of the user's messages that I have been reading on here. I have been looking through the messages and I have not found someone doing something similar to what I would like to do in Excel.

    I have 8 discs (2 of each A,B,C,D)
    Each disc has 6 holes. Each hole is different.

    Disc A sized holes:
    0.30
    0.32
    0.34
    0.37
    0.40
    0.43

    Disc B sized holes:
    0.46
    0.49
    0.53
    0.57
    0.61
    0.66

    Disc C sized holes:
    0.71
    0.76
    0.82
    0.88
    0.95
    1.02

    Disc D sized holes:
    1.09
    1.18
    1.26
    1.36
    1.46
    1.57

    I am trying to find the best combination of 2 holes using any 2 discs (I have 2 of each) from the list about for any given target number.

    If the given number does not land exactly on a combination, then I would like to know 2 combinations.

    The one that gives the closest combination on the low side of the target number and the one that gives the high side of the target number.

    I don't know how to go about doing this and if I should be researching using an array or a pivot table or is there some other way that I am unaware of.

    Hopefully someone here can provide some guidance in helping me resolve this.

    Thanks in advance,
    Antonio
    Last edited by ABoyer; 09-11-2020 at 03:22 PM. Reason: Administrator's request

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,273

    Re: New guy & Not sure how to accomplish this task

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

  3. #3
    Registered User
    Join Date
    09-11-2020
    Location
    Allentown, PA
    MS-Off Ver
    2010
    Posts
    4

    Re: New guy & Not sure how to accomplish this task

    Hi Alan,
    Thank you for the info about my post. I have edited my title but since I am unsure how to accomplish my task I am unable to provide function names.

    I hope my updated title is sufficient now.

    Regards,
    Antonio

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,499

    Re: Trying to find best combination of holes from possible 48 choices

    It looks like it would boil down to a subset sum problem. Solver can sometimes solve subset sum problems. See previous discussion here: https://www.excelforum.com/excel-for...en-amount.html

    Would something like that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    09-11-2020
    Location
    Allentown, PA
    MS-Off Ver
    2010
    Posts
    4

    Re: Trying to find best combination of holes from possible 48 choices

    Hello MrShorty,

    Thank you for the links. I will review them and let you know if this will work for me or not.

    Regards,
    Antonio

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,474

    Re: Trying to find best combination of holes from possible 48 choices

    Hi,

    If you arrange your data in A1:D7 as follows (with headers in row 1):

    Disc A Disc B Disc C Disc D
    0.3 0.46 0.71 1.09
    0.32 0.49 0.76 1.18
    0.34 0.53 0.82 1.26
    0.37 0.57 0.88 1.36
    0.4 0.61 0.95 1.46
    0.43 0.66 1.02 1.57

    and put your chosen target value in F2, then, if by

    Quote Originally Posted by ABoyer View Post
    Each hole is different.
    you mean that each hole is different to all holes for all discs, you can use the following pair of formulas:

    =INDEX(MODE.MULT(A2:D7,A2:D7),1+MOD(MATCH(1,FREQUENCY(0,ROUND(F2-(MODE.MULT(A2:D7,A2:D7)+TRANSPOSE(MODE.MULT(A2:D7,A2:D7))),2)),0)-1,COUNT(A2:D7)))

    =INDEX(MODE.MULT(A2:D7,A2:D7),1+INT((MATCH(1,FREQUENCY(0,ROUND(F2-(MODE.MULT(A2:D7,A2:D7)+TRANSPOSE(MODE.MULT(A2:D7,A2:D7))),2)),0)-1)/COUNT(A2:D7)))

    to give you the two values whose total is equal to or less than the target value, and the following pair of formulas:

    =INDEX(MODE.MULT(A2:D7,A2:D7),1+MOD(MATCH(1,FREQUENCY(0,ROUND(MODE.MULT(A2:D7,A2:D7)+TRANSPOSE(MODE.MULT(A2:D7,A2:D7))-F2,2)),0)-1,COUNT(A2:D7)))

    =INDEX(MODE.MULT(A2:D7,A2:D7),1+INT((MATCH(1,FREQUENCY(0,ROUND(MODE.MULT(A2:D7,A2:D7)+TRANSPOSE(MODE.MULT(A2:D7,A2:D7))-F2,2)),0)-1)/COUNT(A2:D7)))

    to give you the two values whose total is equal to or greater than the target value.

    In the latter case, if there is no such pair of values then the second formula will error.

    If your above statement is to be taken as meaning that each hole is different to the other holes for the same disc, but not necessarily different to all holes for all other discs, then you can use the following pair of formulas:

    =SMALL(A2:D7,1+MOD(MATCH(1,FREQUENCY(0,ROUND(F2-(SMALL(A2:D7,ROW(INDEX(A:A,1):INDEX(A:A,COUNT(A2:D7))))+SMALL(A2:D7,COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(A2:D7))))),2)),0)-1,COUNT(A2:D7)))

    =SMALL(A2:D7,1+INT((MATCH(1,FREQUENCY(0,ROUND(F2-(SMALL(A2:D7,ROW(INDEX(A:A,1):INDEX(A:A,COUNT(A2:D7))))+SMALL(A2:D7,COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(A2:D7))))),2)),0)-1)/COUNT(A2:D7)))

    to give you the two values whose total is equal to or less than the target value, and the following pair of formulas:

    =SMALL(A2:D7,1+MOD(MATCH(1,FREQUENCY(0,ROUND(SMALL(A2:D7,ROW(INDEX(A:A,1):INDEX(A:A,COUNT(A2:D7))))+SMALL(A2:D7,COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(A2:D7))))-F2,2)),0)-1,COUNT(A2:D7)))

    =SMALL(A2:D7,1+INT((MATCH(1,FREQUENCY(0,ROUND(SMALL(A2:D7,ROW(INDEX(A:A,1):INDEX(A:A,COUNT(A2:D7))))+SMALL(A2:D7,COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(A2:D7))))-F2,2)),0)-1)/COUNT(A2:D7)))

    to give you the two values whose total is equal to or greater than the target value.

    Again, if there is no such pair of values then the second formula will error.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,484

    Re: Trying to find best combination of holes from possible 48 choices

    As MrShorty says solver may be a possible tool for solving this problem.

    To amuse myself I set up a solver model to be run by using a macro. To do so one must first set a reference to solver in Visual Basic. Click Visual Basic icon -> Tools
    -> References and tick box marked solver. Then run the macro "FindDisks"

    Solver is set to minimize the difference between target values J3:J4 and solver result with a constraint that this difference should be => 0. In the uploaded file solver uses
    disk A (cells D3 and D8) to meet target value 0.73 and disk C (cells F3 and F5) to meet target value of 1.53

    But changing target value from 1,53 to 1.51 solver uses values from E6 and F7 to minimize difference so in this case disks B and C are used to match this target value as well
    as solver is still using the A disk to meet the 0,73 target and I'm unsure if the use of 3 different disks is allowed.

    On the other hand you got a formula solution from XOR LX that works very well so again solver may not be your best option.

    Alf
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-11-2020
    Location
    Allentown, PA
    MS-Off Ver
    2010
    Posts
    4

    Re: Trying to find best combination of holes from possible 48 choices

    MrShorty,

    I don't understand the solver even after watching several videos and reading some web pages about it.

    XOR LX,
    Thank you for the formulas. I will have to try them and let you know if they work. And to clarify on the discs, I have 2 sets of discs A-D, Each A disc has the same holes as the other A disc and likewise for the other 3 sets of discs. Each lettered disc has different hole sizes than the other letter discs. So the largest combination that can be made is using 2 D discs and hole 1.57 from each to come up with a total of 3.14 and the smallest would be using 2 A discs and holes 0.30 to get .060.

    Alf,
    Thanks for the info. I will try your suggestion also and there can never be 3 discs...2 is the most.

    Regards,
    Antonio

  9. #9
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    23

    Re: Trying to find best combination of holes from possible 48 choices

    Hi,

    Solver is the correct tool to use for this case.
    But we should separate it into two model settings.
    Model1: if match, display results; if not match, go to model 2 to solve.
    Model2: if solution found, display results, if no feasible solutions(1 combination lower and 1 combination upper), display errors.

    The reason for using two model setting is the first priority is to get one best solution(and only one); but the second is to get two combinations, one is lower than target and one upper than target, therefore
    the model should not be put together.

    See attached.

    Hope this helps.

    Jimmy
    Attached Files Attached Files

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,862

    Re: Trying to find best combination of holes from possible 48 choices

    Astupig

    I like what you did with this. I noticed the OP wanted the combination from 2 disks. there was a potential using your solved to get 2 holes from one disk that met the criteria (ie 1.27 can be met by the 0.61 and 0.66 on Disk B) but also 0.32 (A) and 0.95(C) which I believe the later is the preferred output.

    As such I added the other constraints to your solver model to force it to look for only 1 from each disk. - I only did this on the first model but it could easily be extended to the second model too.
    Attached Files Attached Files
    Happy with my advice? Click on the * reputation button below

  11. #11
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,484

    Re: Trying to find best combination of holes from possible 48 choices

    Hi Jimmy

    Very nice and compact model. I did believe that there only was 13 solversolve return values but your constraints made me search again so now I know that there are 20 or do you know any more than that?

    Fore those of us who hate pushing buttons you could add a "Private Sub Worksheet_Change". I put one on sheet "model1"

    Please Login or Register  to view this content.
    Hi Crooza

    I tested your modification but changed cell J15 to 2 before running solver. The model found a solution on sheet "model1" using cells D4 and F7 so one wonders how many possible solutions there are.

    Re your constrain why not "condense" it to:
    Please Login or Register  to view this content.
    Alf

  12. #12
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,862

    Re: Trying to find best combination of holes from possible 48 choices

    Alf,

    I've never really dabbled with Solver before. I just looked at what astupig did and it seemed to be logical so I added the other constraints in. I suspect you could do it as you've suggested.

  13. #13
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    23

    Re: Trying to find best combination of holes from possible 48 choices

    Hi Alf,

    There should be only 20 Solversolve return values according to the official document.
    Using worksheet_change event is indeed a good idea.

    Jimmy

  14. #14
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    23

    Re: Trying to find best combination of holes from possible 48 choices

    Hi Crooza,

    Thanks for pointing this out. "only 1 from each disk" may not be the requirement. But this is a good guess! The requirement should be more specific.

    Jimmy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. What formula/filter/lookup can I use to accomplish this task???
    By jfitzes22 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-04-2019, 11:38 AM
  2. [SOLVED] Pivot Chart: Task spend over time, +comparison w/ Task Budget
    By mike_302 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-28-2018, 04:21 PM
  3. How to accomplish copying task
    By Sameer_81 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-08-2017, 10:37 AM
  4. Replies: 3
    Last Post: 05-20-2017, 09:58 AM
  5. Need ideas for simple way to Import, Sort, & Calculate data.
    By notoriusjt2 in forum Excel General
    Replies: 1
    Last Post: 01-23-2016, 04:51 PM
  6. export excel list of task in custom outlook task 2010.
    By maxseal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-22-2014, 03:51 PM
  7. The second time around, the macro does not accomplish the same task.
    By etmisztal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-29-2009, 11:00 PM

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