Did you miss your activation email?

• Friday April 12, 2024, 8:03 pm
• Proudly celebrating 15+ years online.
• Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.

### Author Topic: How to find the math relationship/graph between one or more variables...  (Read 4384 times)

#### kalos

• Member
• Joined in 2006
• Posts: 1,823
##### How to find the math relationship/graph between one or more variables...
« on: November 01, 2018, 04:55 AM »
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

• Honorary Member
• Joined in 2018
• Posts: 718
##### Re: How to find the math relationship/graph between one or more variables...
« Reply #1 on: November 01, 2018, 07:55 AM »
Progress divide by HoursOfWork multiply Salary (for each Team)
Maby that?

#### Attronarch

• Supporting Member
• Joined in 2012
• Posts: 147
##### Re: How to find the math relationship/graph between one or more variables...
« Reply #2 on: November 01, 2018, 02:01 PM »
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

• Member
• Joined in 2006
• Posts: 1,823
##### Re: How to find the math relationship/graph between one or more variables...
« Reply #3 on: November 02, 2018, 05:50 AM »
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.
-Attronarch

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

#### Attronarch

• Supporting Member
• Joined in 2012
• Posts: 147
##### Re: How to find the math relationship/graph between one or more variables...
« Reply #4 on: November 03, 2018, 02:15 PM »
Yes, MiniZinc.

Here is a tutorial for using it to solve a LP problem:
https://brilliant.or...lling-with-minizinc/

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

#### kalos

• Member
• Joined in 2006
• Posts: 1,823
##### Re: How to find the math relationship/graph between one or more variables...
« Reply #5 on: November 06, 2018, 03:33 AM »
Yes, MiniZinc.

Here is a tutorial for using it to solve a LP problem:
https://brilliant.or...lling-with-minizinc/

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

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!
« Last Edit: November 06, 2018, 03:55 AM by kalos »