Calculation Issues

How to Use Excel Solver to Allocate Worker Hours by Cost

Algirdas Jasaitis Sep 27, 2026 869 views

Question details

The user needs to distribute total working hours among multiple employees with varying hourly rates to meet specific total hours and total cost targets.

How to Use Excel Solver to Allocate Worker Hours by Cost

Product: Microsoft Excel
Device & OS: not provided
Scenario: Determining multiple unknown variable values (worker hours) based on two distinct targets (total hours and total labor cost).
Observed behavior: The standard Goal Seek feature cannot handle multiple unknown variables. An advanced optimization tool like Solver is required to process multiple constraints and objectives.

Before you start

Ensure you have the Solver add-in enabled in your spreadsheet software and verify that your worksheet contains the exact hourly rate for each worker, along with formula cells calculating the total hours and total labor cost.

Solution 1Recommended

Use the Solver Add-in to Determine Hour Allocation

Excel Solver handles multiple variables and constraints simultaneously, making it the perfect tool for calculating multiple worker hours when Goal Seek falls short.

Because you are solving for three variables (hours for three workers) but only have two known equations (total hours and total cost), this problem can have multiple mathematically valid solutions. Setting up Solver with specific constraints or an additional optimization objective helps you find the most realistic and equitable distribution of hours.

Enable the Solver Add-in

In Excel, go to File > Options > Add-ins. In the Manage drop-down at the bottom, select 'Excel Add-ins' and click Go. Check the box for 'Solver Add-in' and click OK.

Prepare your dataset formulas

Set up a column for Worker Hours (leave these blank for now). Create a 'Total Hours' cell using the SUM function on the hours column. Create a 'Total Cost' cell using the SUMPRODUCT function to multiply the worker hours by their respective hourly rates.

Open Solver

Navigate to the Data tab on the ribbon and click 'Solver' located in the Analyze group.

Define variables and objectives

If you want to balance the hours, set your Objective to a cell calculating the variance or standard deviation of the hours and set it to 'Min' (Minimum). Select the blank worker-hour cells in the 'By Changing Variable Cells' box.

Add constraints

Click 'Add' to set your target constraints. For example, set the Total Hours cell = 79 and the Total Cost cell = 10025. To prevent fractional or negative hours, add constraints setting the changing variable cells as 'int' (integer) and `>= 0'.

Run Solver

Click 'Solve'. Once Solver finds a solution, select 'Keep Solver Solution' and click OK to apply the allocated hours to your worksheet.

Use the Solver Add-in to Determine Hour Allocation

Minimizing Variance: If you don't define a specific minimization objective (like standard deviation), Solver might output extreme hour allocations (e.g., zero hours for one worker) that technically meet the cost and hour constraints. Adding an equity objective prevents this.

Solve Complex Data with WPS Office

Allocate Hours and Costs Easily Using WPS Spreadsheet

WPS Office provides a powerful, built-in Goal Seek and Solver tool in its Spreadsheet application, allowing you to easily calculate and allocate labor costs and working hours across your team completely free of charge.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the worksheet containing your hourly rates and blank worker hours.
2. Access the Solver Tool: Navigate to the 'Data' tab on the top ribbon and select 'Solver'.
3. Set parameters and calculate: Input your target values, select the worker hour cells as your changing variables, add constraints for total budget and time, and click 'Solve' to automatically allocate the hours.

Built-in Data Analysis tools including Goal Seek and SolverSeamless and complete compatibility with Microsoft Excel (.xlsx, .xls) formatsFamiliar user interface for immediate productivity with zero learning curveLightweight installation and fast processing for large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use Goal Seek for allocating hours among multiple workers?

Goal Seek is strictly designed to solve equations with a single unknown variable. Because allocating hours to multiple workers involves multiple unknown variables, you must use a tool like Solver, which can process numerous variables and constraints simultaneously.

Where do I find the Solver tool in my spreadsheet?

In most spreadsheet applications, including Microsoft Excel and WPS Spreadsheet, Solver is located under the 'Data' tab. If you do not see it in Excel, you will need to enable it manually via File > Options > Add-ins.

What should I do if Solver assigns negative hours to a worker?

When setting up your Solver parameters, make sure to check the box that says 'Make Unconstrained Variables Non-Negative', or manually add a constraint specifying that the variable cells must be greater than or equal to zero.

How do I force Solver to only give me whole numbers for working hours?

You can force whole numbers by adding an integer constraint. In the Solver Parameters dialog box, click 'Add' constraint, select the cells representing worker hours, and choose 'int' (integer) from the operator dropdown menu.

Excel & Data Problems