The RSD calculation result in the Excel table is incorrect。

Question

Set 12 identical numbers in the Excel table and use the formula to calculate RSD, but the result is incorrect. Please refer to the attached figure for details.

If 12 numbers are exactly the same, according to the RSD calculation formula, the result should be 0. Why is the calculation result not 0 according to the Excel table?

For example, if all 12 numbers are 1.235, using the formula RSD=STDEV (B1: B12)/AVAGE (B1: B12), the result obtained is 0.000 000000 187787875350405. According to the correct RSD calculation formula, if the numbers are the same, RSD should be 0.

Answer 1

Hi,

It is likely due to floating-point arithmetic, which can cause small rounding errors in calculations. This is a common issue in computing when dealing with floating-point numbers, where the precision is finite, and calculations can result in numbers that are very close to, but not exactly, zero.

To ensure that the RSD calculation in Excel returns 0 when the standard deviation is smaller than a certain threshold, such as 0.00001, you can use the following formula with an IF statement:

=IF(STDEV.S(B1:B12)<0.00001, 0, STDEV.S(B1:B12)/AVERAGE(B1:B12))

This formula will check if the standard deviation of the range B1:B12 is less than 0.00001. If it is, the formula will return 0, which is the expected RSD when all numbers are identical or the variation is negligible. If the standard deviation is not less than 0.00001, it will proceed to calculate the RSD normally. And you can also customize the threshold value on your own.