How to Have Excel Solver Give Only Whole Numbers

By Brian Richards

Excel Solver is an optimization algorithm produced by Frontline Systems, Inc. that is designed to analyze models of engineering and finance in a spreadsheet. Solver can be described as a function that answers "what if" questions in business. For example, if a company has a certain budget, Solver can determine how to best allocate that budget to meet the company's given product needs. Naturally, most products cannot be purchased in fractional amounts, so Solver can be configured to report totals in only whole numbers.

Step 1

Go to the "Data" tab in Excel, and click the "Solver" button to launch the Solver dialogue box.

Step 2

Click the "Add" button under Constraints to launch the "Add Constraints" dialogue box.

Step 3

Enter the name of the cell in the "Cell Reference" box, or highlight a range of cells with your cursor to fill this box automatically.

Step 4

Click the drop-down menu in the middle of the "Add Constraints" dialogue box, and select "int" from the menu. The word "integer" will appear in the Constraint box.

Step 5

Click "OK"

Tips & Warnings

  • Solver will not round numbers appropriately, but will instead drop the decimals, essentially rounding down any quantity. This behavior is intentional. For instance, if your budget will only support purchasing 5.87 products, it would be inaccurate for Solver to suggest that you should purchase 6 (the proper way to round 5.87). Instead, Solver will report that you should purchase 5.