5 Mean and z-score ranges

In the previous section on hypothesis testing using the normal distribution, the z-score was frequently mentioned. This is because the z-score is the test statistic that is used to determine whether the null hypothesis should be accepted or rejected.

One way to gain an understanding of the calculated z-score and its alpha value is through the use of a z-score table. Using Excel functions, you can create a table that indicates z-scores and the corresponding area under the normal distribution curve. As you continue through the course, you will see how the z-score table can be used in hypothesis testing. For now, however, you will focus your attention on the use of Excel to create a table of z-scores, using the following steps.

Step 1: In an Excel spreadsheet, you can create a z-score table yourself that shows the z-score associated with any level of probability. To give an illustration, the screenshot below (Figure 10) shows a table with the values – 0.0; 0,1; 0.2; 0.3; 0.4; 0.5 – in a row and the values – 0.00; 0.01; 0.02; 0.03; 0.04; 0.05; 0.06; 0.07; 0.08; 0.09 – in columns. These are the first and second decimal digits of the probability whose z-score will be in the table. For example, the z-score in the first row (0.0) and second column (0.01) in the as-yet empty table refers to the probability p=0.01 (i.e., 0.0+0.01=0.01). The table is really just a long list for probabilities from p=0 to p=0.5 formatted as a table.

Maximise

Figure 10 Creating a z-score table step 1

Show description|Hide description

A picture of a z-table with rows (labelled: 0.0; 0.1; 0.2; 0.3; 0.4; 0.5) and columns (labelled: 0.00; 0.01; 0.02; 0.03; 0.04, 0.05; 0.06; 0.07; 0.08; 0.09).

Figure 10 Creating a z-score table step 1

Step 2: To use the ‘NORM.S.DIST’ Excel formula, begin by typing ‘=NORM.S.DIST(’ into the desired cell. The Excel software will prompt you to complete the formula’s value entry by entering the appropriate values for ‘z’ and ‘cumulative’.

Maximise

Figure 11 Creating a z-score table step 2

Show description|Hide description

A picture of a z-table with rows with rows (labelled: 0.0; 0.1; 0.2; 0.3; 0.4; 0.5) and columns (labelled: 0.00; 0.01; 0.02; 0.03; 0.04, 0.05; 0.06; 0.07; 0.08; 0.09). It shows the entry of Excel formula ‘NORM.S.DIST(z, cumulative)’.

Figure 11 Creating a z-score table step 2

Step 3: After initiating the NORM.S.DIST formula in the designated cell, you can assign a value to ‘z’ by adding the value in the row cell to the value in the column cell.

Maximise

Figure 12 Creating a z-score table step 3

Show description|Hide description

A picture of a z-table with rows (labelled: 0.0; 0.1; 0.2; 0.3; 0.4; 0.5) and columns (labelled: 0.00; 0.01; 0.02; 0.03; 0.04, 0.05; 0.06; 0.07; 0.08; 0.09). It shows the entry of value in the Excel formula ‘NORM.S.DIST($B4+C$3’.

Figure 12 Creating a z-score table step 3

Step 4: To indicate the cumulative distribution function, set the ‘cumulative’ argument to ‘TRUE’.

Maximise

Figure 13 Creating a z-score table step 4

Show description|Hide description

A picture of a z-table with rows (labelled: 0.0; 0.1; 0.2; 0.3; 0.4; 0.5) and columns (labelled: 0.00; 0.01; 0.02; 0.03; 0.04, 0.05; 0.06; 0.07; 0.08; 0.09). It shows the entry of value in the Excel formula ‘NORM.S.DIST($B4+C$3’ and selecting ‘TRUE - cumulative distribution function’.

Figure 13 Creating a z-score table step 4

Maximise

Figure 14 Creating a z-score table step 4

Show description|Hide description

A picture of a z-table with rows (labelled: 0.0; 0.1; 0.2; 0.3; 0.4; 0.5) and columns (labelled: 0.00; 0.01; 0.02; 0.03; 0.04, 0.05; 0.06; 0.07; 0.08; 0.09). It shows the entry of value in the Excel formula ‘NORM.S.DIST($B4+C$3", TRUE)’.

Figure 14 Creating a z-score table step 4

Step 5: After completing the formula with the appropriate values, press ‘Enter’ to calculate the result for the selected cell (Figure 15). To apply the Excel function to all cells within the table, click and drag the green box to cover the desired area (Figure 16).

Maximise

Figure 15 Creating a z-score table step 5

Show description|Hide description

A picture of a z-table with rows (labelled: 0.0; 0.1; 0.2; 0.3; 0.4; 0.5) and columns (labelled: 0.00; 0.01; 0.02; 0.03; 0.04, 0.05; 0.06; 0.07; 0.08; 0.09). After the calculation, it displays the result (0.5000).

Figure 15 Creating a z-score table step 5

Maximise

Figure 16 Creating a z-score table step 5

Show description|Hide description

A picture of a z-table with rows (labelled: 0.0; 0.1; 0.2; 0.3; 0.4; 0.5) and columns (labelled: 0.00; 0.01; 0.02; 0.03; 0.04, 0.05; 0.06; 0.07; 0.08; 0.09). All of the results are displayed after the calculation has been completed as follows:

Z Score	0.00	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09
0.0	0.5000	0.5040	0.5080	0.5120	0.5160	0.5199	0.5239	0.5279	0.5319	0.5359
0.1	0.5398	0.5438	0.5478	0.05517	0.5557	0.5596	0.5636	0.5675	0.5714	0.5753
0.2	0.5793	0.5832	0.5871	0.5910	0.5948	0.5987	0.6026	0.6064	0.6103	0.6141
0.3	0.6179	0.6217	0.6255	0.6293	0.6331	0.6368	0.6406	0.6443	0.6480	0.6517
0.4	0.6554	0.6591	0.6628	0.6664	0.6700	0.6736	0.6772	0.6808	0.6844	0.6879
0.5	0.6915	0.6950	0.6985	0.7019	0.7054	0.7088	0.7123	0.7157	0.7190	0.7224

Figure 16 Creating a z-score table step 5

In the following activity, you can practice using these Excel formulas to create a table of values of the normal distribution that correspond to a range of z-scores. These will be useful when testing hypotheses later.