Solver Or Vba ?!

  • Hi, everybody,


    I was asked to find a way to display the combination of ALL four numbers: 1 5 6 7 in order to get 21 as the result.


    Every number can be used only once !


    All mathematical operators are welcome.


    for example: 6 / (1 - 5/7) = 21


    BUT... how can this formula be "found" with the help of Excel ?


    Thanks for your help,
    Michael

  • "All mathematical operators are welcome." :yikes:


    You could quite easily use Goal Seek, but you would need to manually set-up the mathematical operators etc.


    I'm afraid I will have to leave this to someone else :hubbahub:

  • i'm someone else


    I'm someone else.



    The only way I can see that this would be possible is to hard code every single possibility and then generate the results.


    If four values are used, and four operators are also used (+, -, *, /) then there would be a total of (4 x 4 x 4 x 4) + (4 x 4 x 4 x 4) permutations.


    This results in a case statement with 512 possibilities.


    Should take you the majority of the morning to finish.

  • Thanks for your reply . . .


    I was thinking/looking for somethig "automatic" and not hand-typing...


    Maybe in VBA some nested loops or so...


    Cannot think about the code which will do the task.


    Will wait for more replies,
    Michael

  • code


    well, if you think about it.. all you could do is code all the possibilities of operations, and then replace the values.



    for example:


    Function GenerateFormulas(Value1, Value2, Value3, Value4)

    Code
    Value1 + Value2 + Value3 + Value4
    Value1 + Value2 + Value3 - Value4
    Value1 + Value2 + Value3 * Value4 
    Etc...


    end function


    Then you would have a function call where you can use whatever values you want.


    That would still result in a lot of typing , just half though.
    Regardless, some typing will need to be done.

  • there is another way, although it is not trivial... :)


    if you know reverse polish notation (RPN; used by some calculators) you could create a stack ADT and create an array of the legal operators.


    then create a permutation function


    then pump the operators and the numbers into the permutation function, and the result onto the stack.


    pop the stack RPN style to form an expression and if it validates to the correct result, interpret the combination that gave you the answer and bingo!


    :roll:


    no, i'm not going to do this for you.
    i may do it as an exercise for myself in the future as i have a stack and a permutation implemention.


    excuse my thinking out loud.

  • and in the "Future" will you share it with me... ?!


    as I am not taking or dealing with hypothetical situations...


    I can wait...


    what about using "Recursion" ?!


    Unfortunately I am not familiar with "Recursion" - but as for now, all I get are "DRY" advices also from other "Excel" forums.


    Michael

  • well permutation implementations typically involve recursion.


    i might have a look at knocking this up tomorrow since i already have some tools to help.


    do you happen to have a file with a sample data set that i can use to test?

  • No, unfortunately, I have not.


    I will wait and appreciate every help.


    Please use the above mentioned numbers ( 1, 5, 6, 7 ) in order to get 21 as the result.


    again, thanks in advance,
    Michael

  • Not much to add, but keep in mind that the operators "(" and ")" cannot be used like numbers in the permutations, because one without the other, or in a different order, is not acceptable.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Quote from shades

    Not much to add, but keep in mind that the operators "(" and ")" cannot be used like numbers in the permutations, because one without the other, or in a different order, is not acceptable.


    interesting.


    i guess also that the numbers must have AT LEAST one operator between them, ie not 65*1 type thing.


    and, the operators can be used multiple times (stating the obvious for myself, this adds complexity to the permutation).

  • Quote from Michael Avidan

    isn't what U just said the BASICS of Algebra ?


    Sometimes as the complexity of the problem increases, the basics are forgotten, and so reminders can be helpful. But maybe that's just me.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • after thinking about it a little overnight, i thought it may be good to restate the rules as i understand them:


    - the 4 integers cannot be combined (ie {1,5,6,7} --> {15,67})
    - ALL 4 integers must be in the expression
    - no integer can be used more than once in the expression
    - there may only be one operator at the beginning of the expression: "("
    - as above, there may only be one operator at the end of the expression: ")"
    - there may be a minimum of 1 operator between integers, and a maximum of two (this is important for the next rule). if anyone can tell me how there would be more, please do
    - unless the integer is already negative, it may not be coerced; this makes "+-", "*-", "/-", "(-" combinations of operators illegal
    - therefore maximum expression length is "(" + a + "??" + b + "??" + c + "??" + d + ")"


    hope i didn't miss anything.


    let me know if there are any problems with the above.


  • Hi Michael


    Please understand that what you are asking it way beyond the norm. If it did not invlove all possible mathematical operators, it would be far easier (Goal Seek etc). By adding all possible mathematical operators into the mix, it increases the possible combinations by a HUGE amount.


    Quote

    all I get are "DRY" advices also from other "Excel" forums.


    If this is a cross post I suggest you read below taken from My Question is Not Being Answered. Why?


    You have cross-posted. This is when you post the same question on other forums, newsgroups etc. This will be wasting the time of the kind volunteers as they could well be trying solve a problem that has been solved elsewhere. This is like ringing 5 different cab companies, jumping in the first that shows and not caring less about the other 4.

  • after considering some of the rules (basic algebra...) i decided against the perm/stack/rpn approach.


    i would appreciate some more data sets to test if you have any.


    currently my function tests ~486,000 expressions according to the above rules and finds 1 basic formula that generates the result (similar to the first post, with some variation in bracket placements).


    long live brute force.

  • i have the brute force solution working but there are a few things to bear in mind:


    1. it aint pretty (whaddya expect?)
    2. may be slow on older machines, my 1G PIVM does ~5000/sec
    3. it is specific to the question asked above, ie will not do <4 or >4 inputs; integers only
    4. expressions generated will be variations on the same thing, i didn't bother filtering unnecessary brackets, you can do that if you want
    5. there is bugger-all error checking/handling so if you break it, you bought it.


    cheers,
    Paul.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!