How to Calculate a Weighted Average Bed Fill Rate in Excel or DAX
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.

- 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.
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.
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.
Click on the empty cell where you want the final weighted average to be displayed.
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).
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 DAX
If you are working with data models in Power Pivot or Power BI, use a DAX measure with SUMX and DIVIDE to calculate the weighted average dynamically.
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. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the program capacity and fill rate data.
- 2. Apply the SUMPRODUCT Formula: Select the cell for your result and enter the formula =SUMPRODUCT(capacity_range, rate_range)/SUM(capacity_range).
- 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.

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.




