When calculating the average of a set of values, it is common to use values with the same weight and importance.
But what happens if some values are heavier than others? This is where the weighted average formula comes in.
In this article, we’ll break down how to use this formula in Excel and provide some examples.
What is the weighted average formula?
The weighted average formula is a tool for calculating an average weighted by different values. A weighted average takes into account the distinct values of each data point and assigns 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 a weighted average in Excel, you must use the SUMPRODUCT and SUM functions with the following formula:
The formula works by multiplying each value by its weight and combining the values. Then you divide SUMPRODUCT by the weighted sum of the weighted averages.
Still confused? Let’s review the steps in the next section.
Calculate Weighted Average Using SUMPRODUCT in Excel
1. Enter your data into a spreadsheet and add a column with the weight for each data point.
2. Enter =SUMPRODUCT to start the formula and enter the value.
3. Click Enter to get your results.
How to Find Weighted Moving Average in Excel
A weighted moving average is a technique used to keep the time period of the average the same when adding new data or giving more weight to a specific time period. This allows you to more easily identify trends and patterns.
For example, assuming the number of pageviews your site has received over the past five days, you can easily determine the average pageview for the five days.
Now, say next week, what I want is the five-day average, you would use the Last Five days instead of the original five days from the previous week.
So you’re still relying on the same time period, but updating the data to generate a moving average.
With a weighted moving average, you weight some time periods more than others. You might say that day 5 has a weight of 60% and the rest of the percentages decrease day by day.
Therefore, you need to calculate this formula manually.
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.