Formula or Function that can find a specified answer?

Hi:

I think this a bit more complicated than is seems...it looks like one of the Excel monthly challanges...

How many items would be in the data list?

plettieri

As stated above, this gets asked regularly. The answer depends on the number of choices to search through and the possibility of ties.

You need to answer the following questions to help better define the problem.

Will there always be an answer in the data?

If so, will it be unique? If not, which how do you choose the "best" answer?

If there is the possibility of no answer, what do you want? The closest or nothing? If the closest, how do you define this?

How many choices can be used at one time? 1, 2, all?

How many choices will there be? 6 like your example or many more?

With these answers, someone on the foirum may be able to give you an answer or at least a starting point.

Good luck.

Seti

hi!
I agree with the above answers. Its complicated.
One question is how mane combiantion of each you want.'
example.
Heres a set up I made to give you all the possible answers you like. Just set up the worksheet as per yellow color. they are needed data.

like this


And run this code!<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><SPAN style="color:#00007F">Dim</SPAN> n, k, q<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN><SPAN style="color:#00007F">Dim</SPAN> Comblist()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Dim</SPAN> b()<SPAN style="color:#00007F">Dim</SPAN> Ans()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Comb(j, m<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>)<SPAN style="color:#00007F">Dim</SPAN> tmp, X
    <SPAN style="color:#00007F">If</SPAN> j > n<SPAN style="color:#00007F">Then</SPAN>
        tmp = ""
        <SPAN style="color:#00007F">For</SPAN> X = 1<SPAN style="color:#00007F">To</SPAN> n
            tmp = tmp & b(X)
        <SPAN style="color:#00007F">Next</SPAN> X
        
        q = q + 1
        <SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> Comblist(UBound(Comblist) + 1)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>
        Comblist(UBound(Comblist)) = tmp

    <SPAN style="color:#00007F">Else</SPAN>
        <SPAN style="color:#00007F">If</SPAN> k - m< n - j + 1<SPAN style="color:#00007F">Then</SPAN>
            b(j) = 0
            Comb j + 1, m
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">If</SPAN> m< k<SPAN style="color:#00007F">Then</SPAN>
            b(j) = 1
            Comb j + 1, m + 1
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Sub</SPAN> Sumxxx()<SPAN style="color:#00007F">Dim</SPAN> St, i, j, tmp, Tmp2, AnsCount, L, Res<SPAN style="color:#00007F">Dim</SPAN> listx()
n = Range("a65536").End(xlUp).Row - 1
Res = Range("c1").Value
q = 0<SPAN style="color:#00007F">ReDim</SPAN> Comblist(0)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">ReDim</SPAN> b<SPAN style="color:#00007F">ReDim</SPAN> listx<SPAN style="color:#00007F">ReDim</SPAN> Ans(0)<SPAN style="color:#00007F">For</SPAN> k = 1<SPAN style="color:#00007F">To</SPAN> n
Comb 1, 0<SPAN style="color:#00007F">Next</SPAN> k<SPAN style="color:#00007F">For</SPAN> i = 1<SPAN style="color:#00007F">To</SPAN> n
    listx(i) = Range("a" & 1 + i).Value<SPAN style="color:#00007F">Next</SPAN> i
AnsCount = 0<SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(Comblist)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Comblist)
    St = ""
    tmp = 0
    <SPAN style="color:#00007F">For</SPAN> j = 1<SPAN style="color:#00007F">To</SPAN> Len(Comblist(i))
        tmp = tmp + Mid(Comblist(i), j, 1) * listx(j)
        <SPAN style="color:#00007F">If</SPAN> Mid(Comblist(i), j, 1) = 1<SPAN style="color:#00007F">Then</SPAN>
            St = St & "+" & listx(j)
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> j
    <SPAN style="color:#00007F">If</SPAN> tmp = Res<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">ReDim</SPAN><SPAN style="color:#00007F">Preserve</SPAN> Ans(UBound(Ans) + 1)
        AnsCount = AnsCount + 1
        Ans(AnsCount) = St
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Next</SPAN> i
Range("e2:f65536").Value = ""<SPAN style="color:#00007F">For</SPAN> i =<SPAN style="color:#00007F">LBound</SPAN>(Ans) + 1<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(Ans)
    Range("e" & i + 1).Value = "'" & Mid(Ans(i), 2, Len(Ans(i))) & "="
    Range("F" & i + 1).Value = "=" & Ans(i)<SPAN style="color:#00007F">Next</SPAN> i<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Note. the more list in Column A you have the slower the code will return.
If you got more than 30 entries it will take sometimes to return,and it will be a cold day in hell before youl see the combination of 100 and up.

non-code alternative:

http://www.mrexcel.com/board2/viewtopic.php?t=23107