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.

How to Calculate Weighted Average in Excel

sumproduct weighted average formula

To calculate a weighted average in Excel, you must use the SUMPRODUCT and SUM functions with the following formula:

=SUMPRODUCT(X:X,X:X)/SUM(X:X)

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.

sumproduct calculate weighted average in excel step 1

2. Enter =SUMPRODUCT to start the formula and enter the value. sumproduct calculate weighted average in excel step 2

3. Click Enter to get your results.

sumproduct calculate weighted average in excel step 3

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.

excel marketing template