How to find the math relationship/graph between one or more variables...

[kalos]

Hello!


I have a complex model in excel. Among other parameters which I want to keep constant, I want to find the effect of each of four cells that contain variables (with some constrains) to a cell that is the result cell.

I want to find the optimum (minimum) four values to achieve the result I want and also find which of these four variables has the most significant effect on the result cell.

To give you some context, there are four teams that work in a very complex way and the four input cells are their employee number. The output cell has the end date of the project that is calculated with very complex way. How can I find which team is more worth to hire and how many?

Any idea? I am open to any freeware tool, etc.

Thanks!

[KodeZwerg]

Progress divide by HoursOfWork multiply Salary (for each Team)
Maby that?

[Attronarch]

To understand which variable has the highest impact you can use regression analysis and ANOVA.

Assuming it is a linear process you can use linear programming for optimization. If not, you can look at other mathematical optimization models.

If I remember correctly, Excel has built-in solver for linear programming but it has to be activated.

[kalos]

To understand which variable has the highest impact you can use regression analysis and ANOVA.

Assuming it is a linear process you can use linear programming for optimization. If not, you can look at other mathematical optimization models.

If I remember correctly, Excel has built-in solver for linear programming but it has to be activated.

Yes, I used Solver but it is very limited.
Is there a better tool?

[Attronarch]

Yes, MiniZinc.

Here is a tutorial for using it to solve a LP problem:
https://brilliant.org/wiki/discrete-optimization-modelling-with-minizinc/

And two courses to dig deeper:
- Basic Modeling for Discrete Optimization
- Advanced Modeling for Discrete Optimization

[kalos]

Yes, MiniZinc.

Here is a tutorial for using it to solve a LP problem:
https://brilliant.org/wiki/discrete-optimization-modelling-with-minizinc/

And two courses to dig deeper:
- Basic Modeling for Discrete Optimization
- Advanced Modeling for Discrete Optimization

Thanks that looks very interesting!
Does it work within excel or can I copy/paste my excel model? Or is there anything similar that will work within Excel? I tried Solver but it doesn't seem to work
If I have to build it from scratch using that language it will be a nightmare!