Solve Equation in Excel (Table of Contents)
- Overview of Solve Equation in Excel
- How to Add the Solver Add-in Tool?
- Example on How to Solve Equations Using Solver Add-in Tool
Overview of Solve Equation in Excel
Excel help’s us out in many ways by making the task easier & simple. Solver Add-in tool is significant to perform or solve equations in excel. Sometimes we need to perform or carry out reverse calculations, where we need to calculate one or two variables to get the desired end results.
Example: For the profit of the extra 10%, how many units need to be sold or what is the exact marks needed in the last semester of final exams to get the distinction.
This above calculation or equations can be calculated with the help of Solver Add-in, with specific criteria.
Definition of Solve Equation in Excel
It is used to find out the optimal value of the target cell by changing values in cells which are used to calculate the target cell.
It contains the below-mentioned parameters.
- Target
- Variables
- Constraints
- Formula to use to calculate
How to Add the Solver Add-in Tool?
Let’s check out how to add the solver add-in tool in excel. Calculation or equations can be calculated with the help of Solver Add-in, with specific criteria.
To add the Solver Add-in tool, the below-mentioned procedure, is followed:
- Click on File option or an Office Button, then you need click on Excel Options.
- Now, the Excel Options window dialog box appears, under Add-ins, select Solver Add-in in the inactive application add-ins list and “Go”
- Add-ins window appears where you can see the list of active add-ins options. Tick the Solver Add-in and click on the “Ok” button.
Now, you can observe, Solver Add-in got added to the Excel sheet as Solver under the “Data” tab on the extreme right side.
Example on How to Solve Equations Using Solver Add-in Tool
To calculate Variable Values For % Profit Maximization with the help of the Solver Add-in Tool.
In the above-mentioned table, the monthly sales data of price per unit, containing Cost or stockiest Price per unit & Selling Price per unit to customers. Now, I have April & May month with percentage profit for each unit i.e. 13.33% & 15.38% respectively.
Here, B4 & C4 is the percentage profit for the month of April & May 2019, which is calculated with the help of the below-mentioned formula.
Formula to Find out Percentage Profit:
((Selling price per unit – Stockist price per unit)/ Stockist price per unit) *100
Variables (B2, B3 & C2, C3): Here variables are Cost or stockiest Price per unit & Selling Price per unit to customers, which keeps on changing month on month.
Target & Constraints
Now, my target is to take the percentage profit (%) per unit to 20%. So, for that, I need to find out Cost or stockiest Price per unit & Selling Price per unit to customers needed to achieve a profit of 20%.
- Target Cell: D4 (Profit %) should give a 20% profit
- Variable Cells: C2 (Cost or stockiest Price per unit) and C3 (Selling Price per unit to customers)
- Constraints: D2 should be >= 16,000 and D3 should be <= 20,000
Formula to Find out Percentage Profit:
((Selling price per unit – Stockist price per unit)/ Stockist price per unit) *100
i.e. ((D3-D2)/D2) *100
Prior to usage of the solver add-in tool, we need to enter the profit calculator formula ((D3-D2)/D2) *100 in the target cell (D4) to calculate the 20 % profit.
It is the significant information which is required to solve any sort of equation using Solver Add-in in Excel. Now, select a cell D4, and I need to launch the Solver Add-in by clicking on the Data tab and select a Solver.
Once solver is selected, Solver parameter window appears, where you need mention the “Target Cell” as “D4” cell reference in set objective text box and select a radio button as “Value of”, In the text box of it set the targeted profit as 20 %
In “By changing variable cells”, select the range of D2 (Cost or stockiest Price per unit) and D3 (Selling Price per unit to customers) cell where it is mentioned as $D$2:$D$3 in the text box.
After addition of changing variable cell range, we need to add constraints, it is added by clicking on add under the subject to the constraints.
Now, the first parameter of Constraints is added by inputting the cell reference & constraint value i.e. Cost price or stockiest Price per unit which is either more than or equal to 16,000 (>=16000)
Now it gets reflected under Subject to the Constraints box, again we need to click on add to add one more constraint i.e. Selling Price per unit to customers, it is added by inputting the cell reference & constraint value, which is either less than or equal to 20,000 (<=20000)
Now, we added all the parameters, just we need to click on solve.
It will ask, whether you want to keep the solver solution along with original values, you can Select these options based on your requirement, here, in this scenario I have selected Keep Solver Solution and click on “Ok” button.
Now, you will observe a change in the value in the cell D2 (Cost or stockiest Price per unit) and D3 (Selling Price per unit to customers) to 16000 and 19200 respectively to get the 20% Profit
Things to Remember About Solve Equation in Excel
Most of the third party excel add-in program is available, that provides to solve equations & data analysis tools for statistical, financial and engineering data and Other tools & function which are used to solve equations in excel are:
- What-If Analysis: It is also used to solve equations & data analysis wherein it allows you to try out different values (scenarios) for formulas to get the desired output.
- Goal Seek: It is an inbuilt function in excel under What-If Analysis which help us to solve equations where it will source cell values until the desired output is achieved.
Recommended Articles
This is a guide to Solve Equation in Excel. Here we discuss how to add the Solver Add-in Tool and how to Solve equations with Solver Add-in Tool in excel. You can also go through our other suggested articles to learn more –
