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

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

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?

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

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.

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.