Solver: minimum value of two variables that result in x

Hello once again!

I have the following equation (changed the cells' IDs to letters):

=((1+(A/100))*((B*((C+1)/4)*(1+(D/50))+(D*10))+80))-E

B, C and E are fixed numbers.
A and D are variables.

I need the equation to be as close to 0 (zero) as possible.

However, I need the MINIMUM combination of A and D (basically, the smaller A AND D) that would make it 0, and not ANY combination.

How I've set up the solver so far:
Set Objective: the formula cell
To Value Of: 0
By Changing Variable Cells: A and D

Constraints:

A and D = integer
A and D >= 0
Formula Cell should be >=0

I can't solve, as it return the following error:

Error in model. Please verify that all cells and Constraints are valid.
Perhaps some cells that are not Variable Cells are marked as Integer, Binary or AllDifferent.

So I am asking myself... would the solver provide me the minimum combination of A and D that will make by formula = 0, or is it trying to give me anything that makes formula = 0 and, as many combinations would be possible, it is returning me an arror?

Any hint?

Thank you in advance!

EDIT 1: Solved the error by unmerging the variable cells. But the solver can't find a feasible solution.

EDIT 2: Changed from "To Value Of: 0" to Minimum and set another constraint of Formula >=0. It returns me a solution. But the question remains: is it the minimum value of A and D or just anything that solves my problem?

1) What are allowed ranges (min and max values) for A and D (variables)?
2) What are typical values and what ranges for B C D (constants).
3) As you use excel 365 have you tried also set Solver Solving Method to Evolutionary? (it sometimes helps with situations when there is more than one local minimum)
The equation seem to be a bit overcomplicated. I'd calculate first another constant value based on B and C: F=B*(C+1)/4
and then after some rearrangements your formula looks like: =(1+A/100)*(D*(F/50+10)+F+80)-E
so 2 variables, 2 constants

It could be further transformed to
G = F/50+10 = B*(C+1)/200 +10
H = F+80 = B*(C+1)/4+80

=(1+A/100)*(D*G+H)-E
(I left A as it is because it needs to be an integer value

If you want both A and D be as small as possible then typical condition would be not just A+D = min, but A^2 + D^2 = min

This way we promote small values in both variables may be 500A and 100D 480A 110D is not a perfect example because 480 110 is obviously better .
but lets look at
D A A+D A^2+D^2
500 100 600 260000
480 110 590 242500
480 130 610 247300


of course 480 110 is the best but 480 130 is better than 500 100 if we use squared values

By the way - if you attach sample file with your solver setup it could be easier to help you (see yellow banner near the top of the page).

Hmmm that's actually a good idea. I've tried to make it but idk if I'm in the right direction.

I've attached my file, I'd appreciate if could take a look. Thank you in advance!

EDIT: When I was laying on my bed, about to sleep, I got a spark in my head and realized that I made A^D at some point, instead of (A^2)+(D^2) xD

A and D does not necessarily need to be an integer. When I removed this constraint, the solver could find a solution. I can just round up A and D.

The file is now updated (also had to fix the formula a bit). I seems to be working... what do you think?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Yes, the solution seems to be stable. Especially if we take sum of squares as a measure.

If we take just sum (not squared), the minimum seems to be
859.01
807.63
so close to the values obtained for sum of squares.


On the other hand, if we try to minimize

Formula:

Please Login or Register  to view this content.

To follow your observation from post no. 3 that

A is about 2,7 times greater than D

we got:
1331.64
496.84

Where A/D is ca. 2.68


So no simple answer. It all depends on a measure we use :-).

But from the point of view of solver implementation - your file is fine.