How to Calculate Weighted Average in Excel With 2 Methods

By Indeed Editorial Team

Published April 20, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Microsoft Excel is a widely used spreadsheet program that can be invaluable to anyone working with data analysis or doing statistical research. Excel has functions for various calculations, including weighted average. You may find the weighted average function helpful when assessing central tendency. In this article, we define what a weighted average is, explain how to calculate weighted average in Excel, discuss why you might use it, and explore tips for using Excel's SUM and SUMPRODUCT features.

Related: Advanced Excel Skills: Definitions and Examples

What is a weighted average?

You can calculate a simple average by adding up all values in the set and dividing by the total number of values. The weighted average means the average of a data set with certain values assigned greater significance than others. To calculate the weighted average, you multiply each value in the data set by a specified factor so that it has more significance, or "weight," in calculating the average. Values multiplied by greater factors have more influence on the overall average, while the values multiplied by lesser factors have less influence.

With smaller data sets, it is relatively simple to calculate the weighted average. With larger data sets, the process of calculating the weighted average can be more complex. Excel allows you to calculate the weighted average of a large data set with a few relatively easy steps. There are two functions you can use in Excel to calculate weighted average, SUM or SUMPRODUCT. Each one has advantages for different situations.

Related:

  • How To Calculate Weighted Average in 3 Steps (With Examples)

  • How to Calculate the Median of a Data Set in Statistics

How to calculate weighted average in Excel using SUMPRODUCT

Understanding how to calculate weighted average in Excel can help you become more efficient at work. You can calculate this amount if you have a data set that includes a list of items or responses with a corresponding value or quantity for each. If each value doesn't have a unique name, you can use numbers or letters to label each one. Follow these steps to calculate the weighted average using the SUMPRODUCT function:

1. Enter item names in the first column

After opening a new worksheet in Excel, enter the names or descriptions of the items in your data set in the first column (column A). For example, if you're calculating the weighted average of the ratings of test scores, you might label the first column "Tests" and enter the name of each test in the cells below, with each name in its own cell.

2. Enter the values in the second column

Next, label the second column (column B) with a description of the values in the data set. Enter each value from the data set in the second column. Ensure that each value in column B corresponds to the correct item name in column A. With the test score example, the label for column B would be "Scores", and if cell A2 contains the item name "Test 1", enter the rating for Test 1 in cell B2.

3. List the weights in the third column

Label the third column (column C) "Weight". Then, assign a percentage weight to each value. Determine the percentage weight by considering each value's relative importance, ensuring that the percentage weights add up to a total of 100%. Assign larger percentages to more important items. In the example of the test scores, you might assign a larger percentage to an important test, like a final exam, and a smaller weight to a less important test, like a quiz. You can indicate the weight using either a decimal or the percent symbol.

4. Apply the SUMPRODUCT function

Once you have entered the item names in column A, the values in column B, and the weights in column C, select a cell outside of your data and enter the label "weighted average". Directly to the right of this cell, enter "=SUMPRODUCT" and indicate the data ranges from columns B and C, separated by a comma, in parentheses.

For example, if you have entered your item names in cells A2 to A25, values in cells B2 to B25, and the corresponding weights in cells C2 to C25, label cell A27 "weighted average". Then, in cell B27, enter "=SUMPRODUCT(B2:B25,C2,C25). Press Enter to return the result.

5. Divide the SUMPRODUCT by the SUM

If the percentage weights you used do not total 100%, use the SUM function to divide the SUMPRODUCT by the total of the weights to determine the weighted average. This can be useful when working with an incomplete data set. For example, if you were calculating the weighted average of test scores but some tests were not complete, you might use this method to determine the weighted average of the tests completed up to a point.

How to calculate the weighted average using SUM

The SUM function is useful for finding the weighted average of smaller sets of data. It's less practical for larger sets. Follow these steps when learning how to calculate weighted average in Excel using the SUM function:

1. Enter your data

After opening a new worksheet, create labels for the columns. Label column A with the category for your item names (for example, "Tests"), column B with the type of values in your data set (for example, "Scores"), and column C with weights. Then enter the appropriate data in each column.

Related: Data Entry Skills and How to Improve Them

2. Multiply each value by each weight, then find the sum of the products

Use the SUM function to find the total of the values multiplied by their corresponding weight. To do this, multiply each value in column B by each weight in column C. For example, to find the weighted average of the first three items in your dataset, you can enter =SUM(B2*C2, B3*C3, B4*C4).

Why calculate the weighted average?

Weighted average is useful for statistical analysis when some values in a set hold more importance than others. You might use this when you have a set of survey data that contains a larger number of responses from a particular demographic or category. By assigning a weight to the values from the underrepresented category, you give them a larger impact on the overall average.

For example, you could use weighted average when determining an overall average house price for an area if that area contained an equal number of two-bedroom and three-bedroom houses, but your available price data was mostly for two-bedroom houses. You might calculate a weighted average assigning more weight to the values for three-bedroom houses to make the overall average more representative of the overall market.

Tips for using Excel to calculate weighted average

There are a few tips you can use to simplify the calculation of weighted average in Excel. These may help save time and reduce any confusion when working with large data sets. Try these strategies when using Excel to calculate weighted average:

Take advantage of Excel's auto-populating features

The shortcut features in Excel can speed up data entry and calculations. Some shortcuts include autofill, which allows you to expand a sequential numbered list or set of dates by typing a few list items and then dragging down to auto-populate the cells below. Excel can often also auto-populate functions when you type the first few letters.

Copy and paste where possible

If you have your data set arranged in columns in a table, you may be able to copy and paste the item names and values into your Excel worksheet. This can be particularly helpful if item names are long or difficult to spell, or if values are very large or have many decimal spaces. You can save additional time by using the keyboard shortcuts, like "control + v" for paste.

Use custom ranges

When entering data ranges into a formula, you can select the values by selecting the cells rather than entering the cell ranges manually. Simply enter the function, open parentheses, then click the cells you want to include in the calculations, followed by closed parentheses. This can be especially helpful with large data ranges.

Save your formulas and formatting by creating a template

If you often use weighted average to analyze data sets, or if you might add more data to your analysis, use your worksheet to create a template. After saving the completed worksheet or recording the numbers you require, clear just the data from your worksheet by selecting it and clicking "Clear contents" from the navigation pane or simply pressing backspace. This preserves any formatting. Leave the contents of the cells containing formulas untouched. You can then save this worksheet to use as a template for a similar future project.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles