View original (French)

Algorithm: all possible combinations in a 2 by 7 array

Solved
Hamster18 Posted messages 176 Registration date   Status Membre Last intervention   -  
ccm81 Posted messages 11033 Status Membre -


Hello everyone; before I start programming my Excel macro, I wanted to work out the algorithm of the code because I couldn't see how to do it, but I'm going in circles...

I'm trying to test all possible combinations to find the one that is closest to my target value, but I don't see how to do it.

Here is my problem:
You have a table like in the image.
My target value is 180 (but the target value can change)
By summing one value from each column (either from row 28 or row 29) and adding 179.667 (a modifiable value), we need to get as close as possible to 180.

Not seeing any mathematical formula to solve my problem, I decided to test all possible solutions.

Here is the beginning of the algorithm I wrote, but it's not very conclusive... 


Sub findTarget()
 'targetValue
 'intermediateValue
 'valueToFind
 'previousValueToFind = 100000
 'valueToAdd = 179.667
 'For i = 28 to 29 (indicates the row numbers to process in Excel) 
 'For j = 2 to 8 (indicates the column numbers to process in Excel) 
 'For k = 0 to 1
 'i = i - k
 'intermediateValue = Cells(i,j)
 'valueToFind = valueToFind + intermediateValue
 'Next
 'Next
 'If (previousValueToFind + valueToAdd) - targetValue > (valueToFind + valueToAdd) - targetValue
 'previousValueToFind = valueToFind
 'End If
 'Next
 End


I was thinking of using recursion to solve my problem but I don't see how to use it...
Thank you in advance for your help.
I hope I was clear.
Thank you in advance for your help.
Best regards.

5 réponses

Réponse 1 / 5
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   Ambassadeur 1 587
 
Hello,
putting recursion aside since it doesn't inspire you, I'm thinking of these approaches:
1) how would you do it by hand, how would you organize yourself to count all the sums and find the best one? You can then program that
2) you need to calculate 128 sums. So you could make a loop with an index going from 1 to 127, and use the index to calculate for each column, which row to use.
3) nest seven loops, which might then inspire you towards a recursive approach.
0