No. In Excel, Solver can use decimal values by default; it uses whole numbers only when you add an integer constraint to the relevant decision cells. You can also require a variable to be binary, meaning it must be either 0 or 1.
What Solver does by default
Solver searches for values in designated changing cells that optimize a formula in an objective cell while satisfying the model’s constraints. Unless you add an integer or binary restriction, changing cells are generally treated as continuous variables. That means a result such as 10.5 is allowed, even when the cell is formatted to display no decimal places.
This behavior is useful for quantities that can genuinely vary by fractions, such as hours, kilograms, or liters. It can be unsuitable for indivisible items such as people, vehicles, or boxes. The key distinction is between a value’s displayed format and the rules Solver is required to obey: formatting affects appearance, while constraints affect the solution.
Solver does not decide on its own that a number should be a whole number because a heading says “units” or because the cell looks like an integer. You must encode that requirement in the model. If a variable must also be nonnegative, make sure that requirement is present too; an integer constraint by itself does not rule out negative integers.
How to require whole numbers
In Excel for Microsoft 365 on Windows, enable the Solver Add-in if it is not already available: choose File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Solver Add-in, and select OK. Solver then appears on the Data tab. In Excel for Mac, enable Solver through Tools > Excel Add-ins, then look for Solver on the Data tab. Solver is a desktop add-in; Excel for the web does not run the Solver Add-in.
To constrain a decision cell or range to whole numbers:
- Choose Data > Solver and fill in the objective cell, the goal (such as Max or Min), and the changing variable cells.
- Select Add in the Constraints area. In Cell Reference, enter or select the decision cell or range you want to restrict.
- Open the relation list and choose int, then select OK. The int relation tells Solver that the selected changing cells must be integers.
- Add any other model requirements, such as resource limits or nonnegative values, then choose Solve.
- Review the solution and choose Keep Solver Solution if you want to retain it in the worksheet.
You can apply an integer constraint to one variable or to a range. If only some decisions must be whole numbers, constrain only those cells. A range constraint applies to each cell in the selected range, so check the reference carefully before solving.
A simple example: production quantities
Suppose a small bakery earns $12 for each box of a product and has enough ingredients for 42 kilograms of production input. Each box uses 4 kilograms. Let the changing cell represent the number of boxes, and set the objective to maximize total profit subject to the ingredient limit. If the model permits fractions, Solver can choose 10.5 boxes: that uses all 42 kilograms and gives $126 in modeled profit.
But the bakery cannot ship half a box if it sells only complete boxes. Add an int constraint to the changing cell and Solver must choose a whole-number quantity. The best feasible amount is 10 boxes, using 40 kilograms and producing $120 in modeled profit. The unused 2 kilograms do not indicate a Solver error; they are the consequence of the indivisible-unit requirement.
This example also shows why the integer restriction belongs on the decision variable, not merely on the profit result. Rounding a calculated profit does not make the number of boxes whole. Likewise, a model that omits the ingredient constraint could produce an integer answer that is still impractical. Solver can only respect the requirements represented in the worksheet and its constraint list.
Integer, binary, and continuous variables
Solver’s integer-related choices address different kinds of decisions:
- Continuous: Decimals are permitted. Use this for quantities that can be divided, such as time or liquid volume, when fractional values make sense for the problem.
- Integer (int): Values must be whole numbers, including negative whole numbers unless the model also limits them. Use it for counts such as items produced, staff scheduled, or vehicles assigned.
- Binary (bin): Values must be 0 or 1. Use it for yes/no choices, such as whether to open a site or include a project. A binary decision can represent “selected” or “not selected,” but the model still needs constraints connecting that choice to costs, capacity, or other decisions.
A model can mix these variable types. For example, the number of vehicles might be integer, a site-selection decision binary, and a number of operating hours continuous. Add the appropriate restriction to each cell or group of cells rather than treating every variable the same.
Choosing a solving method
The method matters because integer restrictions can make an optimization problem harder than its continuous counterpart. For a linear model—one where the objective and constraints are linear in the changing cells—Simplex LP is generally the relevant Solver method. In Solver Parameters, choose the method from the Select a Solving Method list. Keep the worksheet model linear if you intend to use a linear method; products of changing variables, powers, and certain nonlinear expressions can change the problem’s character.
For smooth nonlinear models, GRG Nonlinear may be appropriate. For models with discontinuities or relationships that do not work well with the other methods, Evolutionary may be a candidate. The right choice depends on how the worksheet is built; a method name alone does not guarantee that every model will solve to the desired result. If an integer model is not finding a satisfactory answer, check the model structure and Solver’s method and stopping settings rather than assuming that integer constraints are unsupported.
Integer optimization can require Solver to examine alternatives that a continuous model does not. As a result, a model with integer decisions may take longer, and a complicated model may stop without proving that its current answer is the absolute best possible one. Check Solver’s result message and any limits or tolerances in Options before relying on a result. For important work, test the solution against the original constraints independently.
Common mistakes that produce fractional answers
If Solver returns a decimal when you expect a count, the most likely cause is that no integer constraint was added to that changing cell. Open Data > Solver and inspect the Constraints list. Confirm that the reference is the same cell Solver is changing, and that the relation shown is int rather than a numeric comparison such as <= or >=.
Other frequent sources of confusion include:
- Using number formatting as a rule: A cell formatted with zero decimal places may display 10 while its stored value is 10.4. Change the format to inspect the underlying value, but add an integer constraint to control Solver’s choice.
- Rounding after optimization: Rounding a fractional result can make it violate a resource limit or no longer maximize the objective. Solver must optimize with the whole-number condition in place, rather than having the answer rounded afterward.
- Forgetting nonnegativity or bounds: An integer cell can take negative values if the rest of the model allows them. Add an explicit lower bound or use Solver’s nonnegative-variable setting when appropriate.
- Constraining the wrong cells: A formula cell is usually an outcome of the model, not a decision variable. Apply int or bin to the changing cells that represent the choices being made.
Performance and built-in Solver limits
Integer restrictions can increase solving time because Solver must account for discrete choices rather than simply adjusting variables smoothly. The effect can be small for a simple problem with a few decision cells, but substantial for a model with many integer variables, complex formulas, or tight constraints. A good first step is to make the model as small and clear as possible: remove unused changing cells, avoid unnecessary formula complexity, and encode only genuine requirements.
The standard Solver add-in included with Excel has a limit of 200 changing variable cells. A model with more decision cells may need to be simplified or handled with another tool. Solver can also have difficulty when the worksheet contains errors, discontinuous formulas, or constraints that make the model infeasible. If it reports no feasible solution, check whether the stated limits can all be satisfied at once; an integer requirement may expose a conflict that was hidden by a fractional solution.
When another approach may be better
For a small planning problem, Excel Solver is often a practical way to model integer decisions directly. If the model exceeds the built-in variable-cell limit, takes too long, or needs specialized optimization features, consider an optimization package designed for larger mixed-integer problems. Some third-party Excel add-ins provide different limits or algorithms, but their capabilities and costs vary, so check the product’s documentation before moving a model.
For a very small problem, it can also be useful to enumerate the few possible whole-number choices and compare their outcomes. That approach is transparent when there are only a handful of alternatives, but the number of combinations can grow quickly. Whatever method you use, keep the assumptions, constraints, and units visible so someone reviewing the result can tell what the answer means.
Frequently Asked Questions
Can Excel Solver return decimals?
Yes. Solver permits decimal values in changing cells unless you add an integer or binary constraint. The result also depends on the model, its constraints, and the selected solving method.
How do I make one Solver cell a whole number?
In Data > Solver, add a constraint using that changing cell as the reference and int as the relation. Keep any separate requirements, such as a minimum value of zero, in the model as well.
Can some Solver variables be whole numbers and others decimals?
Yes. Add int constraints only to the cells that represent indivisible counts. Leave other changing cells unrestricted by an integer condition if fractional values are valid for those decisions.
How do I make Solver choose yes or no?
Add a bin constraint to the relevant changing cell. A binary variable can be 0 or 1; add linking constraints if that choice must control other quantities, such as whether production is allowed at a selected site.
Why does Solver take longer after I add an integer constraint?
It has to account for discrete alternatives, which can make the optimization harder than the continuous version. Reduce unnecessary decision cells, verify that the model is well formed, and review Solver’s method and stopping settings if the run takes too long.
Related
- Excel Scatter Plot Regression Line: Add and Interpret It
- Excel Scatter Plot Not Showing All Data? Try These Fixes
- Excel Scatter Plot Line of Best Fit: Add and Interpret
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Go From Double to Single Space in Microsoft WordTo change double-spaced text to single spacing in Microsoft Word, select the text—or press Ctrl+A on Windows or…
- Excel Table Color Not Changing? Common Causes and FixesIf an Excel table’s color is not changing, select a cell inside the table, open Table Design, and…
- How to Insert Track Changes in a Word DocumentTo insert tracked edits in a Word document, open the file and select Review > Track Changes >…
- Outlook AutoArchive: How to Set It Up AutomaticallyTo set up scheduled email archiving, open classic Outlook for Windows and go to File > Options >…
- Find and Replace Enter in Excel: Remove Line BreaksTo find and replace an Enter line break inside Excel cells, press Ctrl+H, click the Find what box,…
- Excel Not Responding in Safe Mode? Causes and FixesIf Excel is still not responding in Safe Mode, the problem is less likely to be a normal…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.