Microsoft KB Archive/84174

{|
 * width="100%"|

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

 * }

-

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 &quot;@ Functions and Macro Guide&quot; 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.