Open In App

How to Calculate Weighted MAPE in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

In statistics, we often use Forecasting Accuracy which denotes the closeness of a quantity to the actual value of that particular quantity. The actual value is also known as the true value. It basically denotes the degree of closeness or a verification process that is highly used by business professionals to keep track records of their sales and exchanges to maintain the demand and supply mapping every year. There are various methods to calculate Forecasting Accuracy.

So, one of the most common methods used to calculate the Forecasting Accuracy is MAPE which is abbreviated as Mean Absolute Percentage Error. It is an effective and more convenient method because it becomes easier to interpret the accuracy just by seeing the MAPE value. Here, we face a problem of infinite error when the Actual value of any entity becomes zero.

WMAPE or Weighted MAPE abbreviated as Weighted Mean Absolute Percentage Error is also an accuracy prediction technique. Here, the problem of infinite error (divide by zero) is removed since the summation of actual value in the denominator can never be zero. It calculates the error based on weights but in case of MAPE error was calculated based on the average values. So, WMAPE is more reliable and gives efficient accuracy than MAPE.

The formula to calculate WMAPE in Excel is :

In this article we are going to discuss how to calculate WMAPE in Excel using a suitable example.

Example : Consider the dataset shown below :

Calculation of WMAPE

The functions needed for formulas in Excel are-

SUM : To calculate the sum of multiple values

ABS : To calculate the absolute value.

The steps are :

1. Insert the data set in the Excel sheet.

2. Calculate the sub part of the formula inside the summation which is also known as Weighted Error.

=ABS(Cell_No_Act-Cell_No_Fore)

where
ABS : Used to calculate the absolute value
Cell_No_Act : Cell number where Actual value is present
Cell_No_Fore : Cell number where Forecast value is present

The above formula will calculate the weighted error for the first entry in the data set. Now, you can drag the Auto Fill Options button to get the weighted error for the remaining entries.

3. Now use the SUM function to find the summation of both weighted errors and the actual values and divide them to get the WMAPE.

The entries of Weighted Error are in the Cell range : D3 to D12

The entries of Actual Value are in the Cell range : B3 to B12

The value of WMAPE is 0.09189 or 9.189% approximately.


Last Updated : 28 Jul, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads