Function Problems

How to Use Excel Solver to Find the Closest Combination Below a Target

Olivia Miller Oct 21, 2026 868 views

Question details

The user needs to select a combination of numbers from a dataset that maximizes the total sum while ensuring the result does not exceed a predefined target value.

How to Use Excel Solver to Find the Closest Combination Below a Target

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to optimize a sum from a specific subset of numerical data without crossing a maximum limit, especially when an exact match to the target does not exist.
Observed behavior: The goal state is to successfully configure binary constraints and objective cells in the Solver add-in to identify the optimal mix of numbers.

Before you start

Ensure that the Solver add-in is enabled in your spreadsheet software, as it is often turned off by default. You can activate it by navigating to your application's Options menu and managing your Excel Add-ins.

Solution 1Recommended

Configure Binary Constraints and SUMPRODUCT in Solver

This is the most reliable method for finding the best combination of values. By using binary inclusion cells (0 or 1), you can instruct Solver to selectively add numbers until it finds the maximum possible sum below your target.

This solution relies on the SUMPRODUCT function to multiply your data values by a binary indicator. A value of 1 means the number is included in the total, while a 0 means it is excluded.

Set up your data and binary cells

List your available numbers in a single column (e.g., A2:A10). In the adjacent column (e.g., B2:B10), type '0' in each cell. These will be your binary inclusion cells that Solver will change.

Calculate the total with SUMPRODUCT

Select an empty cell where you want your total to appear. Enter the formula =SUMPRODUCT(A2:A10, B2:B10). This formula calculates the current sum of the included values.

Open the Solver Add-in

Navigate to the 'Data' tab on your ribbon and click on 'Solver'. If it is not visible, you will need to enable it via File > Options > Add-ins.

Set the objective and variable cells

In the Solver Parameters dialog, set the 'Set Objective' box to your total cell containing the SUMPRODUCT formula. Choose 'Max' to maximize the value. In the 'By Changing Variable Cells' box, select your binary cells (B2:B10).

Add rules and solve

Click 'Add' to insert constraints. First, select your changing cells (B2:B10), choose 'bin' from the dropdown to make them binary. Add a second constraint stating that the total cell must be '<=' (less than or equal to) your target value. Click 'Solve'.

Solution Found: Once Solver completes its calculations, a dialog box will appear. Select 'Keep Solver Solution' and click OK to see which items were assigned a 1.

Solution 2

Optimize Solver Speed by Reducing Precision

When dealing with a large dataset, calculating every possible combination can cause Solver to freeze or take an excessively long time. Reducing the constraint precision helps find a near-optimal combination much faster.

Advanced Data Analysis Tool

Solve Complex Data Combinations Faster with WPS Spreadsheet

WPS Spreadsheet features a built-in Solver tool that flawlessly handles complex what-if analysis, maximizing totals, and applying binary constraints. It operates just like the tool you are familiar with, allowing you to optimize data without a steep learning curve.

1. Open Your Data: Launch WPS Spreadsheet and open your existing .xlsx file containing the numbers and SUMPRODUCT formula.
2. Access What-If Analysis: Navigate to the Data tab on the top ribbon and locate the 'What-If Analysis' or 'Solver' feature.
3. Configure the Optimization: Set your target cell to Max, define your changing binary cells, and enforce the upper limit target constraint.
4. Solve and Keep: Click Solve. WPS Spreadsheet will calculate the optimal mix and allow you to save the results directly into your worksheet.

Built-in Solver tool for advanced data modeling and optimizationFully compatible with Microsoft Excel (.xlsx) formats and formulas like SUMPRODUCTLightweight architecture ensures faster calculation times for heavy combinationsFree to use with a familiar, highly intuitive user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is the Solver option missing from my Excel Data tab?

Solver is an add-in program that is not enabled by default. To turn it on, go to File > Options > Add-ins. In the Manage box, select Excel Add-ins and click Go. Check the box for Solver Add-in and click OK.

What does the 'bin' constraint do in Solver?

The 'bin' (binary) constraint forces Solver to only use the numbers 0 or 1 in the designated changing cells. In optimization problems, this acts as a simple 'Yes' (1, include this number) or 'No' (0, exclude this number) switch.

Can I use Solver to find an exact match to my target?

Yes. If you want an exact match, change the objective setting from 'Max' to 'Value Of' and enter your exact target number. However, if no combination equals that exact number, Solver will fail to find a feasible solution. Using 'Max' with a '<=' constraint guarantees it will find the closest possible sum without going over.

Why does Solver display an error saying it cannot find a feasible solution?

This usually happens if your constraints conflict with each other, or if the smallest number in your dataset is already larger than your target. Double-check that your SUMPRODUCT formula is correct and that your target limit is reasonable.

Excel & Data Problems