When you're calculating the average for a set of values, you're generally working with values that have the same weight and importance.
But what happens if some values weigh more than others? This is where the weighted average formula comes in.
In this article, we will break down how to use this formula in Excel, plus provide some examples.
What is the weighted average formula?
The weighted average formula is a tool used to calculate averages that are weighted by different values. The weighted average takes into account the different values of each data point and gives them a weight, or importance, based on those values. This weighted average is then used to calculate the final average.
How to Calculate Weighted Average in Excel
To calculate the weighted average in Excel, you must use the SUMPRODUCT and SUM functions using the following formula:
=SUMPRODUCT(X:X,X:X)/SUM(X:X)
This formula works by multiplying each value by its weight and combining the values. Then, you divide the SUMPRODUCT but the sum of the weights for your weighted average.
Still confused? Let's go over the steps in the next section.
We're committed to your privacy. HubSpot uses the information you provide to us to contact you about our relevant content, products, and services. You may unsubscribe from these communications at any time. For more information, check out our Privacy Policy.
Free Excel Marketing Templates
10 free templates to help you master marketing with Excel.
- Marketing Reporting Template
- Email Planning Template
- SEO Template
- And More!
Loading your download form
You're all set!
Click this link to access this resource at any time.
Download Now
Using SUMPRODUCT to Calculate Weighted Average in Excel
1. Enter your data into a spreadsheet then add a column containing the weight for each data point.
2. Type =SUMPRODUCT to start the formula and enter the values.
3. Click enter to get your results.
How to Find Weighted Moving Averages in Excel
A weighted moving average is a technique used to keep the time period of the average the same as you add new data or give more weight to certain time periods. This can allow you to identify trends and patterns more easily.
For instance, say you have the number of views your website got in the last five days, you can easily determine the average views in a five-day period.
Now, say the next week, I ask for the five-day average, you would use data from the last five days, not the original five days from the previous week.
As such, you're still relying on the same time period but updating the data to generate the moving average.
For a weighted moving average, you give more weight to certain time periods than others. You may say that day 5 weights 60% with the remaining percentages decreasing by day.
As such, you'll need to manually calculate this formula.
WMA = [value 1 x (weight)] + [value 2 x (weight)] + [value 3 x (weight)] + [value 4 x (weight)]
Once you get the hang of it, using the weighted average formula is easy. All it takes is a little practice.
We're committed to your privacy. HubSpot uses the information you provide to us to contact you about our relevant content, products, and services. You may unsubscribe from these communications at any time. For more information, check out our Privacy Policy.
Free Excel Marketing Templates
10 free templates to help you master marketing with Excel.
- Marketing Reporting Template
- Email Planning Template
- SEO Template
- And More!
Loading your download form
You're all set!
Click this link to access this resource at any time.
Download Now
FAQs
To calculate the weighted average in Excel, you must use the SUMPRODUCT and SUM functions using the following formula: =SUMPRODUCT(X:X,X:X)/SUM(X:X) This formula works by multiplying each value by its weight and combining the values. Then, you divide the SUMPRODUCT but the sum of the weights for your weighted average.
How do I calculate a weighted average in Excel? ›
How to calculate the weighted average in Excel with the SUMPRODUCT function
- Enter your labels in the first column. ...
- Use the second column for listing the data values. ...
- Use the third column for listing the weight values. ...
- Apply the SUMPRODUCT function. ...
- Divide SUMPRODUCT by SUM.
How to calculate the weighted average? ›
Simply, in order to find the weighted average, one must first multiply all values in the data set by their corresponding weights. Then, add up the resulting products and divide by the sum of the weights. When dealing with percentages, one will usually find that the sum of weights is equal to 1 or 100%.
What is the formula for the average in Excel? ›
Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
How to use SUMPRODUCT in Excel? ›
To create the formula using our sample list above, type =SUMPRODUCT(C2:C5,D2:D5) and press Enter. Each cell in column C is multiplied by its corresponding cell in the same row in column D, and the results are added up.
How is general weighted average calculated? ›
To calculate the GWA or General Weighted Average, collect all the subject details including subject, subject grade and credit hours or units. Now multiply each subject grade with its corresponding unitl. Once, you perform for all the subjects, sum all the subject weights and divide it with all the credit hours.
What is the difference between average and weighted average? ›
The average is the sum of all values divided by the number of values. In contrast, the Weighted Average is value multiplied by the weight, and added to find a solution. An Average is a mathematical calculation, whereas the Weighted Average is used in finance.
What is the formula for weighted average price method? ›
To use the weighted average model, one divides the cost of the goods that are available for sale by the number of those units still on the shelf. This calculation yields the weighted average cost per unit—a figure that can then be used to assign a cost to both ending inventory and the cost of goods sold.
What is the formula for weighted average mass method? ›
The weighted average is determined by multiplying the percent of natural abundance by the actual mass of the isotope. This is repeated until there is a term for each isotope. For chlorine, there are only two naturally occurring isotopes so there are only two terms.
What is the formula for calculating a time weighted average? ›
A time-weighted average is equal to the sum of the portion of each time period (as a decimal, such as 0.25 hour) multiplied by the levels of the substance or agent during the time period divided by the hours in the workday (usually 8 hours).
We type = AVERAGEIF, opening parenthesis, the range of cells we want evaluated (cells B2 through B5) in the number of orders column, comma, the criteria by which the range is to be evaluated enclosed in quotes (greater than 50), comma, the range of cells we want to average (cells C2 through C5 in the Sales column), and ...
Why do we calculate average in Excel? ›
The Excel AVERAGE function is used to generate a number that represents a typical value from a range, distribution, or list of numbers. It is calculated by adding all the numbers in the list, then dividing the total by the number of values within the list.
What is the formula for Excel? ›
In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3.
How to get a weighted average in Excel? ›
How to calculate weighted average in Excel using SUMPRODUCT
- Enter item names in the first column. ...
- Enter the values in the second column. ...
- List the weights in the third column. ...
- Apply the SUMPRODUCT function. ...
- Divide the SUMPRODUCT by the SUM. ...
- Enter your data. ...
- Multiply each value by each weight, then find the sum of the products.
Is SUMPRODUCT a weighted average? ›
We'll use the SUMPRODUCT and SUM functions to determine the Weighted Average. The SUMPRODUCT function multiplies each Test's score by its weight, and then, adds these resulting numbers. We then divide the outcome of SUMPRODUCT by the SUM of the weights.
Why is my SUMPRODUCT formula not working? ›
All arrays in a SUMPRODUCT formula must have the same number of rows and columns, otherwise you get the #VALUE! error. If any array argument contains non-numeric values, they will be treated as zeros. If an array is a logical test, it results in TRUE and FALSE values.
How to calculate VWAP in Excel? ›
To do this, add the high, low, and close, then divide by three. Multiply this by the volume for that period. Record the result in a spreadsheet, under column PV. Divide PV by the volume for that period.
How to calculate wam? ›
Your WAM is calculated by multiplying each unit of study mark by its credit point value, then adding these totals together. You then divide this by the sum of all credit points attempted. All units have an equal weighting of one. All units that are allocated a mark contribute to your WAM.
How to calculate mkt in Excel? ›
All the data has to be listed vertically. At the end of the last cell simply insert the formula =MKT(B1: B100). This will produce an MKT value in the range of values from B1 to B100. There is no limitation to the range and you may include any number of data.