How to Calculate Weighted MAPE in Excel

This guide will explain how to calculate weighted MAPE in Excel.

Excel is a powerful tool that allows us to perform complex and difficult calculations easily. Since Excel has several built-in functions and tools, we can easily perform calculations that would usually be complicated and long. 

For instance, we can easily calculate the weighted MAPE in Excel using the built-in functions to make the process simpler and faster. Furthermore, calculating MAPE is commonly done in business to forecast or predict how things will change or occur during a period.

To ensure the models are accurate, most business professionals would measure how closely their model looks compared to the actual data result using MAPE or the weighted mean absolute percentage error. 

So MAPE stands for mean absolute percentage error, one of the most common measures used to forecast or predict the accuracy of a model.

However, we will be focusing on learning how to calculate the weighted MAPE. Since MAPE performs poorly when the data set has low volume, it is much better to use the weighted MAPE as an alternative for accuracy. 

In this case, we will calculate the weighted MAPE in Excel using the ABS and SUM functions.

Let’s take a sample scenario wherein we must calculate weighted MAPE in Excel.

Suppose your company is launching a new product. And you are tasked to predict the number of sales for the new product. To do this, you calculated the weighted MAPE in Excel. 

For instance, you predict the company will sell 5 products. And 5 products were actually sold. Then, the weighted MAPE would be 0 percent because there are no errors in your prediction. 

Before we continue on to a real example of calculating the weighted MAPE, let’s first learn how to write the functions we will be using in Excel.

 

The Anatomy of the ABS Function

The syntax or the way we write the ABS function is as follows:

=ABS(number)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • ABS() is our ABS function. And this function is used to return the absolute value of the selected number. So an absolute value of a number would be a number without its sign.
  • number is the only required argument. And this refers to a real number we selected for which we want to get the absolute value. 

The Anatomy of the SUM Function

The syntax or the way we write the SUM function is as follows:

=SUM(number1, [number2],...)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • SUM() refers to our SUM function. So this is used to add all the selected numbers in a range of cells.
  • number1 is a required argument. And this refers to 1 to 255 numbers we want to sum. However, logical values and text values are ignored in the cells.
  • number2 is an optional argument. And this can be an additional range of cells we select that we also want to sum. 

Great! Now we can move on and dive into a real example of calculating the weighted MAPE in Excel.

 

A Real Example of Calculating Weighted MAPE in Excel

Let’s say we have a data set containing three columns showing monthly sales. Firstly, we have a column for each month of the year. Then, our second column contains the actual number of sales. Lastly, the third column has the

forecasted number of sales. So our initial data set would look like this:

Initial data set

 

We want to calculate the weighted MAPE for our data set. So the weighted mean absolute percentage error or weighted MAPE is used to measure the accuracy of statistical and financial forecasts or predictions compared to the real or actual outcomes of a data set. 

Although MAPE is more commonly used since it is easy to understand, MAPE performs poorly with a data set that has a low volume o data. So it would make the forecast error quite high even though the forecast could only be off by a little margin.

Hence, we will be using the weighted MAPE as an alternative. Since the weighted MAPE weighs the percentage errors depending on the volume, we would get a result that reflects a better idea of the true error. 

Additionally, the weighted MAPE helps us make a more accurate forecast or prediction for future data sets or financial trends. For instance, we can find the actual number of sales made for each month compared to the forecasted number of sales.

If we had predicted the actual number of sales for each month correctly, our WMAPE would have been zero percent which would indicate no errors in our forecast. However, we can see that we did not accurately forecast the number of sales for each month. Thus, we will calculate the weighted MAPE.  

To calculate the weighted MAPE, we will be using the formula weighted MAPE = Σ(|actual – forecast| / |actual|) * 100 * actual  /  Σ(actual) wherein Σ is a symbol that represents the sum, actual refers to the actual data value, and forecast refers to the forecasted data value. 

Firstly, we need to calculate the weighted error for each row or month using the weighted MAPE formula. Additionally, we will apply the formula together with the ABS function to ensure we would get the absolute value as the result. 

Secondly, we need to sum the actual number of sales using the SUM function. Then, we can calculate the weighted MAPE by dividing the total weighted eros by the sum of the actual number of sales. 

Finally, our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can explain the process of how to calculate the weighted MAPE in Excel.

 

How to Calculate Weighted MAPE in Excel

In this section, we will discuss the step-by-step process of how to calculate the weighted MAPE in Excel. Additionally, each step has detailed instructions and pictures to guide you along the way.

1. Firstly, we will calculate the weighted error for each month in our data set. To do this, we will type in the formula “=ABS(C2-D2)/C2*100*C2”. Then, we will press the Enter key to return the result.

Weighted MAPE in Excel

 

2. Secondly, we will drag down the Fill Handle tool to apply the formula and get the weighted error for the rest of the months.

Drag down formula

 

3. Thirdly, we will use the SUM function to get the sum of the actual number of sales. So we will input the formula “=SUM(C2:C10)”. Lastly, we will press the Enter key to return the result.

Weighted MAPE in Excel

 

4. Next, we will calculate the weighted MAPE by dividing the total weighted errors by the sum of the actual number of sales. To do this, we will type in the formula “=SUM(E2:E10)/C11”. Then, we will press the Enter key to return the result.

Weighted MAPE in Excel

 

5. And tada! We have successfully calculated the weighted MAPE in Excel.

Final output

 

And that’s pretty much it! We have explained the step-by-step process of how to calculate the weighted MAPE in Excel. Now you can apply this method to your work whenever you need to calculate the weighted MAPE. 

Are you interested in learning more about what Excel can do? You can now use the SUM function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like