Solver in Excel
The Excel Goal Seek feature is a useful tool, but it clearly has limitations. It can solve for only one adjustable cell, and it returns only a single solution. Excel’s powerful Solver tool extends this concept by enabling you to do the following:
- Specify multiple adjustable cells
- Specify constraints on the values that the adjustable cells can have
- Generate a solution that maximizes or minimizes a particular worksheet cell
- Generate multiple solutions to a problem
No Solver Command? —– Tips.
You access Solver by choosing Data -> Analysis -> Solver. If this command isn’t available, you need to install the Solver add-in. It’s a simple process:
- Choose Office Button -> Excel Options.
- In the Excel Options dialog box, click the Add-Ins tab.
- At the bottom of the dialog box, select Excel Add-Ins from the Manage drop-down list and click Go. Excel displays its Add-Ins dialog box.
- In the Add-Ins dialog box, place a check mark next to Solver Add-In and click OK. After performing these steps, the Solver add-in loads whenever you start Excel.
Figure above shows a worksheet that is set up to calculate the profit for three products. Column B shows the number of units of each product. Column C shows the profit per unit for each product, and Column D contains formulas that calculate that total profit for each product by multipying the units by the profit per unit.
You don’t need an MBA degree to realize that the greatest profit comes from Product C. Therefore, in order to maximize total profit, the logical solution is to produce only Product C. If things were really that simple, you wouldn’t need tools such as Solver. As in most situations, this company has some constraints that must be met:
- The combined production capacity is 300 total units per day.
- The company needs 50 units of Product A to fill an existing order.
- The company needs 40 units of Product B to fill an anticipated order.
- Because the market for Product C is relatively limited, the company doesn’t want to produce more than 40 units of this product.
These four constraints make the problem more realistic and a bit more challenging. In fact, it’s a perfect problem for Solver.
Let us discuss step by step to solve this problem.
1. Go to Data -> Analysis -> Solver. Excel displays its Solver Parameters dialog box, shown in Figure below.
2. Enter (or point to) cell D6 in the Sheet Target Cell field of the Solver Parameters dialog box.
3. Because the objective is to maximize this cell, click the Max option.
4. Next, specify the changing cells (which are in the range B3:B5) in the By Changing Cells box. The next step is to specify the constraints on the problem. The constraints are added one at a time and appear in the box labeled Subject To The Constraints.
5. To add a constraints, click the Add button.
6. To set the first constraint (that the total production capacity is 300 units), enter B6 as the Cell Reference, choose equal (=) from the drop-down list of operators, and enter 300 as the Constraint value.
7. Click Add to add the reamining constraints. The table below summarizes the constraints for this problem.
8. After you enter the last constraint, click OK to return to the Solver Parameters dialog box, which now lists the four constraints. At this point, Solver knows everything about the problem.
9. Click the Solve button to start the solution process. You can watch the progress onscreen, and Excel soon announces that it has found a solution. The Solver Results dialog box is shown in Figure below.
At this point, you have the following options:
-> Replace the original changing cell values with the values that Solver found.
-> Restore the original changing cell values.
-> Create any or all three reports that describe what Solver did.
-> Click the Save Scenario button to save the solution as a scenario, so that Scenario Manager can use it.
If you specify any report options, Excel creates each report on a new worksheet, with an appropriate name. Figure below shows an Answer Report. In the Constraints section of the report, two of the constraints are binding, which means that these constraints were satisfied at their limit with no more room to change.
This simple example illustrates how Solver works. The fact is, you could probably solve this particular problem manually just as quickly. That, of course, isn’t always the case.
DATABASE FUNCTIONS
DMIN
DMIN function is useful to find out the lowest value of a field based on the criteria provided by you. The synatx of the function is =DMIN (Database, “Field”, “Criteria”). Database is the table form where the result has to be extracted. Field is the field name (Column name) or the position of the column in the table of which minimum value has to be found out and criteria is the range of cells where you specify the condition.
This function is extremely helpful when you have multiple records for a particular item and you need to find the lowest value among them on certain criteria. Let us suppose we have a database of different products with multiple values and we need to find out the lowest selling price of the product “BLUE TOOTH”, here it is most suitable function that can give you the required solution. Following is the database:
To solve this problem we first have to write down the criteria/condition. Write down the condition in the following manner in somewhere else in the worksheet.
Product: BLUETOOH
Now place your cursor where you want to put your result. Write =DMIN and press CTRL+A. The following formula palate will appear. Fill the information properly as follows and click on OK to get the result.
DMAX
This function is used to return the largest number of the field or column in a database based on certain criteria. The synatx of the function is: =DMAX(Database,Field,Criteria). Meanings of the agrumnets are same as DMIN functions.
From the same database (What we have used to explain DMIN (function) if we have to find out the maximum selling price of a particular product, say BLUETOOTH, then;
Write down the criteria same as the example before (as we are working with the same product and thus our criteria is still unchanged, i.e product is BLUETOOTH).
Keep your cursor where you want to put the result, write = DMAX and press CTRL+A to bring the following formula palate:
Fill the information accordingly and press ok to get the result.
DAVERAGE
This function also takes the same arguments as DMIN and DMAX functions but returns the average of the numbers of a particular field based on certain criteria. To find out the avarage selling price of BLUETOOTH from the previous database we can use this function. Here also the all the arguments of the formula take the same data as the database, field and criteria are same. See the formula palate below:
DCOUNT
Counts the cells that contain numbers in a column of a list or database that match conditions you specify. The Syntax of DCOUNT function is =DCOUNT (DatabaseRange, FieldName, CriteriaRange).
This function examnines a list of information and counts the values in a specified column. It can only count numeric or data values, the text items and blank cells are ignored.
In the example we have to find out the number of Bulb that has wattage between 80 and 100. In Cell E24 we have written the function like this: =DCOUNT(B1:I17,C1,C21:E22).
DatabaseRange: Entire data table (B1:I17)
FieldName: The field which you want to count (C1).
CriteriaRange: The range based on which result will be generated. Write your criteria as it is written in cells C21:E22.
The criteria range actually passing the information to the DCOUNT function to count the records having Product name Buble and Wattage is between 80 and 100.
DCOUNTA
It is same as DCOUNT function but it does not ignores the text items. The Syntax of DCOUNTA function is =DCOUNTA(DatabaseRange, FieldName, CriteriaRange).
DGET
This function examines a list of information and produces one result. If more than one record matches the criteria the error #NUM is shown. If no record match the criteria the eror #VALUE is shown
The syntax of DGET function is =DGET(DatabaseRange, FieldName, CriteriaRange).
In the example we are going to find out the quantity in hand of the product Bulb with brand name Horizon and Wattage 100. In cell E24 the function is written like this:
=DGET(B1:B17,H1,B21:E22).
All the agruments are supplied with the same logic as we have discussed in DCOUNT function.
This example extracts information from just one record as shown in figure.
