find math expression with given elements and results

[Fable09]

Hi everyone,

How can this be solved in VBA

given S is a result and some number a b c d e f g

So how can i find the combination of math expression ( plus/ minus ) so that

a + b + c - d -e -f + g = S

or a - b +c - d - e +f - g = S

Thanks,

 

[Joe4]

I am not quite clear on what your expected outcome is.
Are you saying that you are given number a,b,c,d,e,f,g, and S, and one of the two equations you listed will be true, and if so, you want to know which of those formulas is true?
Or are you looking for something else?

It may be best to show us an actual example with your expected outcome.

 

[Eric W]

This seems related to a puzzle I've seen. Given the digits from 1-9, put operators (or nothing) between each digit to get a given result. For example, if you want to get a result of 2019, the answer would be:

1 + 2345 - 6 * 7 * 8 + 9 = 2019

In this version, the standard order of operations is used, although I've also seen it where it just expects the operations to be performed from left to right in order. I've written a macro that solves problems like this for arbitrary sums, and selectable operators. It would take a bit to make it work with arbitrary values as well.

 

[Fable09]

I do a lot of accounting reconciliation work so i'm looking for the vba code to work with this problem



[TABLE="width: 500"]
<tbody>[TR]
[TD]Example [/TD]
[TD]Given[/TD]
[TD]After running Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD]11[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]22[/TD]
[TD]-22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]33[/TD]
[TD]-33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]44[/TD]
[TD]-44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]55[/TD]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]66[/TD]
[TD]-66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD]77[/TD]
[TD]-77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]-176[/TD]
[TD]-176[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see , some how the macro calculate all possible cases and assign the PLUS or MINUS sign in front of each elements ( from a to g ) to get the given result -176

It would be great if you could help me using VBA to solve this

 

[pgc01]

Hi

I can't do this now, but just a remark: you may have more than 1 solution, like:

11 + 22 + 33 - 44 - 55 - 66 - 77 = -176
11 - 22 - 33 - 44 + 55 - 66 - 77 = -176
-11 + 22 - 33 + 44 - 55 - 66 - 77 = -176
-11 - 22 - 33 - 44 - 55 + 66 - 77 = -176

Not sure if it's important to your case.

 

[Eric W]

You can try the Solver. If you don't have it installed, click File > Options > Add-ins > Go (next to Excel Add-ins on the bottom) > and check the Solver box. Then set up your sheet like this:

Excel 2012

	A	B	C	D
Item	Value	Sign	Work
a
b
c
d
e
f
g
S

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]44[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]-308[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(D2=0,-1,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=SUMPRODUCT(B2:B8,C2:C8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Drag the C2 formula down to C8.

Then go to the Data tab, click Solver (far right), and enter these parameters:

Set Objective: $B$11
To: Value of: -176
By changing variable cells: $D$2:$D$8
Subject to the constraints: $D$2:$D$8 = binary
Solving method: Evolutionary

and click Solve. It'll ponder for a minute or so and hopefully come up with an answer.

Like pgc01 said, there are multiple solutions. And if you have a lot of values, this could take a long, long time. And if you want to include the option of not adding in a value at all, it gets even more complicated.

 

[shg]

If you don't have your heart set on a macro,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

11​

[/td][td]

22​

[/td][td]

33​

[/td][td]

44​

[/td][td]

55​

[/td][td]

66​

[/td][td]

77​

[/td][td]

176​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#F3F3F3"]

a​

[/td][td="bgcolor:#F3F3F3"]

b​

[/td][td="bgcolor:#F3F3F3"]

c​

[/td][td="bgcolor:#F3F3F3"]

d​

[/td][td="bgcolor:#F3F3F3"]

e​

[/td][td="bgcolor:#F3F3F3"]

f​

[/td][td="bgcolor:#F3F3F3"]

g​

[/td][td="bgcolor:#F3F3F3"]

Sum​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

+1​

[/td][td]

-1​

[/td][td]

-176​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

44​

[/td][td]

-1​

[/td][td]

+1​

[/td][td]

-1​

[/td][td]

+1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-176​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

72​

[/td][td]

+1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

+1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-176​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

116​

[/td][td]

+1​

[/td][td]

+1​

[/td][td]

+1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-176​

[/td][/tr]
[/table]


In A4 and copied across and down,

=2*MOD(INT(2*(ROWS($G$4:Me) - 1) / 2 ^ COLUMNS($G$4:Me)), 2) - 1

In H4 and copied down,

=SUMPRODUCT($A$1:$G$1, A4:G4)

Filter col H for the desired sum.

 

[Fable09]

Thanks everyone for the answers. I probably need some time to digest the solutions

@shg: $G$4:Me , would you please help me understand where is range "Me" from the example ?

 

[shg]

Apologies, it's a named range that refers to the cell in which it appears. Change the formula in A4 to


=2*MOD(INT(2*(ROWS($G$4:A4) - 1) / 2 ^ COLUMNS($G$4:A4)), 2) - 1