Z.TEST just returns 0. I’ve checked in excel with same data, returns correct value. Is it a problem with the function? Sample file : ztest_test.ods Expected result : 7.61985E-24, Formula: =Z.TEST(A2:A101,150,2)
And how should anyone be able to analyze and answer without any sample data, expected value and the formula you are using?
MS describes Z.TEST:
Z.TEST is calculated as follows when sigma is not omitted:
Z.TEST( array,x,sigma ) = 1- Norm.S.Dist ((Average(array)- x) / (sigma/√n),TRUE)
…
where x is the sample mean AVERAGE(array), and n is COUNT(array).
I opened your sample in Excel 2016 (noticed the value you mentioned in B2), and put this into C2:
=1-NORM.S.DIST((AVERAGE(A2:A101)-150) / (2/SQRT(COUNT(A2:A101)));TRUE)
… and got 0 (zero).
Please check if my formula matches the description given by MS (and your formula =Z.TEST(A2:A101;150;2) in B2), and who to blame that MS calculates this wrong…
Confirmed your formula matches MS description. Thank You.