To reach a Value (or closest to it) with all 14 elements

[sasamaribor]

Only addition and subtraction operations (+ and -) are available among 14 chosen elements.

The task is to reach chosen Value (or closest value with minimal difference to it) with all 14 chosen elements (natural numbers), always written in column from smallest to largest.

Minimum difference: this is the one that is the lowest possible.
This does not mean that the difference is no more than 1 to the selected value (which should be approached).
It can be any, but the lowest possible (reached with 14 natural numbers) difference to the selected value, if an accurate result is not found.

Value to reach and 14 natural numbers are INPUT, and all can be large.

Possible to solve in Excel 2010 (write a macro)?

Thank you very much.

Kind regards,

Sasa

[kaasplankje]

Please try this. It's pretty neat, working with the Dec2Bin instruction (here in a slightly modified form to allow for numbers exceeding 512) to systematically scan binary sequences of 14 digits long to probe all possibilities of addition (1) or subtraction (0). Furthermore, I have made it dynamic to deal with different target numbers and 14 freely adjustable integers. Note that there might be multiple answers but that it stops at the first hit.

Please Login or Register  to view this content.

Sample file here: Value to reach.xlsm
Cheers.

[sasamaribor]

Hello,

In example I attached it is: 1+2+3-4+5-6+7+8-9-10+11+12-13+14 = 21

I have just opened your xlsm file. I do not see nothing there. Just changed + and - in green column. But such order do not give result 25 with all 14 elements.

So i do not understand that file you have sent me.

I am not programmer.

What should i do with "program" you attach in picture above? Write all that in Excel 2010 in cells? I supose not, hm...

Thank you very much. I appreciate your efforts.

Sasa

[kaasplankje]

Youi must run the macro. This will fill the correct pluses and minuses and show the best possible answer.

[sasamaribor]

How I run the macro? I do not see any button.

[kaasplankje]

Well, yes that could be possible as well. But please introduce yourself to the vba editor first and follow these steps: https://support.microsoft.com/en-us/...3-50e645fe3155

[sasamaribor]

Hello, I manage to do.

Perhaps we did not understand ourselves about the minimum difference. This is the one that, with 14 natural numbers selected, is the lowest possible. This does not mean that the difference is no more than 1 to the selected value (which should be approached). It can be any, but the lowest possible difference to the selected value, if an accurate result is not found.

Is it possible to change something to a macro so that any natural number can be entered?

There are a maximum of 14 natural numbers, but they can be large.

The same applies to the number we want to approach it, it can be a large natural number.

Thank you very very much!

[Hydraulics]

Here is a solution based on Solver. You may need to load the Add-In first.

Once it is available, just run it. Variables and constraints are in columns L-N.

HTH,

Francesco

[kaasplankje]

Sorry but it seems you are asking for a finger and then try to take my hand. Please get yourself familiarized with macros and try yourself. You now have two good answers to play with.