Number Problem

Sounds like you're trying to cheat on one of those games in the newspaper! :wink:

Errrghh no...

This type of problem exists as part of a game show on TV in the UK. I was just trying to think of a way of using Excel to solve it. A nice little challenge!!!

Do you have to follow the order of operations or are the operations always applied in sequence. e.g. say the numbers were 1,2,3,4,5,6 and you entered:

1 + 2 * 3 + 4 + 5 + 6,

would that be calculated as (1 + 2) * 3

or 1 + (2 * 3 )

Carol Vorderman wouldn't like it!!

I agree. It sounds really interesting. How do they handle the order of operations and parentheses?

For example:

4+5*9/3-2+1+7 = 25 but 4+(5*9)/(3-2)+1+7 = 57

same numbers, same operators, but add parenthesis and you get different answers.

Yea, I was going to say "sounds like a Countdown fan to me.

Can't think of a way to do it though - too many variables.

Yeah this is the typcial Countdown problem - I'm thinking this can't be done as there are too many combinations to choose from.

I did manage to get this working with just using addition, but as soon as you start to think about using the other operators it becomes very difficult!!!

I have a way to run all the possibilities... I'm working on getting it faster.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CountDown()

<SPAN style="color:#00007F">Dim</SPAN> ValueArray(8)
<SPAN style="color:#00007F">Dim</SPAN> OperatorArray(3)
<SPAN style="color:#00007F">Dim</SPAN> r, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#007F00">'use single digit values 1 to 6</SPAN>
<SPAN style="color:#007F00">'You could also define the 6 array values using ranges on another sheet</SPAN>
<SPAN style="color:#007F00">'or by using a userform...</SPAN>
<SPAN style="color:#00007F">For</SPAN> a = 0 <SPAN style="color:#00007F">To</SPAN> 5
    ValueArray(a) = a + 1
<SPAN style="color:#00007F">Next</SPAN> a

<SPAN style="color:#007F00">'Initialize operators</SPAN>
OperatorArray(0) = "+"
OperatorArray(1) = "-"
OperatorArray(2) = "/"
OperatorArray(3) = "*"

r = 1
c = 1

StartTime = Timer

<SPAN style="color:#007F00">'Run all possibilities</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> 5
<SPAN style="color:#00007F">For</SPAN> j = 0 <SPAN style="color:#00007F">To</SPAN> 5
<SPAN style="color:#00007F">For</SPAN> k = 0 <SPAN style="color:#00007F">To</SPAN> 5
<SPAN style="color:#00007F">For</SPAN> l = 0 <SPAN style="color:#00007F">To</SPAN> 5
<SPAN style="color:#00007F">For</SPAN> m = 0 <SPAN style="color:#00007F">To</SPAN> 5
<SPAN style="color:#00007F">For</SPAN> n = 0 <SPAN style="color:#00007F">To</SPAN> 5

    <SPAN style="color:#00007F">For</SPAN> v = 0 <SPAN style="color:#00007F">To</SPAN> 3
    <SPAN style="color:#00007F">For</SPAN> w = 0 <SPAN style="color:#00007F">To</SPAN> 3
    <SPAN style="color:#00007F">For</SPAN> x = 0 <SPAN style="color:#00007F">To</SPAN> 3
    <SPAN style="color:#00007F">For</SPAN> y = 0 <SPAN style="color:#00007F">To</SPAN> 3
    <SPAN style="color:#00007F">For</SPAN> z = 0 <SPAN style="color:#00007F">To</SPAN> 3
    
        <SPAN style="color:#007F00">'Make sure all values are unique</SPAN>
        ValueString = i + 1 & j + 1 & k + 1 & l + 1 & m + 1 & n + 1
        <SPAN style="color:#00007F">For</SPAN> p = 1 <SPAN style="color:#00007F">To</SPAN> 6
            OldLen = Len(ValueString)
            NewLen = Len(WorksheetFunction.Substitute(ValueString, p, ""))
            <SPAN style="color:#00007F">If</SPAN> OldLen - NewLen > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> SkipValue
        <SPAN style="color:#00007F">Next</SPAN> p
        
        <SPAN style="color:#007F00">'Paste formula</SPAN>
        Cells(r, c) = "=" & ValueArray(i) & OperatorArray(v) & ValueArray(j) & OperatorArray(w) & ValueArray(k) _
             & OperatorArray(x) & ValueArray(l) & OperatorArray & ValueArray(m) & OperatorArray(z) & ValueArray
        
        <SPAN style="color:#007F00">'Starts a new column at the end of "A"</SPAN>
        <SPAN style="color:#00007F">If</SPAN> r < 65536 <SPAN style="color:#00007F">Then</SPAN>
            r = r + 1
        <SPAN style="color:#00007F">Else</SPAN>
            r = 1
            c = c + 1
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> z
    <SPAN style="color:#00007F">Next</SPAN> y
    <SPAN style="color:#00007F">Next</SPAN> x
    <SPAN style="color:#00007F">Next</SPAN> w
    <SPAN style="color:#00007F">Next</SPAN> v
    
SkipValue:

<SPAN style="color:#00007F">Next</SPAN> n
<SPAN style="color:#00007F">Next</SPAN> m
<SPAN style="color:#00007F">Next</SPAN> l
<SPAN style="color:#00007F">Next</SPAN> k
<SPAN style="color:#00007F">Next</SPAN> j
<SPAN style="color:#00007F">Next</SPAN> i

<SPAN style="color:#007F00">'EndTime = Timer</SPAN>

<SPAN style="color:#007F00">'MsgBox "Process took " & EndTime - StartTime</SPAN>

End <SPAN style="color:#00007F">Sub</SPAN></FONT>

Any experts have a faster way of doing this?

[edit] It's STILL running on my work PC... it's been a good 20 minutes, so if it's going to be useful at all, it's gotta work quite a bit faster...

I think the key might be to NOT paste anything into the cells, but how to cycle through operators? There are 1,024 combinations of 4 operators in 5 locations... [/edit]

Solved it. Works for 3, 4, 5, or 6 value combinations with 4 operators, and it allows for numbers to be used as often as they are in the list.

Most goals can be solved in under 3 minutes. If interested, let me know via PM.