Solver acts as a specialized tool that you can incorporate into programs like Excel, functioning as an add-in. Its primary purpose is to solve intricate problems by employing mathematical methods to find the best solution. Solver is particularly adept at resolving linear programming problems, which involve maximizing or minimizing a value while adhering to certain constraints. This capability has led some individuals to refer to it as a linear programming solver. However, its utility extends beyond linear problems; it can also address other types of challenges involving curves and complex scenarios. Thus, whether your problem is straightforward or intricate, Excel Solver is equipped to lend assistance.
In this method, we aim to minimize the value of ()=3+32−5 f ( x ) = x 3 + 3 x 2 − 5 without any constraints.
The value of x in cell C4 is converted to 0 and the minimum value of f(x) is achieved.
Here, we introduce constraints to the optimization process using Solver.
To explain this method, we have 2300 cm 2 of metal sheet in our hand. From this, we have to make a cylinder with a Height double its Radius. We have to optimize the system so that the Volume gets maximized.
In the Solver Parameters dialog box:
The maximum achievable Volume with this area of the metal sheet is visible in cell C7 and its Height and Radius are also available.
In this scenario, we maximize profit by determining the quantity of each product to be produced while considering resource constraints.
Now, we’ll calculate the quantity of each product to be produced using the given resources. Our prime objective is to maximize the profit from this production.
The SUMPRODUCT function returns the sum of the products of the corresponding values from all the arrays.
In the Solver Parameters dialog box:
At a glance, Excel computed the quantity of products, utilized resources, and the maximum total profit.
Our aim is to maximize the profit while considering inventory and capital constraints in a retail setting.
Calculate the total unit of products, total cost, inventory, and profit:
The SUM function adds all the numbers in each range of cells.
Similarly, the total inventory and total profit is calculated in cells E9 and F9.
In the Solver Parameters dialog box:
So, we can see that, the only way to make the most profit is by buying ACs. However, the inventory needs to be expanded as only one-third of the capital can be spent.
We should meet the demand of each store and make the logistics cost the bare minimum.
Calculate the total shipped products from each storage and received products for each store.
In the Solver Parameters dialog box,
Our optimization in Excel is done.
What is Magic Square?
A magic square is like a puzzle that consists of a square grid. You can imagine it as a square box divided into smaller boxes or cells. Each cell in the grid contains a different number, usually a whole number.
The remarkable thing about a magic square is that if you add up the numbers in any row (going from left to right), any column (going from top to bottom), or either of the two main diagonals (the diagonal lines that go from one corner of the square to the opposite corner), the total will always be the same.
It’s like magic because the sum will always be equal no matter which row, column, or diagonal you choose.
How to Create Magic Square
Here, we opt to create a 4*4 magic square:
For a 4*4 magic square, the sum for any columns or rows has to be,
Sum = (n * (n^2 + 1)) / 2Here, n represents the size of the magic square, which is the number of cells in each row or column.
In the Solver Parameters dialog box, we inserted the following constraints as in the image below:
Magically, Excel created the magic square. You can apply the same procedure for magic squares of any dimension.
Different solving methods in Excel Solver cater to various problem types:
GRG Nonlinear: This method utilizes the Generalized Reduced Gradient (GRG) Nonlinear algorithm. It is specifically designed for tackling problems that involve smooth nonlinear functions. In other words, if your problem includes at least one constraint that is a smooth nonlinear function of the decision variables, this method can be useful.
LP Simplex: The LP Simplex method is based on the Simplex algorithm, which was developed by an American mathematician named George Dantzig. It is employed for solving Linear Programming (LP) problems. Linear Programming deals with mathematical models where the requirements can be described by linear relationships. These models usually consist of a single objective represented by a linear equation that needs to be either maximized or minimized.
Evolutionary: The Evolutionary method is suited for non-smooth problems, which are generally the most challenging type of optimization problems to solve. Non-smooth problems often involve functions that are not smooth or even discontinuous. In such cases, determining the direction in which a function is increasing or decreasing becomes difficult. The Evolutionary method is tailored to handle these complex optimization problems.
By selecting the appropriate solving method based on the nature of your problem, you can leverage the power of Excel Solver to find optimal solutions efficiently.
1. What is the objective function in optimization?
The objective function represents the quantity to maximize or minimize in optimization.
2. What are the constraints in optimization?
Constraints limit the feasible solutions in an optimization problem.
3. Can Excel Solver handle large-scale optimization problems?
While Excel Solver can handle moderately large problems, specialized software may be needed for very large-scale or complex problems.
You can download the practice workbook from here:
