How to calculate percentiles in Apple Numbers??

Hi Marsh,

The formula for calculating percentiles is PR% = L + ( 0.5 x S ) / N

Where L is the number of scores that are less than the subject score, S is the number of scores that are the same as the target score, and N is the total number of scores. rthe result is a fraction showing the portion of scores less than the current score (with an adjustment adding half the number of scores the same as the target score).

Translating this into a spreadsheet formula which will express the percentile as a whole number produces a tablelike the following:

The formula in B2 of this table is:

B2: ((COUNTIF(A,"<"&A2)+0.5×COUNTIF(A,A2))÷COUNT(A))×100

The formula will return a result accurate to several decimal places—likely more precise than you require. I've formatted the cells in column B to display that value rounded to the nearest whole number, by setting the number of decimal places to 0.

Although the values in the table above are sorted, the formula does not require a sorted list.

Regards,

Barry

Hi Ian,

Thanks for looking up the PERCENTILE function, which I had neglected to do.

Nice and simple in appearance, but at first glance it appears to be working in the direction opposite to what's needed here.

The percentile formula I've used accepts the numerical value and returns the position of that value in a sorted list as the percentage of all values in the list that are less than the chosen value (with an adjustment that includes half the values that are equal to the chose value as part of the 'lower than' group.

The PERCENTILE function accepts the Percentile value and the list of values, and calculates a value that would sit at that spot in the sorted list of values. Here are examples of both calculations, using the same list of values as I used above:

The raw data in in column A of the table on the left.

Percentiles for each value are calculated in column B of that table, and displayed as the nearest whole number.

Because the maximum and minimum values in the set each occur twice, the formula assigns half of each to the collection of values 'below' that value. There are 19 values in the collection, so person is equivalent to about 5% of the population surveyed/tested, putting the lowest score (2) at the fifth percentile, and the highest at the 95th percentile.

The formula in B2 of this table is the one presented previously:

B2: ((COUNTIF(A,"<"&A2)+0.5×COUNTIF(A,A2))÷COUNT(A))×100

The table on the right uses the same data list.

The desired percentile values are listed in column A. Column B contains a formula that returns the value that would fit at that position, That actual value may not exist in the data list.

Values in column A are produced by this formula, entered in A2 and filled down to row 12:

A2: (12−ROW())×10

The score at each listed percentile are calculated by this formula, entered in B2 and filled down.

B2: PERCENTILE(Table 1::A,(12−ROW())÷10)

or this alternate formula:

B2: PERCENTILE(Table 1::A,A2/100

PERCENTILE might be useful in this case to set up a lookup table that would return percentiles from a limited list (such as the 10 in the table above (discounting the 0) for each height or weight figure in those columns.

Regards,

Barry

Hi Marsh,

Perhaps the PERCENTILE function is what you want.

Type = in any spare cell to bring up the Function Browser. Type "per" (without the quotes) in the search box.

Scroll down for Notes and examples. I haven't got my head around it, but perhaps Barry can explain.

Regards,

Ian.

Hi Marsh,

"I tried to enter your formula as follows."

While there are parts of this that look like the formula I provided, there is a lot missing.

Here's how mine appears when copied from the formula editor and pasted as text:

B2: ((COUNTIF(A,"<"&A2)+0.5×COUNTIF(A,A2))÷COUNT(A))×100

In the formula editor, it looks like this:

Note that yours is missing the two COUNTIF functions and the COUNT function, telling Numbers what to do with the parts contained in the parentheses following each function name. Here's a translation into 'people speak'

(

(

COUNTIF(A,"<"&A2) Count the values in column A that are less than the value in cell A2

+ Add

0.5*COUNTIF(A,A2) 0.5 multiplied by the count of the values in column A that are equal to the value in A2.

(Numbers will display * —the multiplication operator as × — the multiplication sign)

) Do everything from the bold ( to here. Use the result in the next step.

/ (displayed as ÷) Divide the result above by the result of:

COUNT(A) Count all the numbers in column A

) Complete the calculations above before the next step, using the result.

(The result will be a fraction between 0 and 1)

* 100 Multiply the result by 100 to convert the fraction to a number between 1 and 100

A in my formula is E in yours, displayed as Height.

A2 in my formula is E2 in yours.

My formula is placed in B2, and filled down that column.

Yours is placed in F2, and filled down that column.

When yours is working, you can click once on F2 to select that cell, then Copy (command-C).

Then select H2 and all cells in the column below it and Paste (command-V). The formula will automatically adjust to use values in column G.

To make the results display only two places after the decimal, select the cells in column F, open the Format Inspector, choose Cell, then set the pop-up menu to Number and the Decimal box to 2:

Regards,

Barry