To calculate a weighted average rate in Excel, multiply each rate by its corresponding weight, add those products, and divide by the sum of the weights. With rates in B2:B10 and weights in C2:C10, enter =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10). This gives more influence to entries with larger weights, such as a larger loan balance or a greater quantity.
What a weighted average rate tells you
A simple average treats every rate as equally important. A weighted average reflects how much each rate applies to the total. For example, if you have one loan balance at 4% and another at 7%, the overall rate depends not just on the two percentages but on the size of each balance. A 4% rate on a large balance should count more than a 7% rate on a very small balance.
The weight is the quantity associated with each rate. Depending on the question, it might be a principal balance, transaction amount, number of units, hours, or another measure of exposure. A weighted average rate is therefore not meaningful until you decide what the weights represent. For a combined borrowing rate, balances are a common choice; for a rate applied to units sold, units may be the appropriate weights.
Mathematically, the calculation is the sum of rate-times-weight products divided by the sum of the weights. If every weight is the same, the weighted average equals the ordinary arithmetic average. If weights differ, the result shifts toward the rates attached to larger weights. It will normally fall between the lowest and highest included rates when all weights are nonnegative and the rates and weights are valid numeric values.
Set up the rate and weight columns
Put each rate and its matching weight on the same row. For example, a small table could have a loan or category name in column A, an annual rate in column B, and its balance in column C. The row relationship matters: the balance in C2 must belong to the rate in B2, the balance in C3 to the rate in B3, and so on. Sorting just one column can break that pairing and produce a plausible-looking but incorrect result.
Format rate cells as percentages and weights as numbers or currency, as appropriate. In Excel for Microsoft 365 on Windows, select the rate cells and use Home > Number group > Percent Style. The formula itself works the same in Excel for Mac and Excel for the web; the ribbon layout may differ slightly. Percentage formatting changes how a value appears, not its underlying numeric value. Excel stores 5% as 0.05, which is exactly what the weighted-average calculation needs.
Check that both ranges cover the same rows and contain corresponding entries. If rates are in B2:B10, weights should normally be in C2:C10—not C2:C9 or C3:C11. Include only the records that belong in the result. If a row is a subtotal, a duplicate, or an unrelated item, including it will change the calculation.
Use SUMPRODUCT to calculate the result
With rates in B2:B10 and their matching weights in C2:C10, put this formula in an empty cell:
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)
SUMPRODUCT multiplies values in corresponding positions and adds the products. The denominator, SUM of the weights, converts that weighted total into an average. For instance, the first rate is multiplied by the first weight, the second rate by the second weight, and so on. The ranges must have matching dimensions so Excel can pair each rate with its weight.
Because the rates are stored as decimal values, the result is also a decimal rate. If the result displays as 0.0533, that represents approximately 5.33%. Apply percentage formatting to the result cell to make it easier to read. You can choose the number of decimal places shown; changing the displayed precision does not change the underlying result used in later calculations.
To make the formula easier to audit, you can also calculate the numerator and denominator in separate cells: one cell for the total of rate multiplied by weight, and another for the total weight. Then divide the first result by the second. This is not necessary for a straightforward table, but separate components can help when reviewing a workbook or explaining how the answer was obtained.
Worked example using loan balances
Suppose three illustrative balances have annual rates of 4%, 5%, and 6%. Enter the rates in B2:B4 and the balances in C2:C4 as follows: B2 is 4% with C2 equal to $10,000; B3 is 5% with C3 equal to $20,000; and B4 is 6% with C4 equal to $30,000. The total balance is $60,000.
With those rates in B2:B4 and balances in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). The products are $400, $1,000, and $1,800, respectively, for a weighted total of $3,200. Dividing $3,200 by $60,000 gives 0.053333…, or approximately 5.33%. Format the formula cell as a percentage to display that result.
The ordinary average of 4%, 5%, and 6% is 5%, but that would give each loan equal influence. Here the largest balance is attached to the highest rate, so the weighted rate is above 5%. If instead the $30,000 balance belonged to the 4% rate, the combined rate would move closer to 4%. The example is only a spreadsheet illustration; actual loan terms can include fees, compounding, and other details that this simple calculation does not model.
Choose weights that match the question
The formula is only as useful as the weights. Before calculating, ask what combined rate you want to describe and what quantity should determine each entry’s influence. A few common choices include:
- Loan or deposit balances: Use each balance when finding a balance-weighted rate across accounts.
- Transaction amounts: Use the amount associated with each transaction if the goal is to summarize rates across different-sized transactions.
- Units or quantities: Use the number of units when each rate applies to a different quantity of the same comparable item.
- Time periods: Use time as a weight only when the rate and the question are defined so that time weighting is appropriate.
These choices can produce different answers from the same list of rates. For example, weighting by the number of accounts gives each account equal weight, while weighting by balances gives larger accounts more influence. Neither is automatically correct: choose the one that answers the real question, and label the result so someone reading the workbook can see what it represents.
Also make the rates comparable before combining them. Do not mix annual rates and monthly rates as though they were the same unit. Convert them to a common basis first, using a conversion that matches the meaning of the source rates. Similarly, if entries cover different periods or use different definitions of rate, confirm that a simple weighted average is appropriate rather than assuming the formula resolves those differences.
Common errors and checks
A frequent mistake is taking an ordinary average when the entries have unequal weights. Another is dividing by the number of rates rather than by the total weight. The denominator must match the chosen weights; in the example, dividing $3,200 by three would not produce a meaningful rate. Check that the weights are the intended quantities and that the total weight is not zero.
Other issues to look for include:
- Rates stored as text: A cell that looks like 5% may not be a numeric percentage. Convert it to a number or enter it as a percentage value before calculating.
- Misaligned rows: A rate paired with another record’s weight can quietly distort the result. Keep each pair together when sorting or filtering.
- Different range lengths: SUMPRODUCT needs corresponding arrays of compatible dimensions. Make the rate and weight ranges cover the same number of rows.
- Zero or negative weights: A zero weight contributes nothing; negative weights can make the result unsuitable as an ordinary average. Investigate whether those values belong in the calculation.
- Errors in source cells: An error value in a referenced range can cause the formula to return an error. Find and correct the underlying data issue rather than masking it without review.
Blanks and nonnumeric entries also deserve attention. SUM ignores text and blank cells in its weight range, while the intended treatment of a row with a missing rate may not be obvious. If a weight is included in the denominator but its matching rate is missing, the result can effectively treat that rate as zero in some array calculations. Decide whether incomplete rows should be excluded, completed, or handled under a documented rule, then make the formula and data consistent with that decision.
When a different method may be clearer
SUMPRODUCT is usually the most compact method for a clean two-column list. A helper column can be preferable when the workbook needs a visible audit trail: calculate the rate-times-weight contribution for every row, sum those contributions, then divide by the total weights. The helper values make it easier to identify which rows contribute most and to inspect individual calculations. They also make a workbook longer, so for a small table the single formula is often simpler.
If the table includes categories that should not be included, first define the inclusion rule. You can filter the data and calculate only the relevant rows, or use a more advanced conditional calculation when needed. Be careful that a filter alone does not necessarily change what a formula using the entire fixed range includes. For a reusable report, a structured table and explicit criteria can make the scope clearer than relying on the visible rows.
For a large or regularly refreshed dataset, consider making the source data an Excel table and using clear column headings. Table references can expand as rows are added, reducing the chance that a new record falls outside fixed ranges. Whatever method you use, retain the underlying rates and weights alongside the final result; a single displayed percentage without its inputs is much harder to verify.
Frequently Asked Questions
What is the Excel formula for a weighted average rate?
With rates in B2:B10 and matching weights in C2:C10, use =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10). Format the answer cell as a percentage if the rates are percentages.
Should I use the rate or the amount as the weight?
Use the quantity that should determine how much each rate influences the overall result. For a combined rate across loans, balances are often the relevant weights. If you want each account to count equally, use equal weights instead.
Why is my weighted average different from AVERAGE?
AVERAGE assigns equal influence to each numeric rate. A weighted average assigns influence according to the weights, so the two results differ when weights are unequal and rates vary.
Can weights be percentages?
Yes, if they represent valid proportions and are used consistently. The result is still a weighted average. Make sure the weights sum to a meaningful total and that they correspond to the right rates.
What happens if all weights are zero?
The denominator is zero, so Excel cannot calculate the average and returns a division-by-zero error. There is no weighted average in that case; check whether the weights are missing, entered incorrectly, or genuinely zero.
Related
- Basic Sum in Excel: How to Add Cells and Numbers
- Basic Excel Budget Template: Build a Monthly Budget
- Basic Inventory Tracking in Excel: A Practical Setup
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average Time Between Dates in Excel: Formula & ExamplesTo calculate the average time between consecutive dates in Excel, subtract each date from the one below it,…
- Excel Filter Dates Within a Range: Steps and FixesTo filter Excel dates within a range, select a cell in your data, choose Data > Filter, open…
- Table Functions in Tableau: How They WorkIn Tableau, table functions are functions used in table calculations to compute results across the values currently displayed…
- Custom Text Filter in Excel: How to Use ItTo apply a custom text filter in Excel for Microsoft 365 on Windows, select a cell in your…
- Concat Excel Cells with Dates: Formulas, Fixes, and TipsTo concatenate text and a date in Excel, use the TEXT function to format the date before joining…
- Extract Text in an Excel Cell: Formulas and Steps That WorkTo extract part of a cell’s text, use a formula such as TEXTBEFORE for text before a separator,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.