How to Get the RMS in Excel

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

The Root Mean Square calculates the effective rate or measurement of a varying set of values. It is the square root of the average of the squared values in a data set. RMS is primarily used in physics and electrical engineering. One of the more common uses for an RMS calculation is comparing alternating current and direct current electricity.

Advertisement

For example, RMS is used to find the effective voltage (current) of an AC electrical wave. Because AC fluctuates, it's difficult to compare it to DC, which has a steady voltage. The RMS provides a positive average that can be used in the comparison.

Video of the Day

Video of the Day

Image Credit: Ron Price

Unfortunately, Excel doesn't include a standard function to calculate RMS. This means you'll have use one or more functions to calculate an it.

Advertisement

Step 1: Enter the Data Set

Enter your data values so that the raw data (measurement, test value, etc.) is located in a single column or row. Allow space adjacent to the data values to place the results of other calculations.

Step 2: Calculate the Squares

Image Credit: Ron Price

Calculate the square (x^2) for each of the values in your data set. Enter the formula =^2 adjacent to each data value. For example, "=D3^2" calculates the square of the contents of cell D3.

Advertisement

Advertisement

Step 3: Average the Squares

Calculate the average of the individual squares. Below the last entry in the column containing the squares of the data set values, enter the formula =AVERAGE(First Cell:Last Cell). For example, =AVERAGE(D2:D30) calculates the mean (average) of the squares in the cells ranging from D2 to D30, inclusive.

Advertisement

Step 4: Calculate the Square Root of the Average

In an empty cell, enter the formula to calculate the square root of the average of the squares of the data. Enter the formula =SQRT(XN), where "XN" represents the location of the average calculated in the previous step. For example, =SQRT (D31) calculates the square root of the value in cell D31.

Advertisement

The value calculated in this step represents the RMS of the values in the data set.

Calculate the RMS with One Excel Formula

It is possible to calculate the RMS in a single formula using the original data values. The sequence of the steps, those of Steps 1 through 3, are as follows: calculate the square of each value, calculate the average of the squares and calculate the square root of the average.

Advertisement

The formula =SQRT((SUMSQ(First:Last)/COUNTA(First Cell:Last Cell))) uses the SUMSQ function to produce the sum of the squares of the values in the cell range. Then that number is divided by the number of cells containing data in the cell range specified (COUNTA). Finally, the square root of this value is calculated, which is the RMS.

Advertisement

For example, the formula =SQRT((SUMSQ(C2:C30)/COUNTA(A2:A30))) calculates the sum of the squares in the range C2 through C30, divides that sum by the number of entries in the range A2 through A30 that are not blank and then finds the square root of that value to produce the RMS.

Advertisement

Advertisement

references