How to Calculate MSE in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

Mean squared error (MSE) is used in statistics to give a numerical value to the difference between values indicated by an estimation and the actual value of the quantity. The larger the MSE, the further away the estimation is from the true data points. To calculate the MSE by hand, you would have to make several calculations that opens the process to error. Using a spreadsheet format, such as Microsoft Excel, cuts down on errors and allows for faster calculation.

Advertisement

Step 1

Type the data points in column A, starting in cell A1.

Video of the Day

Step 2

Type the estimated data points in column B, starting in cell B2.

Step 3

Type "=A1-B1" in cell C1, then grab the fill handle, which is the little black square at the bottom right of the cell. Drag the fill handle down the column to match the last row of data you entered in columns A and B.

Advertisement

Step 4

Type "=C1^2" into cell D2 then grab the fill handle. Pull the fill handle down the column to match the last row you filled in columns A through C.

Advertisement

Step 5

Click the first empty cell in column D, then click the sigma symbol in the Ribbon. This action adds the sum of the square of the errors.

Advertisement

Step 6

Calculate the MSE from the entered data. Click cell E1, then type "=." Click the summation cell, then type "/." Type the number of data points that you entered in column A. Press Enter to get the MSE.

Video of the Day

Advertisement

Advertisement

Report an Issue

screenshot of the current page

Screenshot loading...