Excel: STDEV Function is Translated Incorrectly to Lotus 1-2-3

ID: Q84174

The information in this article applies to:

Microsoft Excel for Windows, version 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for OS/2, version 2.2, 3.0
Microsoft Excel for the Macintosh, version 2.2, 3.0, 4.0, 5.0

SUMMARY

The @STD function in Lotus 1-2-3 is not equivalent to the STDEV function in Microsoft Excel. When saved as a Lotus 1-2-3 file, the STDEV function in Excel translates to STDEVP. There is no equivalent function for STDEV in Lotus 1-2-3, therefore the next closest function is used, @STD. If you are saving the sheet as a WK1 file, it is better to use the STDEVP function which returns the same value as the @STD function in Lotus.

In version 5.0, you won't receive the error message that Microsoft Excel can't convert this to a Lotus formula. It automatically converts it to the @STDS function. In version 4.0, it reports that it can't convert the formula and then when opened in 1-2-3, it converts it to @STDS.

MORE INFORMATION

Page 77 of the Lotus 1-2-3 "@ Functions and Macro Guide" states that the standard deviation of a sample of a population can be calculated using:


   @std(list)*sqrt(@count(list)/(@count(list)-1))

where list is the series of values for which you are calculating.

It appears as if the STDEV (standard deviation of a sample of a population) is mapping to @STD, rather than the formula listed above. When the formula is brought back into Excel as the WK1 file, the formula is translated to STDEVP, which is the equivalent of @STD.

Trying to output the formula listed above would not improve the problem, a different value would still be produced in Excel.

Definitions

STDEV: Returns an estimate for the standard deviation of a population based on a sample given as arguments.

STDEVP: Returns the standard deviation of a population given the entire population as the argument.

@STD: Calculates the population standard deviation of a list of values.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 419

Additional query words: 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00 5.0 5.00 1 2 3 123

Keywords :
Version :
Platform :
Issue type :