18 Jan 2023

Posted on:

16 Jan 2023

0

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

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

Posted on:

16 Jan 2023

1

You can try the NORMSINV function in excel.

Posted on:

16 Jan 2023

0

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

Posted on:

18 Jan 2023

0

=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)