# Excel Spreadsheet Building Blocks II: NORMSDIST and NORMDIST

In this 2nd of 3 Excel guides, we review two functions that are extremely useful if you want to convert a standard score (like a z score or T-score) to a percentile. In Part 1 of the series, we demonstrated how a regression-based normative (RBN) equation can be embedded in a spreadsheet to calculate an estimated IQ score. We stopped short, however, and did not convert the IQ score to a percentile. To carryout such a conversion, the Excel function NORMDIST is used. There is also NORM** S**DIST, which would be useful only for converting a z score to a percentile. Both functions, NORM

**DIST and NORMDIST, are reviewed below. If you would like to practice along at home, the spreadsheet can be found here.**

__S__## NORM__S__DIST and NORMDIST

__S__

NORMSDIST or NORMDIST are extremely useful because they obviate the need to lookup tabled percentiles. Lets start with NORM-**S**-DIST, since it is more straightforward than NORMDIST.

### NORM__S__DIST

__S__

NORMSDIST is used to covert a z score to a percentile. The function is:

```
=NORMSDIST(z)*100
```

In the sample spreadsheet, NORMSDIST is used in cells E15, E16, and E17 to convert a z score calculated in cells I15, I16 and I17 (more on how those z score were computed in a future post about the LOOKUP function).

Simply type this formula in the cell you want the percentile to appear (here, E15 ) and replace the “z” with the actual z score value or the cell reference that contains the z score (here, I15). The resultant value, after multiplying by 100, will be a percentile (in this case 35th %ile).

```
=NORMSDIST(I15)*100
```

To see if you understand, try entering z scores of –1, 0, and +1 and see if each result is 16, 50, and 84.

#### Embedded NORMSDIST computations

In the spreadsheet, a z score was computed in a separate cell (I15). However, this is an unnecessary step because the z score can be computed within NORMSDIST. The formula to convert an actual test score to a z score could have referenced the examinee’s scores and the normative mean and standard deviations directly like this:

```
=NORMSDIST((C15-J15)/K15)*100
```

Which wound also return a value of 35.

### “NORMDIST”

NORMDIST is used to convert a standardized score, like a T-score (M=50, SD=10) or scaled score (M=10, SD=3), to a percentile. The formula is:

```
=NORMDIST(x, mean, standard deviation, cumulative)*100
```

where:

- “x” = the number or cell you want to convert (e.g. a T-score of 40)
- Mean = the mean of the scale you are converting from (in this case 50)
- SD = the standard deviation of the scale you are converting from (in this case 10).
- Cumulative will typically remain in the default mode of 1 or true.
- The function should be multiplied by 100, otherwise the result will be a decimal.

Thus, the formula:

```
=NORMDIST(40, 50, 10, TRUE)*100
```

Will result in the value “16”.

In the sample spreadsheet (FIgure 3), cell C12 contains an IQ value of 123 and we would like to convert this to a percentile in cell E12. The formula is:

```
=NORMDIST(C12, 100, 15, 1)*100
```

The equation above converts cell C12 to a percentile (94) based on a scale that has a mean of 100 and an SD of 15.

The next post in the Excel Table series will show you how to use the LOOKUP functions and explain a little about formatting for MultiMarkdown output.