Formula to find closest combination of numbers ...

Hi all,

So I have looked for the answer for this and cannot find it. In the picture below you can see this but in column A there is 4 numbers from A2-A5, B there is 4 numbers from B2-B5, and C there is 4 numbers from C2-C5. I need to find the combination of three numbers that meet this criteria: 1) when any two numbers of the three are compared they have to be within 10% of each other 2) If more than one combination passes this test then the one result with the closest numbers passes.

Some combinations that would be compared are:
A2,B3,C4
A3,B5,C4
A2,B4,C4
A5,B2,C2

There should be 64 different combinations here...

So If I was to compare the first combination A2,B3, C4 the numbers for these cells are 9.48,8.467, and 9.061.

This combination doesn't pass the first test in that 8.467/9.48=.893; and 8.467/9.061=.934; and 9.061/9.48=.956... so the first test states that all numbers need to be within 10 percent of each other and one number is .893 so it isn't within 10 percent.


So If I was to compare the second combination A3,B5, C4 the numbers for these cells are 9.303,8.911, and 9.061.

The combination passes the first test in that 8.911/9.303=..958; and 8.911/9.061=.983; and 9.061/9.303=.974... so the first test states that all numbers need to be within 10 percent of each other and they all are. However I have to do this 62 more times to find the closest combination.

Thanks.... I am thinking I need to use an array in a formula for this to work.

Capture2.JPG

Please see the yellow banner at the top of the page.

Are you expecting a single cell formula for this? Do you know what kind of algorithm you want to use and need help programming that algorithm into Excel, or do you need help developing an algorithm?

First, a rundown of what I understand you want to do. You want to select a value from each column and the resulting trio should have the smallest range from min to max and no of the values should differ from the other values by more than 10%.

From what I see, the hardest part of what you describe is generating combinations. What I did:
0) I copied the data out of the table, because I am not comfortable with table references. I'm sure it works in tables, but didn't want to be bothered. So, the main data is in A2:C5.
1) I generate the permutations with 3 column of numbers from 1 to 4:
1a) In E2:E5 I enter the number 1. In F2:F5, I also enter the number 1. In G2:G5, I enter the numbers 1 to 4.
1b) In G6, I enter =G2. In F6, I enter =F2+1. In E6, I enter 1.
1c) Copy E6:G6 down until column F exceeds the number 4 (to E33:G33).
1d) In E34, I enter =E2+1. In F34, I enter =F2. In G34, I enter =G30
1e) Copy E34:G34 down until column E exceeds the number 4. Delete the excess rows.
2) With the combinations generated, the next step is to use INDEX() functions to pull the appropriate values from the source data. In H2 I enter =INDEX(A$2:A$5,E2) and copy across to column J and down to row 65.
3) Now I need to get the max [=MAX(H2:J2) in K2], min [=MIN(H2:J2) in L2], difference [=K2-L2 in M2], and % difference [=K2/L2-1 in N2] (note that I am assuming if the max and min are within 10%, that all pairs will also be within 10%.) Copy those formulas to the bottom.
4) From there, a simple MIN() function on column M will tell me what the smallest range is, A lookup function with that result will tell me which row/combination is associated with that minimum, and I can then return the combination and the value from column N to check if it meets the 10% criteria.

That's a fairly simple brute force method that worked for the given sample set. It probably needs to be tested on more data sets to be sure that it will work for other data sets.