Re: [office-formula] ZTEST

[Stefan Nikolaus <stefan.nikolaus@kdemail.net>]

On Friday 09 February 2007 00:44:16 Andreas J. Guelzow wrote:
> I just had a look at the ZTEST description. Considering the 2 argument
> version, the description seems to differ from what OOo and GNumeric
> implements (and according to what the description itself says also what
> Excel does.)
>
> I can't confirm what Excel does (it doesn't run under Linux so it is not
> available to me) but OOo and Gnumeric calculate the onesided p-value for
> the z-test.

Hmm, Calc's help claims, that it computes the two-tailed test. I've interpret 
this as: it uses both tails of the distibution to compute the value.
Calc's code look like this:
		PushDouble(0.5 - gauss((mue-x)/sqrt(sigma/rValCount)));
Which shows, that it tries to use the onesided p-value and also tries to 
implement the one-tailed test, because I see not doubling of the value in the 
contextual code.
gauss() is the method returning the value for GAUSS (what a surprise ;-) ). 
And that is defined as NORMDIST(x)-0.5. NORMDIST(x) returns the value of the 
normal distribution pdf (!) at position x. Is that what you call the p-value? 
I think not. You want the value of the cdf! Otherwise, it makes no sense at 
all (AFAICT).

And if you want to calculate the one-tailed test, make sure, that you don't 
make the same error as Excel: It takes the value of the cdf at the position 
of the signed (!) z value. If z is negative, this results in a wrong value.

> The three-argument version yields significantly different values between
> OOo and Gnumeric and I haven't quite determined yet which values that
> are.
>
> Am I correct in assuming that our function descriptions ought to be
> close to at least some implementation?

The upcoming KSpread 2.0 implements it exactly this way. ;-)
The z-Test is not arbitrarily definable. AFAIK, the z-Test is a well defined 
method in statistics. To use the name ZTEST and differ from this definition 
is not acceptable, even not for compatibility reasons. If you define the one- 
or two-tailed test, is only a matter of taste.
Calc's help claims to implement the two-tailed test, but it actually 
implements the one-tailed test and gets it wrong.
Gnumeric claims to calculate the one-tailed test. It produces the same values 
as Calc, which are wrong.
Excel claims to calculate the one-tailed test, but gets it wrong for negative 
z values.
KSpread claims to implement the two-tailed test, and gets it right (this 
statement is biased ;-) ).

Regards,
Stefan

-- 
Stefan Nikolaus
humba, humba, tätärää, tätärää, tätäräää ;-)

PGP signature