.PL63
.PN1
L.....T.T.........................................R..L...............A
.FO3
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA>@@
[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[
UNOTE 49‰ Page # of 2
Ref: UNOTE 49~
From‰ :‰ Customer Services~ 18 Oct 89~
Re‰ :‰ Standard deviation using ucalc.~
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA>@@
[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[
L.......T.......T.......T.......T.......T.......T.......T.......T....R
Some confusion exists about the calculation of standard deviation
within Uniplex (ucalc) and the difference between the result returned
by some other packages, notably Lotus 1-2-3.
Statistics being the baffling subject that it is, two formulae exist
to calculate standard deviation. The first assumes the data being
manipulated is derived from a sample of an infinite population. A
typical example of this would be the results of a market opinion poll,
where a sample of the population had been interviewed.
To calculate standard deviation using this formula the spreadsheet
cell would contain the following:
T.......L.......T.......T.......T.......T.......T.......T.......T....J
root(sum(eval(range,(X-avg(range))^2))/(count(range)-1))@@
AAAAAAAAAAAAAAIIIIIAAAAAAAAIIIIIAAAAAAAAAAAAAAIIIIIAAAAA
L.......T.......T.......T.......T.......T.......T.......T.......T....J
For instance:
T.......L.......T.......T.......T.......T.......T.......T.......T....J
.JN
.FN FX-SMALL
Salary Difference Square Of
From Mean Difference
1 -3.5 12.25
2 -2.5 6.25
3 -1.5 2.25
4 -0.5 0.25
5 0.5 0.25
6 1.5 2.25
7 2.5 6.25
8 3.5 12.25
Sum 42.00
Std. Dev. 2.44949
.JY
.FN NORMAL
L.......T.......T.......T.......T.......T.......T.......T.......T....J
The second method of calculating Standard Deviation is used to
calculate the standard deviation of an entire population.
The formula to calculate this is:
T.......L.......T.......T.......T.......T.......T.......T.......T....J
root(sum(eval(range,(X-avg(range))^2))/count(range))@@
AAAAAAAAAAAAAAIIIIIAAAAAAAAIIIIIAAAAAAAAAAAAAIIIIIAA
L.......T.......T.......T.......T.......T.......T.......T.......T....J
This may be further simplified to:
T.......L.......T.......T.......T.......T.......T.......T.......T....J
root(sum(eval(range,X^2))/count(range)-avg(range)^2)~@@
AAAAAAAAAAAAAAIIIIIAAAAAAAAAAAAAIIIIIAAAAAAIIIIIAAAA
L.......T.......T.......T.......T.......T.......T.......T.......T....J
in as much as the calculation involved is less for the spreadsheet.
Using the above example, assuming we only have a sample of a near
infinite range:
.PA
T.......L.......T.......T.......T.......T.......T.......T.......T....J
.JN
.FN FX-SMALL
Value Value
Squared
1 1
2 4
3 9
4 16
5 25
6 36
7 49
8 64
Sum 204
Divided By
Count 25.5
Less Average
Squared 5.25
Std. Dev. 2.291288
.JY
.FN NORMAL
L.......T.......T.......T.......T.......T.......T.......T.......T....J
Uniplex has used the first method for it's function stdev(), as it is@@
AAAAAAA
more representative of data likely to occur in an office environment.
Lotus have used the latter. As can be seen from the above, both
formulae are fairly easily created in a form suitable for the
spreadsheet. It is also possible to convert between the Uniplex
stdev() formula and the one used by Lotus 1-2-3 using the following:@@
AAAAAAA
T.......L.......T.......T.......T.......T.......T.......T.......T....J
stdev(range)*root((count(range)-1)/count(range))@@
AAAAAAIIIIIAAAAAAAAAAAAAAIIIIIAAAAAAAAAAAIIIIIAA
L.......T.......T.......T.......T.......T.......T.......T.......T....J
From Uniplex II Plus Version 7 the Lotus 1-2-3 import filter will make
the appropriate corrections to any incoming standard deviation command
to ensure that the result Uniplex gives matches that from Lotus 1-2-3.