How To Calculate Percent Error In Excel

In statistics, we oft utilise Forecasting Accuracy which denotes the closeness of a quantity to the actual value of that particular quantity. The bodily value is likewise known every bit the truthful value. Information technology basically denotes the degree of closeness or a verification procedure that is highly used by business professionals to keep track records of their sales and exchanges to maintain the need and supply mapping every year. There are various methods to calculate Forecasting Accuracy.

Then, one of the most common methods used to summate the Forecasting Accurateness is MAPE which is abbreviated equally Mean Accented Pct Error. It is an effective and more convenient method considering it becomes easier to interpret the accuracy just by seeing the MAPE value.

In this article, we are going to encounter how to calculate Hateful Accented Pct Error, MAPE in Excel.

The formula to calculate MAPE is :

The to a higher place formula tin can be interpreted every bit the average value of Accented Percentage Error (APE) of all the observations in the information set.

Notation: The bodily value tin can't be zero. We tin can notice from the to a higher place formula that if the Bodily value becomes zilch, information technology will be undefined.

Example:

Consider the dataset shown below :

Calculation of MAPE in Excel:

The functions needed for formulas in Excel are-

ABS : To calculate the absolute value.

Boilerplate : To summate the mean.

The steps are :

i. Insert the data set in the Excel sheet.

ii. Calculate APE for each individual ascertainment using Excel Formula. The formula will be :

=ABS(Cell_No_Act-Cell_No_Fore)/Cell_No_Act*100  where ABS : Used to calculate the accented value Cell_No_Act : Cell number where Actual value is present Cell_No_Fore : Prison cell number where Forecast value is present

Similarly, you can write the formulas for the other entries and get the APE for all the records.

APE

iii. Now, simply we demand to notice the boilerplate or the mean value for all these values in social club to summate MAPE.

The formula to find average value in Excel is :

=Average(Cell_Range)

The value of MAPE for the given information set is 9.478% approximately. Therefore, we tin can say that the average divergence between the actual value and forecasted value is 9.478%.