Let's say we've collected data on the number of hours that each member of a sample of
college students sleep each night. In Figure 1 below, these data are illustrated in Column A
with the Column Heading "Sleep (in hours).” !
First, we need to calculate the Mean (illustrated in cell D2 in Figure 1 below) and the
Standard Deviation (illustrated in cell E2 below) because we’ll need the Mean and Standard
Deviation to calculate the percentile of each value in our data set.!
Then, to calculate each percentile, we create a new Column Header called Percentiles, as
illustrated in Column B of Figure 1 below. Next, we use the NORMDIST function. !
For any function, we start by typing an equal sign = in the cell where we're building our
formula. Immediately after typing the equal sign, we type the name of the function, in this
case NORMDIST, then we type an open parenthesis and begin building our formula.!
As shown in Figure, 1, the formula for calculating the percentile of the first data value in our
data set is =NORMDIST(A2,$D$2,$E$2,TRUE).!
The NORMDIST function requires four arguments, enclosed in parentheses and separated
by commas. The first argument (e.g., A2) tells the formula which cell we want to calculate a
percentile for. !
The second argument tells the formula which cell contains our Mean. In Figure 1, our Mean
is in cell D2. We place a dollar sign $ around both the letter D and the number 2 of the cell
name D2 (e.g., $D$2) to tell our formula to always look in cell D2 for the Mean.!
How To Calculate Percentiles in
Microsoft Excel, Google Sheets, or Apple Numbers
Chelsea Andrews, M.S.
Figure 1 - Apple Numbers
Figure 1 - Microsoft Excel and Google Sheets
