How to Calculate Mean Percentage Error in Excel

Get FREE Advanced Excel Exercises with Solutions!

The mean percentage error is one kind of error calculation technique. It presents the mean or average error. In this article, we will discuss how to calculate mean percentage error in Excel with the proper illustration.


What Is Mean Percentage Error?

Mean Percentage Error is commonly known as MPE in short. It is the average difference between the actual value and the expected value of a certain event in percentage form. One more thing needs to be added: the MPE can be negative and positive. The mathematical formula of MPE is given below.

Formula to calculate mean percentage error


How to Calculate Mean Percentage Error in Excel: 4 Easy Steps

We will discuss all the steps to calculate the mean percentage error in Excel in detail in the below section.

⦿ Step 1: Collect Data

We want to calculate the mean percentage error of a super shop. Error is based on the amount of net profit expected and the actual for the 1st 6 months of 2022. For this, we collect the data and insert that data in the following Excel sheet.

Read More: Make an Excel Spreadsheet Automatically Calculate Percentage


⦿ Step 2: Calculate the Difference Between Actual and Expected Results

  • We add two columns on the right side to determine the difference and percentage error.

  • Go to Cell E5.
  • Insert the following formula on that cell.
=C5-D5

Calculate error for mean percentage

  • Now, press the Enter button and drag the Fill Handle icon.

We get the difference between the actual and the expected profit by applying the subtraction operation. When the result is positive, the actual profit is more than expected. For the negative result, the opposite situation occurs.


⦿ Step 3: Calculate Percentage of Error

Now, we will determine the percentage error.

  • Go to Cell F5 and insert the below formula.
=E5/C5 

Calculate error in percentage form

Here, we divide the difference by the actual value.

  • Press the Enter button and drag the Fill Handle icon.

But the result is in decimal format. We want to get the percentage error.

Now, select the Range F5:F10 and press the Ctrl + 1.

  • Format Cells window appears.
  • Choose the Percentage option from the Number tab.
  • Put the number of decimal places in the marked box.

  • Finally, press the OK button.

We get the error in percentile form that is expected.


⦿ Step 4: Determine Mean Percentage Error

In this step, we will calculate the mean percentage error using the SUM and COUNT functions.

  • Add a new row for Mean Percentage Error in the dataset.

  • Put the following formula based on the SUM and COUNT functions on Cell F12.
=SUM(F5:F10)/COUNT(F5:F10)

Formula to get mean percentage error in Excel

  • Press the Enter button.

Finally, we get the result. If the result does not appear in percentile form, change the format of the cell as shown before.

The value of MPE negative means the expected mean profit is lower than expected.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we described how to calculate mean percentage error in Excel. We explained all the steps in detail. I hope this will satisfy your needs. Please inform us in the comment section about your experience.


Related Articles


<< Go Back to Percentage Formula ExamplesCalculating Percentages | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo