Practical: Calculate MSE and RMSE using Microsoft Excel

Machine Learning models that predict numerical values are called Regression Models. After building such a model, it is important to determine how close the predicted values are to the actual values. This is done using evaluation metrics such as Mean Squared Error (MSE) and Root Mean Squared Error (RMSE).

Although these metrics are usually calculated using Python libraries, Microsoft Excel also provides an easy way to calculate them. Excel allows us to organize data, perform calculations using formulas, and verify the performance of Machine Learning models without writing any code.

In this practical activity, you will learn how to calculate MSE and RMSE step by step using Microsoft Excel.

---

Practical Aim

To calculate the Mean Squared Error (MSE) and Root Mean Squared Error (RMSE) of a regression model using Microsoft Excel.

---

Learning Outcomes

After completing this practical, you will be able to:

• Understand Actual and Predicted values.
• Calculate prediction errors.
• Calculate squared errors.
• Find Mean Squared Error (MSE).
• Calculate Root Mean Squared Error (RMSE).
• Interpret the performance of a regression model.

---

Software Required

Software	Purpose
Microsoft Excel	Perform calculations and analysis
Windows / macOS	Operating System

---

Theory

Regression models predict continuous numerical values such as marks, temperature, sales, or house prices.

The difference between the Actual Value and the Predicted Value is called the Error. Smaller errors indicate better predictions.

Instead of adding positive and negative errors directly, we square each error so that all values become positive. The average of these squared errors is called the Mean Squared Error (MSE).

The square root of MSE is called the Root Mean Squared Error (RMSE), which is easier to interpret because it has the same unit as the original data.

---

Formula for MSE

          Σ (Actual − Predicted)²

MSE = ───────────────────────────

             Number of Records

---

Formula for RMSE

RMSE = √MSE

---

Sample Dataset

Enter the following data in Microsoft Excel.

Student	Actual Marks	Predicted Marks
Aman	80	78
Neha	72	75
Rahul	90	88
Priya	85	87
Rohan	76	74

---

Worksheet Layout

-------------------------------------------------------

A          B             C           D          E

-------------------------------------------------------

Student   Actual      Predicted    Error    Error²

-------------------------------------------------------

Aman         80            78

Neha         72            75

Rahul        90            88

Priya        85            87

Rohan        76            74

-------------------------------------------------------

---

Step 1: Enter the Data

Open Microsoft Excel and enter the sample dataset exactly as shown above.

---

Step 2: Calculate Error

The error is calculated as:

Error = Actual − Predicted

Suppose:

• Actual Marks are in Column B.
• Predicted Marks are in Column C.

Enter the following formula in cell D2.

=B2-C2

Press Enter and drag the formula down for all rows.

---

Step 3: Calculate Squared Error

Square each error value.

In cell E2, enter:

=D2^2

Copy the formula down for all rows.

---

Worksheet After Calculations

Student	Actual	Predicted	Error	Error²
Aman	80	78	2	4
Neha	72	75	-3	9
Rahul	90	88	2	4
Priya	85	87	-2	4
Rohan	76	74	2	4

---

Step 4: Calculate Mean Squared Error (MSE)

Below the Error² column, calculate the average using the following formula.

=AVERAGE(E2:E6)

Excel calculates the Mean Squared Error automatically.

---

Expected Result

MSE = 5

---

Step 5: Calculate Root Mean Squared Error (RMSE)

If the MSE value is stored in cell E7, calculate RMSE using:

=SQRT(E7)

Excel automatically calculates the square root of the Mean Squared Error.

---

Expected Result

RMSE = 2.236

---

Flow of Calculation

Enter Data

     │

     ▼

Calculate Error

     │

     ▼

Square Error

     │

     ▼

Calculate Average

     │

     ▼

MSE

     │

     ▼

Square Root

     │

     ▼

RMSE

---

Complete Excel Worksheet

Cell	Data / Formula	Description
A1	Student	Heading
B1	Actual Marks	Heading
C1	Predicted Marks	Heading
D1	Error	Heading
E1	Squared Error	Heading
D2	=B2-C2	Calculate Error
E2	=D2^2	Square of Error
E7	=AVERAGE(E2:E6)	Calculate MSE
E8	=SQRT(E7)	Calculate RMSE

---

Formula Explanation

Formula	Purpose
=B2-C2	Calculates the prediction error.
=D2^2	Calculates the square of the prediction error.
=AVERAGE(E2:E6)	Finds the Mean Squared Error (MSE).
=SQRT(E7)	Calculates the Root Mean Squared Error (RMSE).

