# Trying to find best combination of holes from possible 48 choices

1. ## 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.

Antonio

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

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. ## 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. ## 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?

5. ## 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. ## 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

Originally Posted by ABoyer
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

7. ## 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

8. ## 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. ## 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

10. ## 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.

11. ## 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. ## 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. ## 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. ## 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

##### Users Browsing this Thread

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

#### 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