Разница между процентами и процентилем
- Определение: Процент — это число из 100, а процентили — нет.
- Позиция: Percentile — это значение, ниже которого определен определенный процент значений.
- Условное обозначение: Символ процента — знак процента,%, а процентиль обозначен х-м, где x — число, так, например, 25го
- Квартили: Процентники имеют квартили, а проценты — нет.
- Ранг: Percentiles основаны на ранжированных числах, а проценты — нет.
- Десятичные: Проценты могут быть записаны как десятичные числа, процентили не могут быть записаны как десятичные.
- соотношение: Проценты могут быть записаны как отношение, в то время как процентили не могут быть записаны как отношение.
- Основано на: Процент основан на одном случае, а процентиль основан на сравнении одного случая со всеми случаями в конкретной ситуации.
- распределение: Percentiles полагаются на нормальное распределение, а проценты — нет.
Определение коэффициента эластичности
Для данного товара коэффициент эластичности k эл = -0,5.
Как изменится потребление этого товара, если цены на него возрастут на 10%?
Коэффициент эластичности – это отношение процентного изменения спроса к процентному изменению цены.
Следовательно процентное изменение спроса = k эл*процентное изменение цены=
Найти децильный коэффициен, моду и медианну по следующим данным.
Модальный интервал определяется по наибольшей частоте. Наибольшая частота в данной задаче 31,3, что соответствует интервалу от 4000 до 6000 рублей.
– накопленная частота интервала, предшествующего медианному.
Следовательно интервал от 6000 до 8000 руб является медианным интервалом. Накопленная частота интервала, предшествующего медианному, равна 61,9.
Значит половина людей в совокупности имеет доход менее 6937 рублей, а половина более 6937 рублей.
=0.1*1 47,5 =14. 75 млн чел.(14,75 млн чел соответствует 1-му интервалу).
Таким образом, дециль №1 входит в 1-й интервал с доходом до 4000.
Где – нижняя граница децильного интервала (интервала, содержащего дециль).
– накопленная частота интервала, предшествующего децильному.
=0. 9 *14 7,5 = 132,75 млн чел.(соответствует интервалу 16000-20000, а накопленная частота, предшествующая интервалу, содержащему дециль, равно 132,6)
Таким образом, минимальный доход 10% самого богатого населения в 8,4 раза выше, чем максимальный доход 10% самого бедного населения.
Квартили и интерквартильный интервал (IQR) в EXCEL
Для вычисления квартилей в MS EXCEL существует специальная функция КВАРТИЛЬ() . В этой статье дадим определение квартилей и научимся их вычислять для выборки и для непрерывного распределения. Также вычислим интерквартильный интервал.
Квартили (Quartiles) — значения, которые делят выборку (набор значений) на четыре части, содержащие приблизительно равное количество наблюдений (по 25%).
Поясним определение квартиля на примере. Пусть имеется выборка , состоящая из 50 значений в ячейках А7:А56 (см. файл примера , лист Квартиль-выборка). Для наглядности отсортируем значения по возрастанию и построим гистограмму .
Чтобы разделить выборку на 4 части достаточно 3-х квартилей .
Первый квартиль (или нижний квартиль , Q1) делит выборку , на 2 части: примерно 25% значений в выборке меньше Q1, остальные 75% — больше. Для вычисления 1-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;1) . Для нашей выборки формула вернет значение 224. Значения 224 нет в выборке , формула произвела интерполяцию на основе 2-х соседних значений 223 и 227.
Примечание : Функция КВАРТИЛЬ.ВКЛ() появилась в MS EXCEL 2010. В более ранних версиях использовалась аналогичная ей функция КВАРТИЛЬ() .
Чтобы убедиться, что примерно 25% значений меньше, чем 224, используем формулу =СЧЁТЕСЛИ(A7:A56;» . В результате получим, что 26% меньше, чем 1-й квартиль .
Чем в выборке больше значений и меньше повторов , тем точнее деление выборки квартилями на четверти.
Примечание : Первый квартиль — это то же самое, что и 25-я процентиль . Подробнее см. статью про процентили .
Второй квартиль (или медиана , Q2) также делит выборку , на 2 равные части: половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана . Для вычисления 2-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;2) или =МЕДИАНА(A7:A56)
Третий квартиль (или верхний квартиль , Q3) делит выборку , на 2 части: примерно 75% значений в выборке меньше Q3, остальные 25% — больше. Для вычисления 3-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;3) или =ПРОЦЕНТИЛЬ.ВКЛ(A7:A56;0,75)
Примечание : Третий квартиль — это то же самое, что и 75-я процентиль .
Второй аргумент функции КВАРТИЛЬ.ВКЛ() может также принимать значения 0 и 4. В первом случае функция вернет минимальное значение , во втором – максимальное .
Нормальное распределение и процентили
Представление правила трех сигм . Темно-синяя зона представляет наблюдения в пределах одного стандартного отклонения (σ) по обе стороны от среднего (μ), что составляет около 68,3% населения. Два стандартных отклонения от среднего значения (темно-синий и средний синий) составляют около 95,4%, а три стандартных отклонения (темный, средний и голубой) — около 99,7%.
Методы, приведенные в разделе определений (ниже), являются приблизительными для использования в статистике малых выборок. В общих чертах, для очень больших популяций, следующих нормальному распределению , процентили часто могут быть представлены ссылкой на график нормальной кривой. Нормальное распределение откладывается по оси с точностью до стандартных отклонений или единиц сигмы ( ). Математически нормальное распределение простирается до отрицательной бесконечности слева и положительной бесконечности справа
Обратите внимание, однако, что только очень небольшая часть людей в популяции выйдет за пределы диапазона от –3 до +3. Например, с человеческим ростом очень мало людей выше +3 уровня роста.
σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}. Процентили представляют собой площадь под нормальной кривой, увеличивающуюся слева направо
Каждое стандартное отклонение представляет собой фиксированный процентиль. Таким образом, округляя до двух десятичных знаков, −3 — это 0,13-й процентиль, −2 — 2,28-й процентиль, -1 — 15,87-й процентиль, 0 — 50-й процентиль (как среднее, так и медианное значение распределения), +1 84,13-й процентиль. процентиль, +2 для 97,72-го процентиля и +3 для 99,87-го процентиля. Это связано с правилом 68–95–99,7 или правилом трех сигм
Процентили представляют собой площадь под нормальной кривой, увеличивающуюся слева направо. Каждое стандартное отклонение представляет собой фиксированный процентиль. Таким образом, округляя до двух десятичных знаков, −3 — это 0,13-й процентиль, −2 — 2,28-й процентиль, -1 — 15,87-й процентиль, 0 — 50-й процентиль (как среднее, так и медианное значение распределения), +1 84,13-й процентиль. процентиль, +2 для 97,72-го процентиля и +3 для 99,87-го процентиля. Это связано с правилом 68–95–99,7 или правилом трех сигм
Обратите внимание, что теоретически 0-й процентиль попадает в отрицательную бесконечность, а 100-й процентиль — на положительную бесконечность, хотя во многих практических приложениях, таких как результаты тестов, применяются естественные нижние и / или верхние пределы.
σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}σ{\ displaystyle \ sigma}
Ранг и Персентиль в надстройке Пакет анализа MS EXCEL
334 является приблизительным. например, 380 баллов с понятием квантиля
можно сформировать с укажем на текущем функция ПЕРСЕНТИЛЬ производитМИН(A1:A20)
Используйте функцию Обязательный. Значение, которое6Данные
выборки не былиВозьмем выборку состоящую из квартилью. 11 (см. ячейку 0,08-я процентиль равна читать пользователям, владеющими
Точное значение дает из 400, то функции распределения. Поэтому помощью формулы =ПРОЦЕНТРАНГ.ВКЛ($B$7:$B$32;I7). листе, например, в интерполяцию для определения
1-я четверть:
PERCENTILE требуется вернуть.61 удалены или сгруппированы. 100 значений (см.Функция ПРОЦЕНТРАНГ.ВКЛ() используется дляB21 5, а в базовыми понятиями математической формула =ПРОЦЕНТИЛЬ.ВКЛ(A8:A127;0,9), которое вполне вероятна ситуация, имеет смысл освежитьНе смотря на свое ячейке значения k-ой персентили.ПЕРСЕНТИЛЬ(A1:A20;0,25)(ПЕРСЕНТИЛЬ), показанную ниже,
Если часть равна73 В этом смысле, файл примера лист оценки относительного положения). выборке нет ни
статистики (случайная величина, равно 331,4. О когда половине студентов в памяти понятия название Ранг и
- D6Скопируйте образец данных изКВАРТИЛЬ(A1:A20;1) чтобы вычислить 30-йКВАРТИЛЬ возвращает8
- 2 построение Кривой процентилей Кривая процентилей). Значения значения в массиве.По аналогии с непрерывной одного значения меньше функция распределения). том как работает поставили бы «автоматом» функции распределения и Персентиль, надстройка Пакет(это будет левый следующей таблицы иМедиана: процентиль. Excel возвращает094 это более точная
- содержатся в диапазоне Для заданного значения функцией распределения (см. 5.Начиная с версии MS функция ПРОЦЕНТИЛЬ.ВКЛ() читайте (если бы они, обратной функции.
- анализа не возвращает верхний угол таблицы вставьте их вПЕРСЕНТИЛЬ(A1:A20;0,50) значение 12,7. ЭтоМинимальное значениеФормулаФормула процедура для оценкиА5:А104 функция вычисляет сколько статью про квантили),Как видно из рисунка EXCEL 2010 для ниже. конечно, набрали быНа основании вышесказанного сформулируем Персентили. По крайней результатов). ячейку A1 новогоКВАРТИЛЬ(A1:A20;2) означает, что 30%1ОписаниеОписание вида функции распределения,. значений в массиве
получается, что 12,50% ниже первое значение расчета процентилей используетсяКак показывает опыт, для больше 380 баллов). определение для процентили: мере в томПосле нажатия кнопки ОК листа Excel. ЧтобыМЕДИАНА(A1:A20) (6 из 20)
Первую квартиль (25-ю персентиль)РезультатРезультат чем построение ГистограммыСначала построим таблицу частот меньше или равно значений должно быть (минимальное, равное 1) функция ПРОЦЕНТИЛЬ.ВКЛ() – данных выборки K-я Или наоборот, при
Коромысло
Ловля с коромыслом приобрела популярность у наших рыболовов много лет назад. И по сей день большинство использует именно коромысло. Во многих регионах страны название «коромысло» ассоциируется со снастью для ловли щуки и окуня. Многие изготавливают самостоятельно из аналогичных приспособлений, популярных у доночников, прикрепляя к одному «плечу» грузило, а к другому поводок с приманкой.
Интересной конструкция становится в том случае, если в качестве элементов огрузки использовать съемные дробинки весом 3-4 г. В этом случае, не меняя оснастки, рыболов может за несколько минут подобрать груз необходимого веса для конкретных условий.
Создать список уникальных значений с помощью специальной функции
Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».
В появившемся диалоговом окне «Удалить дубликаты», вы выделяете те столбики, где необходимо произвести отсев уникальных значений и нажимаете «Ок».
В случае, когда в выделенном диапазоне размещается и заголовок таблицы, то поставьте галочку на пункте «Мои данные содержат заголовки», что бы вы случайно не удалили данные
Внимание! Когда вы будете производить отсев уникальных значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит вам расширить диапазон выбора, с чем вы должны, согласится, иначе будет нарушена логическая связь с другими столбиками
Создать список уникальных значений с помощью расширенного фильтра
Это также не сложный способ произвести отбор уникальных значений в таблице.
Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно».
Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте. 
После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок». 
Если же вам не нужно никуда переносить ваши данные, то просто установите флажок для пункта «Фильтровать список на месте», данные не пострадают, произойдет наложение обыкновенного фильтра.
Внимание!Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения
Создать список уникальных значений с помощью формул
Этот способ более сложен, нежели те, что мы рассматривали ранее, но его преимущество в том, что он более динамичен и работает на постоянной основе. В разных случаях вам будут нужны разные формулы, вот и рассмотрим несколько вариантов и примеров.
Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:
ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)
Суть формулы в том, что она проверяет сколько раз, текущее значение встречается в вашем диапазоне (начиная с начала), и если это значение равно 1, то есть это первое уникальное значение, формула ставит последовательно возвращающий номер по порядку.
Теперь можно произвести отбор уникальных значений, которые были ранее пронумерованы. Сделать это возможно в любом из соседних столбиков используя функцию ВПР и копируя ее вниз:
ЕСЛИ(МАКС(A1:A100)
Схема независимых испытаний
В общем виде схема повторных независимых испытаний записывается в виде задачи:
Пусть производится $n$ опытов, вероятность наступления события $A$ в каждом из которых (вероятность успеха) равна $p$, вероятность ненаступления (неуспеха) — соответственно $q=1-p$. Найти вероятность, что событие $A$ наступит в точности $k$ раз в $n$ опытах.
Эта вероятность вычисляется по формуле Бернулли:
$$ P_n(k)=C_n^k cdot p^k cdot (1-p)^=C_n^k cdot p^k cdot q^. qquad(1) $$
Данная схема описывает большой пласт задач по теории вероятностей (от игры в лотерею до испытания приборов на надежность), главное, выделить несколько характерных моментов:
- Опыт повторяется в одинаковых условиях несколько раз. Например, кубик кидается 5 раз, монета подбрасывается 10 раз, проверяется 20 деталей из одной партии, покупается 8 однотипных лотерейных билетов.
- Вероятность наступления события в каждом опыте одинакова. Этот пункт связан с предыдущим, рассматриваются детали, которые могут оказаться с одинаковой вероятностью бракованными или билеты, которые выигрывают с одной и той же вероятностью.
- События в каждом опыте наступают или нет независимо от результатов предыдущих опытов. Кубик падает случайно вне зависимости от того, как упал предыдущий и т.п.
Если эти условия выполнены — мы в условиях схемы Бернулли и можем применять одноименную формулу. Если нет — ищем дальше, ведь классов задач в теории вероятностей существенно больше (и о решении некоторых написано тут): классическая и геометрическая вероятность, формула полной вероятности, сложение и умножение вероятностей, условная вероятность и т.д.
Подробнее про формулу Бернулли и примеры ее применения можно почитать в онлайн-учебнике. Мы же перейдем к вычислению с помощью программы MS Excel.
Calculating a Percentile in Excel
Suppose you have the below data set where there are scores in column A, and you want to find out the score that is the 90th percentile in this dataset.
To get this, in cell C2, enter the formula:
In this example, the array consists of scores in range A2:A11, and k (percentile) is 0.9, as you want to find the 90th percentile.
The result of the function is 92.8.
This means that 90% of the scores in a given data range (A2:A11) is below 92.8.
If you check in the data set, you will see that this is true, as only one score (in cell A7 – 100), is above 92.8, and all other scores (9/10 – 90% of them, are below).
This is often used to grade and rank students in a competitive exam. One benefit of using this method is that it takes the overall performance of the group.
For example, instead of telling me that I scored 90 out of 100, it tells me that I did better than 90% of the people. The score in itself doesn’t tell me my position, as it could be an easy exam and many students might have scored more than 90. But the percentile instantly tells me my performance in comparison with the group.
Just like I used 0.9 as the argument to get the 90th percentile, you can use other values between 0 and 1 to get any percentile value. For example, if you want the 50th percentile, then use 0.5 as the second argument in the formula.
Как работает процентильная функция
При вводе функция ПРОЦЕНТИЛЬ должна вводиться как формула. Чтобы добавить эту функцию, вы должны выполнить следующие шаги:
- Щелкните ячейку, в которой должен отображаться результат.
- Введите «= ПРОЦЕНТИЛЬ («. Конечно, без кавычек!
- После выбора массива чисел ставится запятая. Это диапазон чисел, для которого вы находите процентиль.
- Возвращаемый процентиль с закрытой круглой скобкой. Это число следует вводить в виде десятичной дроби.
- Наконец, данные необходимо ввести в ячейку.
Более подробно синтаксис функции ПРОЦЕНТИЛЬ можно увидеть здесь:
= ПРОЦЕНТИЛЬ (массив; k)
В этой функции есть два аргумента: массив и k. Массив определяет набор данных, а k определяет, какое значение процентиля выбирается от нуля до единицы. Оба эти аргумента необходимы для работы этой функции.
Массив можно ввести как массив или именованный диапазон.
Медиана и квартили
Квантили нормального распределения
Основная статья: Медиана (статистика)
- 0,25-квантиль называется первым (или нижним) кварти́лем (от лат. quarta — четверть);
- 0,5-квантиль называется медианой (от лат. mediāna — середина) или вторым кварти́лем;
- 0,75-квантиль называется третьим (или верхним) кварти́лем.
Интеркварти́льным размахом (англ. Interquartile range) называется разность между третьим и первым квартилями, то есть x,75−x,25{\displaystyle x_{0{,}75}-x_{0{,}25}}. Интерквартильный размах является характеристикой разброса распределения величины и является робастным аналогом дисперсии. Вместе, медиана и интерквартильный размах могут быть использованы вместо математического ожидания и дисперсии в случае распределений с большими выбросами, либо при невозможности вычисления последних.
Automatic Conditional Ranking and Percentiles with SUMPRODUCT
The above process works for simple data sets and one-time sort and filter operations, but if you have a large data set or need to be able to refresh it and re-rank often, you need a more automatic way to do the filtering and sorting of criteria.
Fortunately, SUMPRODUCT can help us do just that using a trick called an array formula and Boolean (TRUE and FALSE) equations.
Boolean Equations in Excel
Excel can test equations to see whether they are TRUE or FALSE. For example, if you entered:
=1+2=3
Excel would show TRUE in the cell because 1+2 is equal to 3. Try an inaccurate comparison:
=1+2=4
Excel shows FALSE.
Pretty simple. The trick is that Excel can do math with Boolean statements. It treats TRUE as 1 and FALSE as .
Try entering the following in a cell:
=TRUE+TRUE+TRUE
Excel will calculate the cell’s value as 3.
Using Boolean Arrays with SUMPRODUCT
SUMPRODUCT can use booleans an an array to filter results. If you compare an array to a criteria and multiply it with itself, you’ll get a filtered sum, just like if you were using a SUMIF function. For example:
The above example evaluates to 6. It is comparing each row to 4. When the number is less than 4, it returns TRUE (or 1). When the number is not less than 4, it returns FALSE (or ). The SUMPRODUCT multiplies that set of results against the same numbers and adds them up. 1, 2, and 3 are less than 4, so they are summed to get 6.
This is the same as if we did the multiplication and addition long-hand:
1 * 1 = 1 1 * 2 = 2 1 * 3 = 3 0 * 4 = 0 0 * 5 = 0 --------- 6 = (1 + 2 + 3 + 0 + 0)
Building a SUMPRODUCT Percentile Formula
We are going to apply the same strategy to filter the city populations by matching their continents in our example data set. We don’t even need to sort it first! Let’s go back to that original, un-sorted city list:
In the first row, enter the following formula:
=SUMPRODUCT(($C$2:$C$245=C2)*(D2>=$D$2:$D$245))/COUNTIF($C$2:$C$245,C2)
Pay attention to the locked ranges. They are selecting the entire column of data for Continent ($C$2:$C$245) and Population ($D$2:$D$245). Let’s examine what the formula is doing by looking at each of the Boolean statements:
($C$2:$C$245=C2)
This is comparing each row’s Continent to the continent in the current row. It will return TRUE for each match and FALSE for each mis-match. This means we will only be considering populations in the same continent.
(D2>=$D$2:$D$245)
This is comparing each row’s Population to the population in the current row. It will return TRUE for each value that is smaller than or equal to the current row and FALSE for each value that is greater than the current row. Remember, this will only count for populations in the same continent because of the earlier Boolean statement.
This is doing a conditional ranking, similar to the RANK statement in the manual example earlier in the tutorial. It’s just doing it for the un-sorted list. You could add or remove rows from the list and it would automatically re-calculate the rank.
The COUNTIF function is the same as the COUNT function in the manual, sorted example. It’s just counting only the rows that meet the criteria. The syntax for COUNTIF is as follows:
=COUNTIF(range, criteria)
In this case, the range is the continent column, and the criteria is the current row’s continent.
Now that the formula has been built, we can simply drag it down to all the rows in the data set. It will automatically calculate a separate percentile ranking for each country within its own continent!
Note that the largest city in Europe (Moscow) is ranked in the 100th percentile for Europe even though it is third largest overall. This shows that the formula is working correctly!
Расчет процентиля в Excel
Процентиль несложно вычислить по формуле:
но проще обрабатывать массив данных одноименной функцией Excel. Для примера возьмем произвольную выборку полученных баллов и рассчитаем в ней процентили:
Функция PERCENTILE (ПЕРСЕНТИЛЬ) включает в себя ввод диапазона ячеек данных (А1:А10) и значения процентиля К, деленного на 100%. Т.е. в данном случае ввод 0.3 означает нахождение тридцатого процентиля. Смысл расчета: к 30-му процентилю будут относиться все результаты, меньше или равные 7.9.
Если мы хотим узнать процентиль участника, получившего 10 баллов, то это несложно сделать, варьируя значение К до тех пор, пока значение в ячейке С12 не станет близким 10. Получится примерно 34-ый процентиль. При увеличении процентиля в выборку попадает больше табличных значений:
Итого, в 80-ый процентиль попадает уже 8 табличных значений из 10, которые меньше или равны 47.2. При этом подчеркнуть результат можно диапазоном процентилей — например, между восьмидесятым и сотым. В этом случае значения будут лежать между 47.2 и 67 (максимальным числом выборки).
Среднее геометрическое
При несимметричном распределении данных среднее арифметическое не будет обобщающим показателем распределения.
Если данные скошены вправо, то можно создать более симметричное распределение, если взять логарифм (по основанию 10 или по основанию е) каждого значения переменной в наборе данных. Среднее арифметическое значений этих логарифмов — характеристика распределения для преобразованных данных.
Чтобы получить меру с теми же единицами измерения, что и первоначальные наблюдения, нужно осуществить обратное преобразование — потенцирование (т. е. взять антилогарифм) средней логарифмированных данных; мы называем такую величину среднее геометрическое.
Если распределение данных логарифма приблизительно симметричное, то среднее геометрическое подобно медиане и меньше, чем среднее необработанных данных.
Creating Percentile Formulas in Excel
Let’s look at some sample data and see how we can use the various percentile functions.
We begin with a set of data that shows the average salary for a software developer in various countries.
Our goal is to determine the 25th percentile across the range of salaries.
To do this inclusively, we can use Excel’s PERCENTILE.INC function.
PERCENTILE.INC has the following syntax:
Array is the list of values to be calculated.
K is the rank you need to determine.
For our example, determining the 25th percentile would require the following formula.
This means that 25% of the listed salaries are below $48,504 and 75% of the listed salaries are above $48,504.
If we wanted to calculate the 50th and 75th percentile values of these numbers, we can write the following formulas.
Featured Course
Determining the Percent Rank
The opposite approach to these calculations is to take a value (like a salary) and determine where it ranks as a percentage within a range of other values.
This can be accomplished using the PERCENTRANK functions.
We’ll start with the inclusive version of PERCENTRANK.
PERCENTRANK.INC has the following syntax:
Array is the list of values to be calculated.
X is the value you need to determine the rank for.
For our example, determine the percentile rank for a salary of $65,000.
Getting Fancy Responses
If you want to get fancy, you could write the following formulas to take the PERCENTRANK.INC results and concatenate them to text with a bit of custom number formatting.
(NOTE: the result for PERCENTRANK.INC is in cell E11.)
Keep in mind, the above formulas could use cell references for the values (k) or the percentages (x) instead of hard-coded values.
Using the Exclusive Versions of These Functions
The exclusive versions of these functions (PERCENTILE.EXC and PERCETRANK.EXC) do not include the extreme low and high ends of the data range. (For a more precise explanation of this linear interpolation process, click here to read the Wikipedia article on Percentile.)
If we wished to determine the 25th exclusive percentile of the same salaries, we could write the following formula.
Notice that the 50th percentile yields the same result. However, the 15th and 75th percentiles are a bit lower and higher respectively.
This is compensating for the absence of the low and high ends of the data.
It’s as if the remaining data were “stretched” to reach the original low and high points.
If we were to use the exclusive version of PERCENTILE and calculate for and 1, we would see the following errors.
“Which one should I use?”
The use of the inclusive versus exclusive versions of PERCENTILE and PERCENTRANK comes down to your profession, the size of the data set, and the type of analysis you are performing; the story you are trying to craft.
The more commonly used version is the inclusive version.
Пример расчета перцентиля с использованием функции ПЕРСЕНТИЛЬ в Excel
Пример 1. В магазин будет завезена новая партия обуви. Ранее в рамках маркетингового исследования были записаны размеры ног 10 случайных клиентов. На основании имеющихся данных определить размер обуви, являющийся пороговым значением для 90% клиентов.
Вид таблицы данных:
Для расчета используем функцию:
- B3:B12 – исследуемый ряд значений;
- 0,9 – число, указывающее, что необходим поиск 90-й перцентили (0,9=90%).
В результате вычислений формулы получен 90 перцентиль. Найденное значение не соответствует ни одному из рассматриваемого ряда, поскольку функция ПЕРСЕНТИЛЬ выполнила интерполяцию данных. 90% клиентов покупают обувь до 41 размера включительно.