---

Calculation Table

Student	Actual	Predicted	Error	Error²
Aman	80	78	2	4
Neha	72	75	-3	9
Rahul	90	88	2	4
Priya	85	87	-2	4
Rohan	76	74	2	4
Total Squared Error				25
Mean Squared Error (MSE)				5
Root Mean Squared Error (RMSE)				2.236

---

Interpretation of Results

The calculated MSE is 5. This means that the average squared difference between the actual marks and the predicted marks is 5.

The calculated RMSE is 2.236. Since RMSE is expressed in the same unit as the original data (marks), it indicates that the model's predictions differ from the actual marks by approximately 2.24 marks on average.

A smaller MSE or RMSE value indicates that the Machine Learning model makes more accurate predictions.

---

Observation

• The prediction error was calculated for every record.
• Negative errors became positive after squaring.
• The Mean Squared Error represented the average prediction error.
• The Root Mean Squared Error provided the prediction error in the original unit.
• The regression model performed reasonably well because the RMSE value was small.

---

Result

The Mean Squared Error (MSE) and Root Mean Squared Error (RMSE) were successfully calculated using Microsoft Excel. These metrics can be used to evaluate the performance of regression-based Machine Learning models.

---

Real-Life Applications

• Predicting house prices.
• Forecasting sales revenue.
• Estimating crop production.
• Weather forecasting.
• Stock market prediction.
• Student performance prediction.
• Electricity demand forecasting.

---

Case Study

A coaching institute develops a Machine Learning model to predict students' final examination marks based on attendance, unit tests, assignments, and practical marks.

After comparing the predicted marks with the actual examination results, the institute calculates the MSE and RMSE values using Microsoft Excel.

The obtained RMSE value is only 2.1 marks, indicating that the prediction model performs well and can be used to identify students requiring academic support.

---

Think Like a Data Scientist

Two regression models are developed for predicting house prices.

Model	RMSE
Model A	4.8
Model B	2.3

Which model should be selected and why?

Click to View Answer

Model B should be selected because it has a lower RMSE value, indicating that its predictions are closer to the actual values.

---

Competency-Based Question

A weather forecasting department develops two regression models for predicting daily temperatures.

• Model A has an RMSE of 3.8°C.
• Model B has an RMSE of 1.6°C.

Which model is more reliable? Explain your answer.

---

Practical File Observation

• Microsoft Excel formulas simplified the calculations.
• MSE measured the average squared prediction error.
• RMSE provided the prediction error in the original unit.
• Smaller RMSE indicated better prediction performance.

---

Common Errors

• Using addition instead of subtraction while calculating the error.
• Forgetting to square the error values.
• Using SUM() instead of AVERAGE() for MSE.
• Applying the square root before calculating the average.
• Selecting an incorrect range in Excel formulas.

---

Troubleshooting Tips

• Verify that the Actual and Predicted values are entered correctly.
• Check that Excel formulas reference the correct cells.
• Use AutoFill carefully while copying formulas.
• Ensure that all values are numeric.
• Confirm that the AVERAGE function includes all squared error values.

---

Viva Questions

1. What is Mean Squared Error (MSE)?
2. Why do we square the prediction errors?
3. What is Root Mean Squared Error (RMSE)?
4. Why is RMSE easier to interpret than MSE?
5. Which Excel function calculates the average?
6. Which Excel function calculates the square root?
7. Which evaluation metrics are used for regression models?
8. Does a smaller RMSE indicate a better model?
9. Can Accuracy be used for regression problems?
10. Why are MSE and RMSE important in Machine Learning?

---

Quick Revision

---

Memory Trick

---

Exam Tips

• Memorize the formulas for MSE and RMSE.
• Practice Excel formulas for calculating Error and Squared Error.
• Remember that lower MSE and RMSE values indicate a better regression model.
• Understand the difference between regression and classification metrics.
• Be able to interpret the meaning of MSE and RMSE values.

---

Summary

• MSE and RMSE are commonly used evaluation metrics for regression models.
• Microsoft Excel provides an easy way to calculate these metrics.
• MSE measures the average squared prediction error.
• RMSE expresses the prediction error in the original unit of measurement.
• Lower MSE and RMSE values indicate better model performance.

---

Next Topic: Practical: Calculate Precision, Recall, F1-Score and Accuracy from a Confusion Matrix