A fixture sits 3 mm left of center, on center and 3 mm right of center in three readings. The signed average is zero, but there is still an offset to describe.

Square the readings and return to the original unit

Enter −3, 0 and 3 as numbers in B2:B4. In a result cell enter:

=SQRT(SUMSQ(B2:B4)/COUNT(B2:B4))

The squares sum to 18. Divide by three for a mean square of 6, then take the square root. The RMS is about 2.45 mm.

The three readings square to 9, 0 and 9, with a mean square of 6.

Illustrated RMS calculation. The square root returns the result to millimeters.

Test how the sheet treats a missing reading

Clear the middle cell. COUNT now sees two readings and the RMS becomes 3 mm. Restore the zero when it was actually measured. When a group has no numeric readings, report that there are no data for it. Keep every numeric input in the same unit.

Including a measured zero gives about 2.45 mm. Clearing that cell gives 3 mm.

Illustrated missing-value check. Keep measured zeros in the dataset.

Microsoft’s SUMSQ function reference