Step 6: The first thing we need to identify is Se t Objective: Since our objective is to figure out the total cost involved so that it can be minimized, set this to D4. Step 5: Once you click on Solver, a new tab named “Solver Parameter” will open up, under which you need to set the parameters for this set of the equation to be solved.

Step 4: Click on Data tab and then on Solver which is present under Analyze section in the tab.

These quantities will appear once the system of equations is solved using Excel Solver. This is because B3 and C3 are the cells denoting quantities for Product A and Product B, respectively. If you will have a closer look at this formula, we have used B3 and C3 as fixed members for each formula across the different cells in column D. Please see the attached screenshot below: You can see this formulated under column D for all cells containing constraints B3, B4, C3). Step 3: Now, we need to use the formula Quantity * Per Unit Cost and sum it up for both the products to get the actual material requirements. Step 2: Use these equations to add the constraints cell-wise under Excel across A2:C8 of the given sheet. Step 1: We can see all the equation constraints we can form using the information provided in the example above. If the per-unit cost for Product A is $30 and the cost of Product B is $35, how many products the manufacturer should blend to meet the minimum material requirements at the lowest cost possible? Let’s use the information provided under this example for modelling the equations. The manufacturer requires a minimum of 460KG or Material_1, 960KG of Material_2 and 220KG of Material_3. On similar lines, Product B requires 10KG of Material_1, 30 KG of Material_2 and 10 KG of Material_3. These two products need raw materials as shown below: Product A needs three types of raw materials – Material_1 20KG, Material_2 30KG, Material_3 as 5 KG. Now, we will try to solve the linear programming problem using the Excel Solver tool.Įxample: A chemical plant produces two product:: A. Method #2 – Solving Linear Programming Problem using Excel Solver This way, you can enable the Excel Solver under Microsoft Excel. Click the OK button after tick selecting the Solver Add-in.

Tick to select the Solver Add-in so that you can use it under the Data tab for solving the equations. Step 4: As soon as you click on Go… button, you will be able to see the list of all Add-ins available under excel in a new window. Step 3: Under the Manage section at the bottom of the window, select Excel Add-ins from the dropdown list and click on Go… button placed beside.

Excel solver function numerically