Calculation Issues

How to Calculate a Weighted Average Bed Fill Rate in Excel or DAX

Camila MilosovichCamila Milosovich Nov 11, 2026 870 views

Question details

The user needs to calculate a weighted average bed fill rate across multiple programs, ensuring that programs with larger capacities have a proportional impact on the final average.

How to Calculate a Weighted Average Bed Fill Rate in Excel or DAX
Product
Excel / DAX
Device & OS
not provided
Scenario
Calculating accurate performance or utilization metrics where simple averages fail to account for varying program sizes.
Observed behavior
A mathematical model or formula is required to multiply each program's capacity by its fill rate, sum those products, and divide by the total overall capacity.
Before you start

Ensure your dataset contains separate columns for 'Capacity' (number of beds) and 'Fill Rate' (percentages or decimals), and remove any rows with blank or invalid capacity values to avoid calculation errors.

Solution 1Recommended

Calculate Weighted Average Using SUMPRODUCT in Excel

Use the SUMPRODUCT function combined with SUM to quickly calculate the weighted average without needing helper columns.

The SUMPRODUCT function multiplies the corresponding components in given arrays and returns the sum of those products. By dividing this result by the sum of the capacities, you get a perfectly weighted average.

1
Select the target cell

Click on the empty cell where you want the final weighted average to be displayed.

2
Enter the SUMPRODUCT formula

Type =SUMPRODUCT(CapacityRange, FillRateRange) / SUM(CapacityRange). Replace 'CapacityRange' with your actual cell range for bed capacities (e.g., B2:B20) and 'FillRateRange' with the cell range for the fill rates (e.g., C2:C20).

3
Format as a percentage

Press Enter to calculate. If the result appears as a decimal (like 0.85), go to the Home tab on the ribbon and click the '%' (Percent Style) icon to format the cell as a percentage.

Calculate Weighted Average Using SUMPRODUCT in Excel
Formula Validation: Make sure that both the CapacityRange and FillRateRange selected in the formula are exactly the same size (e.g., rows 2 through 20 for both), otherwise SUMPRODUCT will return a #VALUE! error.
Efficient Data Analysis

Easily Calculate Weighted Averages with WPS Spreadsheet

WPS Spreadsheet fully supports advanced statistical functions like SUMPRODUCT, allowing you to calculate weighted averages seamlessly. Enjoy a user-friendly interface that handles complex data analysis just like Microsoft Excel, completely free of charge.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the program capacity and fill rate data.
  2. 2. Apply the SUMPRODUCT Formula: Select the cell for your result and enter the formula =SUMPRODUCT(capacity_range, rate_range)/SUM(capacity_range).
  3. 3. Format as Percentage: Navigate to the Home tab on the top menu and click the Percent Style button to view the accurate weighted fill rate.
Fully compatible with Microsoft Excel formulas and file formats (.xlsx, .xls)Built-in SUMPRODUCT and array calculation capabilities for complex weightingLightweight software that runs smoothly even on older devicesFree to download and highly intuitive for everyday data tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use the AVERAGE function for fill rates?

The standard AVERAGE function treats all programs equally, regardless of their size. If a 10-bed program is 100% full and a 100-bed program is 50% full, a simple average yields 75%, which vastly misrepresents the actual total beds filled. A weighted average correctly accounts for the size (capacity) of each program.

What should I do if some programs have no capacity data?

Blank or invalid text values in the capacity column will result in a #VALUE! or #DIV/0! error. Before calculating, filter out or delete rows missing capacity data, or use an IFERROR wrapper around your SUMPRODUCT formula to handle exceptions gracefully.

Does the SUMPRODUCT method work if fill rates are whole numbers instead of percentages?

Yes, but your result will also be a whole number. If your fill rate is recorded as '80' instead of '80%', the formula will output a weighted average in the same whole number scale. You can divide the final result by 100 if you need to convert it into a standard percentage format.