Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

Solver in Excel

What is Solver?

Microsoft Excel facilitates an add-in programming tool known as Solver that uses operational research techniques to find the optimal solutions for objective problems. This tool operates with a group of cells (also known as decision variables), or only variable cells for calculating the formulas used in the cells. The Solver is used when you have a value in mind for your dependent variable, or you want to maximize the dependent value or minimize it (which is formula based). You want to change the values of the input variables to achieve that value. It is precisely the opposite of ‘What-If Analysis’. In other words, in ‘What-If Analysis’, we change the values of the input variables to see the impact on the dependent variable, over here, it’s the reverse of it.

Do note that Solver may not always be able to give you a solution as it may not be able to find one which achieves all the constraints (conditions) set by you.

Advantages of Solver

  1. The solver is a handy tool and can lead to minimizing the cost of inventory or maximizing the production of a product.
  2. Solver searches for optimal solutions for different types of decision-based problems.
  3. This method calculates the max or min value of a cell by changing the values of the corresponding cells.
  4. It satisfies all the restrictions on cells and generates the desired output for the objective cell.
  5. It helps you to change the amount of your predictable budget and evaluates the effect on your expected profit.
  6. It enhances the profit of the company or even to increase/decrease the number of alerts being generated by various risk strategies in a bank! 

Solver Parameters

Let’s load the Solver Add-in, and step by step look at its different parameters.

Solver in Excel
  1. Set Objective: This objective box is used to enter the cell reference for the objective cell wherein the specified cell must contain a formula. The objective can be maximized, minimized, or can be set to some user-defined value. 
  • To: This parameter is used to set the value for the objective cell.
  • MAX- To set the value of the objective cell to large.
  • MIN- To set the value of the objective cell to small.
  • VALUE- To set the value of the objective cell to the particular value specified by the user in the value bar (present at the right corner).
  • By Changing Variable Cells: This box parameter is used to enter the reference or name for each cell range decision variable. The data in the variable cells can be changed to achieve the output. The non-adjacent references are separated by using commas. The user can specify up to 200 variable cells.
  • Subject To The Constraints: This box parameter enables the user to enter the constraints that you want to apply. Solver Constraints are the restrictions or limitations of the feasible solutions of problems, and these are the conditions that must be satisfied by the solver. The subject constraints can be further added or edited by the following options:
  • Add- This option allows the user to add the constraints by selecting the cell reference edit box (present on the left side) and the Constraint edit box (present on the right side).
  • Change- This option is used to make some changes to the existing subject constraints.
  • Delete- If you want to delete any of your constraints, click on the Delete option.
  • Reset All- This option is used to delete all the available constraints and resets it to afresh.
  • Load/Save- If any of the constraints is used frequently so instead of writing it again and again you can load or save it for future use. Thus, it benefits in saving your time.
  • Select a Solving Method: This parameter is used to specify the Excel solver problem type. The user can choose one of the following methods:
  • GRG Nonlinear (Generalized Reduced Gradient Nonlinear)- This algorithm is used for functions which consists of at least one of the smooth non-linear constraints.
  • LP Simplex (Linear Programming)- This method is used to solve the problems carrying linear relationships.
  • Evolutionary- This approach is used for non-smooth difficult problems wherein it is difficult to evaluate the direction in which a function is increasing or decreasing.
  • Options: To use your own customized way of solving the problem, the ‘options’ parameter is used. It the help of this, you change the approach of the Solver to find a solution.  
  • Solve: Once you have entered all the parameters, click on the solve button. The excel solver will search for all the optional solutions of the specified problem and will display the result.
  • Close: This button is used to close the Solver parameters dialog box.

Below are the steps to use Solver –

What if in the previous example where ABC bought a car had in his mind that he will not be paying more than $1,400 installment per month – to achieve this, either he will buy a car which is less costly or he will pay more down-payment or he will increase the term of the loan or he will look for a better deal on the interest rates.

Let’s evaluate the step-step procedure for loading Solver add-in:

  1. For the Excel versions 2010 and above, the solver is not directly available. To enable the option:
  2. Click on File-> Options.
Solver in Excel
  • The Excel options dialog box will appear. Select the ‘Add-ins’ option. In the bottom, next to the manage bar select Excel Add-in and click on the ‘GO’ option.
Solver in Excel
  • The Add-ins dialog box appears. Tick on the Solver Add-in check box. Click on Ok. This will enable the solver option on your Excel Ribbon toolbar.
Solver in Excel
  • Enter the data in a sheet and go to ‘Data’ tab. Click on ‘Solver’ in the ‘Analyze’ section.
Solver in Excel
  • In ‘Set Objective’ click on the cell which you want to maximize, minimize or set to a particular value
  • In ‘By Changing Variable Cells’, you will have to enter all the input cells which you want to be changed – for ex: for a factory which produces 3 different products, we can change the number of units produced for each product to maximize profit
  • For ‘Subject to Constraints’, we can add constraints like overall production of the facility is less than a number 450 or each product production needs to be above a specified limit
  • Click on ‘Solve’.
Solver in Excel
  • Excel will give you the results but if it won’t be able to meet all the constraints, it will give you a prompt stating the same
Solver in Excel

On Row 18, Solver has changed the number of units for each product which were earlier 50 (each) on Row 4 to maximize the profit.