Possibly linear of Non-linear equation solution?

Hello,

I would be grateful for some help to set up some calculations in excel, which I believe may require some kind of equation, but I am unsure where to start. I have unknowns a, b & c, which must all equal the same value, but each can only use certain numbers as shown below. As there are no resources, like in a normal linear equation, I assume that the numbers are being treated as if they are the resource.

Set A: 3; 3; 13; (must use a MIN of 2 numbers)
Set B: -1; 1; 3; 4; 11; 15 (must use a MIN of 2 numbers)
Set C: (must use at least 1 number each from set A & set B)
A > 17 (and is an even number. If the > was an even number the result would have to be odd).
A=B=C
You are looking for the MIN value that gives the result. You can use add; minus; divide or multiply, but if the value you start with is a minus, you can only deduct it. Each set can start with a set of 2 to 8 or more numbers. The answer to this one is 22 (Set A: (3X3=9)+13=22; Set B: 15+11-3-1=22), but I need some kind of calculator where I can do this quickly.

Thank you

First, this is a very difficult problem that requires much more than "some kind of equation." IMHO will require VBA programming to yield a definitive solution. (I don't know if Solver would work, and if so would require a complicated setup.) It will have to iterate through all possible expressions, and it will probably take a while to execute.

I think the terms "linear" and "non-linear" in your title are misleading. You are not trying to solve a system of equations for unknowns. This is more like an arithmetic puzzle where you are trying to arrange several constants using various operations into an expression that equals another constant.

Your description of a sample solution describes Set A and Set B but does not address Set C. What is the solution for Set C?

You do not say what the rules area for forming an expression. What operations are allowed? Are parentheses allowed?

Describing this as having three unknowns is unnecessarily complicated. You really have one unknown.

If we allow four basic operators (+, -, *, /) then a set of three numbers would yield something like 200 different possible expressions (if you use all three). As you add more values, this number gets much, much larger. Set B would have over 7 million possible expressions if you use all the numbers. I haven't tried to calculate all the different possibilities if you select 2, 3, 4, or 5.


How are these numbers and the ">" number chosen? Is it guaranteed that a solution exists?

Thanks for the response 6Stringazzer.

Set C must use the values from set A & set B, but must use at least one from each. 13+(1+11-3=9)=22.
As the answer in the example given is the MIN even value >17 that all 3 sets can post the same result for, I don't think the calculator should need to go through all possibilities to come up with the result. The four basic operators are the only ones used.

The numbers are generated by an app. It does not calculate the answers. I have only seen one situation where one of the set of numbers could not generate the same result as the others, and that just meant moving on to the next set of numbers.

Originally Posted by SamJay

I don't think the calculator should need to go through all possibilities to come up with the result.

Then how would you do it? If you can provide the algorithm, we can show how to implement it in Excel.

I should have added that the result should probably never be >100, and in most instances would never be > 70. As I am searching for the MIN even result >17, I would have thought the calculator could be set up to calculate the lowest even result for Set A, B or C, then check if the other two sets can come to the same result using their values, and if not, move on to the next lowest even result etc. So there should not be a need to calculate all the possible results.

Hello MrShorty,

A manual brute force test is what I have been using to date.

Thank you.

Originally Posted by SamJay

I would have thought the calculator could be set up to calculate the lowest even result for Set A, B or C, then check if the other two sets can come to the same result using their values, and if not, move on to the next lowest even result etc. So there should not be a need to calculate all the possible results.

Here is the problem. It might be possible to do this without calculating all possible results, but you know don't which results you are interested in unless you do that.

It is not as simple as it sounds to calculate the lowest even result for Set A, when the number of values, the values themselves, and the four operations, all vary. I suppose you could do things like determine the two lowest numbers that are either both even or both odd, then subtract the lower one from the higher one. But that isn't guaranteed to be the lowest even result! You would also have to look for the two lowest numbers such that the lower one evenly divides the higher one by an even result. These are the kind of heuristics you would have to develop to systemically find the lowest even number if you don't want to use brute force.

For example, let's say we have -3, 1, 5, 10

5 - 1 = 4. That seems pretty low. Is that the lowest even number we can get?
Well, 10/5 = 2, and that is lower.

But you also showed an example with a negative number, and things get really interesting now. So
-3 - 5 = -8 that's even lower.

But
-3 * 10 = -30 and that's even lower. But wait:

-3 * 10 * 5 = -150 and I think that is the lowest even number possible in this set.

I would not know how to write logic that could do that the same way that a human just did it.

This is a fairly straightforward problem to solve by brute force, but very difficult to optimize. It would be a good master's thesis for a computer science grad student.

calculate the lowest even result for Set A

This is akin to saying, "It should be fairly easy to fly, just stick out your wings and flap."

I see what you are saying. I should have said the result needs to be positive. I will keep using the brute force method for now, and when I have time see if I can get a machine learning tool to do the calculations.

Thank you.

This reminds me of a similar arithmetic puzzle, i.e. using a single number four times plus the arithmetic operators + - * / and parentheses where necessary, generate the numbers from 1 to 20.

So, for example, if 5 is the number:

1 = 55 / 55

2 = 5 / 5 + 5 / 5

3 = (5 + 5 + 5)/ 5

and so on. You can try it with other numbers (note, this is not something I've ever tried within Excel).

Happy New Year.

Pete

each can only use certain numbers

What is the maximum number of values that will be provided in each set? If I get some spare time I could look at the brute force VBA solution.

I am assuming that each number in each set may be used only once.