Skip to main content

Changed values with Solver have to be the same for 2 out 3 cells



  • Official comment
    Brandon Takahashi

    Hi Jonathan,


    Thank you for submitting your request. To be perfectly honest I'm not 100% sure I fully understand, but I will do my best.

    My initial interpretation makes me believe this will be fairly straight forward. The Solver allows for Equality Constraints on cells, which can include variables and/or functions.

    To create an equality constraint where the right hand side will not be a constant, for example A = B where A and B are functions or variables, we want to enter into a cell =A - B, and set a constraint on that cell such that <cell> = 0. This creates the constraints A-B = 0 in a way Solver can calculate it fairly easily. As you can see from the constraint it is satisfied only when A = B.


    Taking your examples, lets assume we know how to calculate Flow3 and Flow1, and we want them to be equal. In an empty cell, let's say A1 for simplicity, in A1 we set the formula =Flow3 - Flow1. In the setup of your model you then set the constraint A1 = 0 and you're all set.


    Note if your Right hand side is a constant, for example we know before hand we want Flow3 and Flow1 to be =10 we can simple set a constraint on the cells containing the formula for Flow3 and Flow1 = 10.

    Please let us know here if this helps or if your request differs from what my initial interpretation is.



    Brandon T.

  • Jonathan Verhulst

    Hi Brandon,

    In the first place, many thanks for your quick response. I have created an excel file, which contains my current issue.
    I have created the model, but solver changes all of values with different numbers, incase the flows are the same, solver has to change the values with the same values.

    Next, I have to make more contraints that the pricing should be greater than that total costs per product, but that is an easy constaint, sales price >=total costs per product.
    Do you know how to cope with this?

    Kind regards,


  • Brandon Takahashi

    Hi Jonathan,


    Thank you for sending this, it does help clarify for me a bit now that I can see what you mean by Flow and what values you're changing.

    You mentioned the following: "incase the flows are the same, solver has to change the values with the same values." coupled with your original explanation, I believe my initial statement on how to handle this still stands.


    What you will want to do here is, in a new column (or row, if you prefer), create formulas that calculate the difference (A - B) in your "Sales price per piece" values for flows with the same name. Manually is probably easiest with a smaller model like this.

    So let's say cell E3 contains the Sales Price Per Piece of BeneluxProd, and E4 is the sales price for that same product/Flow Code. These are the values that are changing that we want Solver to solve for.

    In a new cell of your choosing, enter the formula =E3-E4. This is the difference in current sales prices for the same product.

    In the solver dialog box, you create a constraint on the cell (let's say we put it in J3) so that it's = 0. Specifically, J3 = 0.

    When you then solve the model with that constraint implemented, Solver will try to find a solution where the difference in Sales Price Per Piece for those two product entries is zero, meaning that are equal in value.

    You would then have to create the same difference formula and constraint for any other duplicates you may have in your actual model.


    Hope this helps! If you have any further followup questions please let us know. Unfortunately we don't have an example of a model trying to do something like this at this time, so do continue to let us know here if you have any follow up questions and we'll do our best to help out.



    Brandon T.


Please sign in to leave a comment.

Powered by Zendesk