HI, I'm struggling to find an automated way to lookup the number in th Z-table. Refer to 5:10, I finally managed to do this by formula, but this doesn't work for CI 90% or 95% (refer to 6:30) Here's what I've done so far: - F38 is number to lookup (0.9750 in this case) - ZTABLE is the the named range of the z-table in a separated sheet Get Z-Table column ---> Result: 0.06 =INDEX(ZTABLE,1,SMALL(IF(NOT(ISERROR(SEARCH(F38,ZTABLE))),COLUMN('Z-Table'!A:K),99^99),1)) Get Z-Table row ---> Result: 1.90 =INDEX(ZTABLE,SMALL(IF(NOT(ISERROR(SEARCH(F38,ZTABLE))),ROW('Z-Table'!1:36),99^99),1),1) Zα /2 = Z-Table column + Z-Table row (1.96) Is there any simpler way finding Zα /2 that can be solved by formula instead of manually find the number in the table? Thanks

You can try the NORMSINV function in excel.

Thanks Eugene! This make more sense than looking up the table.

=NORMSINV(probability)=NORMSINV(0.95) or you can also use the excel function Z.INV(probability, mean, standard_deviation) =Z.INV(probability, mean, standard_deviation)=Z.INV(0.95,0,1)

Follow this topic

Nunno Nugroho

Last answered:

18 Jan 2023

Posted on:

16 Jan 2023

Resolved: Excel formula to find number in the Z-table

in Statistics / Population variance known, z-score

3 answers ( 1 marked as helpful)

Eugene YC Lee

Super learner

Posted on:

16 Jan 2023

Helpful answer

Nunno Nugroho

Posted on:

16 Jan 2023

Rosaline Pahud de Mortanges

Posted on:

18 Jan 2023

Resolved: Excel formula to find number in the Z-table

Submit an answer

related questions