Introduction to percentile and percentrank functions in excel

Let’s Begin With a Definition

Imagine you just received your test results for an Excel exam and learn that you answered 80% of the questions correctly.  Also, your score ranks in the 70th percentile.

What does this mean?  Is this good or bad?

Having a score in the 70th percentile means that 70% of all test scores were below your test score, and 30% of all test scores were above your test score.

Said another way, 70% of the test takers answered fewer than 80% of the questions correctly, while 30% of the test takers answered greater than 80% of the questions correctly.

When describing percentile, we often use the word percentage.  This is where the confusion comes in because the word “percentage” can mean different things depending on the sentence context.

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.

PERCENTILE.INC vs PERCENTILE.EXC – What’s the Difference?

Now, if you’re wondering why there are two separate percentile functions in Excel, let me try and explain.

When you use PERCENTILE.INC function, it would calculate the result while including the first and last value in the dataset. And if you want to exclude the first and the last value from the calculation, you need to use the PERCENTILE.EXC function.

In most cases, you will be required to use the PERCENTILE.INC function only.

But since we are talking about the difference between the two functions, let me try and show you the difference with an example.

Suppose you have the dataset as shown below where I have calculated different percentile values (in column C) using both PERCENTILE.INC and PERCENTILE.EXC.

As you can see, apart from getting different results, the PERCENTILE.EXC function would return a #NUM! error when I try to calculate the percentile value for 0 or 100%

In fact, PERCENTILE.EXC would give you an error for any value between:

where N is the total number of data points in the dataset (10 in this example)

So it would give the NUM error for any Kth values that lie between 0 and 1/11 or 10/11 and 1.

So it’s good to have the PERCENTILE.EXC function, but in most cases, you can just go ahead and use the PERCENTILE or the PERCENTILE.INC function.

I hope you found this tutorial useful!

Other Excel tutorials you may also like:

PERCENTILE.INC vs PERCENTILE.EXC — в чем разница?

Если вам интересно, почему в Excel существуют две отдельные функции процентиля, сейчас я это объясню.

Когда вы используете функцию PERCENTILE.INC, она вычисляет процентиль, используя первое и последнее значение в наборе данных. Если же вам нужно исключить первое и последнее значение из расчета, вам нужно использовать функцию PERCENTILE.EXC.

В большинстве случаев можно использовать функцию PERCENTILE.INC.

Но раз уж мы заговорили о разнице между этими функциями, я наглядно продемонстрирую её.

Допустим, у нас есть набор данных, как показано на картинке ниже, где я рассчитал разные процентили (в столбце C), используя как функцию PERCENTILE.INC, так и функцию PERCENTILE.EXC.

Как вы можете заметить, помимо того что результаты каждой функции разные, еще и функция PERCENTILE.EXC возвращает ошибку #NUM!, когда мы пытаемся вычислить 0-ой и 100-ый процентиль.

На самом деле, функция PERCENTILE.EXC выдаст ошибку для любого значения между:

• 0 и 1/(N+1)
• N/(N+1) и 1

где N — количество строк данных в наборе(таблице) (10 в данном примере).

Таким образом, функция выдаст ошибку #NUM! для любых K значений, лежащих между 0 и 1/11 или 10/11 и 1.

Поэтому хорошо что функция PERCENTILE.EXC существует, но в большинстве случаев мы будем использовать функцию PERCENTILE или PERCENTILE.INC.

Надеюсь, вам помогло данное руководство!

Как рассчитать перцентиль в Excel с помощью функции ПЕРСЕНТИЛЬ

Пример 2. В таблице введен ряд некоторых значений. Необходимо:

1. Определить, во сколько раз 80-й перцентиль превышает 20-й перцентиль.
2. Рассчитать 40-й перцентиль без использования рассматриваемой функции.

Вид таблицы данных:

Для поиска значения соотношения используем следующую запись:

То есть, для исследуемого ряда значений 80-й перцентиль превышает 20-й почти в 4,5 раз.

Альтернативный способ нахождения перцентиля – следующая формула:

Данная запись соответствует формуле, указанной в определении понятия перцентиль. Результат вычислений:

Рядом (справа) указано значение, полученное с использованием функции ПЕРСЕНТИЛЬ:

Значения отличаются, поскольку рассматриваемая функция выполняет интерполяцию данных.

Как рассчитываются основные баллы JEE?

Рейтинговый список JEE Main 2022 будет подготовлен NTA на основе процентилей, а не на основе предварительных оценок, полученных соискателями на экзамене. … Как рассчитать основной процентильный балл JEE 2022: знание процесса нормализации.

JEE Главный результат 2022	JEE Main 2022 Marks vs Percentile
Основные вопросы JEE	Главное отключение JEE

Как рассчитывается процентиль в CMAT?

Как рассчитывается процентиль CMAT? Вы также можете рассчитать ранг по приведенной ниже формуле: Процентиль (P) = 10,000 1 – XNUMX.(ранг)/10,000 100 х 99.99 = XNUMX. Точно так же, если человек получил ранг 3, его / ее процентиль будет 99.97.

Как рассчитать процентиль в зарплате? Чтобы определить процентиль диапазона заработной платы, вы сначала необходимо рассчитать разницу между максимальной и минимальной заработной платой. Например, если диапазон заработной платы для определенной должности составляет от 45,000 75,000 до 30,000 XNUMX долларов, разница между этими двумя цифрами составит XNUMX XNUMX долларов.

Что такое K в процентильной формуле? k-й процентиль — это значение, ниже которого падает k процентов значений в наборе данных. Процентиль, рассчитанный с помощью . 4, поскольку k означает, что 40% процентов значений меньше или равны рассчитанному результату, процентиль, рассчитанный с помощью к = . 9 означает, что 90% процентов значений меньше или равны рассчитанному результату.

Квантили распределения ХИ-квадрат

Вычислять квантили распределения ХИ-квадрат с n -1 степенью свободы требуется, если проводится проверка гипотезы о дисперсии нормального распределения (см. статью Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения ).

При проверке таких гипотез также используются верхние квантили. Например, при двухсторонней гипотезе требуется вычислить 2 верхних квантиля распределения ХИ 2 : χ 2 _α/2,n-1 и χ 2 _{1- α/2,n-1} . Почему требуется вычислить два квантиля , не один, как при проверке гипотез о среднем , где используется стандартное нормальное распределение или t-распределение ?

Дело в том, что в отличие от стандартного нормального распределения и распределения Стьюдента , плотность распределения ХИ 2 не является четной (симметричной относительно оси х). У него все квантили больше 0, поэтому верхний альфа-квантиль не равен нижнему (1-альфа)-квантилю или по-другому: верхний альфа-квантиль не равен нижнему альфа-квантилю со знаком минус.

Чтобы вычислить верхний 0,05/2 – квантиль для ХИ 2 -распределения с числом степеней свободы 10, т.е. χ 2 _0,05/2,n-1 , необходимо в MS EXCEL записать формулу =ХИ2.ОБР.ПХ(0,05/2; 10) или =ХИ2.ОБР(1-0,05/2; 10)

Результат равен 20,48. .ПХ означает правый хвост распределения, т.е. тот который расположен вверху на графике функции распределения .

Чтобы вычислить верхний (1-0,05/2)- квантиль при том же числе степеней свободы , т.е. χ 2 _1-0,05/2,n-1 и необходимо записать формулу =ХИ2.ОБР.ПХ(1-0,05/2; 10) или =ХИ2.ОБР(0,05/2; 10)

Результат равен 3,25.

Реализация

Мы ориентировались на реализацию алгоритма на Go.

Заведем два массива, один — буфер, в который будут помещаться наблюдаемые значения, а второй — массив наблюдений для хранения структур для отрезков:

Алгоритм работает только с отсортированными значениями. Ограничим размер буфера 500 значениями, а размер массива наблюдений определим как  — операция сжатия сокращает размер массива примерно вполовину, так что в среднем нам потребуется: 500 элементов несжатого массива с предыдущего шага + 500 элементов, которые вливаются в массив на текущем шаге + 2 элемента и  для упрощения поиска в массиве.

Ход разработки

Разрабатывали итеративно: делаем версию, проверяем производительность c помощью профилировщика и сравниваем с версией на Go; думаем, как улучшить. Сравнивать будем с простым бенчмарком: делаем вставку 108 образцов, для гошной версии это занимает порядка 8 с. Теперь подробнее о каждом шаге:

1) pure-Lua версия — очень плохо, вставка занимает в среднем около 100 с.

В профилировщике видим следующее:

Код проседает на вставке наблюдений в массив (вызов ) и сортировке буфера (). На помощь приходит ffi, или foreign function interface. Ffi позволяет обращаться к функциям из стандартной библиотеки C, а потом работать с ними в Lua, как с обычными Lua-объектами (ну, почти; например, индексация таблиц в Lua начинается с 1, а у массивов, созданных из С, всё еще с 0).

2) Lua + ffi — заменим создание буфера на создание массива double:

Сортировать такой массив будем средствами стандартной библиотеки С:

Функцию-компаратор для  нужно написать на С и подключить как динамическую библиотеку. Пишем компаратор:

Собираем его:

Подключаем библиотеку в Lua-коде:

Теперь можно заполнить массив и вызвать сортировку:

Тестируем производительность и получаем прирост в 3 раза, в среднем до 30 с. Проседание происходило из-за того, что размер таблиц в Lua не фиксированный, тип элементов тоже никак не указывается заранее. Это позволяет гибче работать с таблицами, но снижает производительность (подробнее о Lua-таблицах можно почитать здесь). Ffi позволяет перейти от Lua-таблиц к С-массивам с фиксированным размером, поэтому вставка и вычисление размера массива теперь обходятся в  вместо . Сортировка тоже происходит гораздо быстрее благодаря зафиксированным типам и, соответственно, фиксированным размерам элементов. Но при таком решении появилась зависимость от gcc, что усложняет поставку приложений. Поэтому пришлось избавиться от C-кода.

3) Lua + ffi + самописная сортировка — время работы простейшего варианта быстрой сортировки на Lua получилось всего лишь на пару секунд хуже, чем вариант с сишной библиотекой. Это значение вместе с отсутствием gcc нас удовлетворило, и мы решили остановиться на нем.

Расход памяти

использует два массива:

• Буфер размером = байт.
• Массив наблюдений размером = байт. Размер массива наблюдений может увеличиваться при изменении наблюдаемых значений на несколько порядков.

Влияние на производительность

Провели нагрузочное тестирование Яндекс.Танком (подробнее здесь). Приложение с выключенными метриками:

При использовании Summary-коллектора:

Просело на ~10%, это та цена производительности, которую нужно платить за использование метрик. Если вы хотите избежать сильной просадки, нужно пользоваться коллектором аккуратно, например, замерять только часть запросов.

Распределение Фишера (F-распределение). Распределения математической статистики в EXCEL

history 12 ноября 2016 г.

Распределения вероятностей

Рассмотрим распределение Фишера (F-распределение). С помощью функции MS EXCEL F .РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.

F-распределение (англ. F-distribution) применяется для целей дисперсионного анализа (ANOVA), при проверке гипотезы о равенстве дисперсий двух нормальных распределений (F-тест) и др.

Определение : Если U ₁ и U ₂ независимые случайные величины, имеющие ХИ2-распределение с k ₁ и k ₂степенями свободы соответственно, то распределение случайной величины:

носит название F -распределения с параметрами k ₁ и k ₂ .

Плотность F -распределения выражается формулой:

где Г(…) – гамма-функция:

если альфа – положительное целое, то Г( альфа )=( альфа -1)!

Приведем пример случайной величины, имеющей F -распределение.

Пусть имеется 2 нормальных распределения N(μ ₁ ;σ ₁ ) и N(μ ₂ ; σ ₂ ), из которых сделаны выборки размером n ₁ и n ₂ . Если s ₁ 2 и s ₂ 2 – дисперсии этих выборок , то отношение

имеет F -распределение. Это соотношение нам потребуется при проверке гипотезы о равенстве дисперсий двух нормальных распределений (F-тест) .

Графики функций

В файле примера на листе График приведены графики плотности распределения вероятности и интегральной функции распределения .

Примечание : Для построения функции распределения и плотности вероятности можно использовать диаграмму типа График или Точечная (со сглаженными линиями и без точек). Подробнее о построении диаграмм читайте статью Основные типы диаграмм .

F-распределение в MS EXCEL

В MS EXCEL, начиная с версии 2010, для F-распределения имеется специальная функция F.РАСП() , английское название – F.DIST(), которая позволяет вычислить плотность вероятности (см. формулу выше) и интегральную функцию распределения (вероятность, что случайная величина Х, имеющая F — распределение , примет значение меньше или равное х, P(X Примечание Плотность вероятности можно также вычислить впрямую, с помощью формул (см. файл примера ).

До MS EXCEL 2010 в EXCEL была функция FРАСП() , которая позволяет вычислить функцию распределения (точнее — правостороннюю вероятность, т.е. P(X>x)). Функция FРАСП() оставлена в MS EXCEL 2010 для совместимости. Аналогом FРАСП() является функция F.РАСП.ПХ() , появившаяся в MS EXCEL 2010.

Примеры расчетов приведены в файле примера на листе Функции .

В MS EXCEL имеется еще одна функция, использующая для расчетов F-распределение – это F.ТЕСТ(массив1;массив2) . Эта функция возвращает результат F-теста : двухстороннюю вероятность того, что разница между дисперсиями выборок «массив1» и «массив2» несущественна. Предполагается, что выборки делаются из нормального распределения .

Обратная функция F-распределения

Обратная функция используется для вычисления альфа — квантилей , т.е. для вычисления значений x при заданной вероятности альфа , причем х должен удовлетворять выражению P

Функция F.ОБР.ПХ() используется для вычисления верхнего квантиля . Т.е. если в качестве аргумента функции указан уровень значимости, например 0,05, то функция вернет такое значение случайной величины х, для которого P(X>x)=0,05. В качестве сравнения: функция F.ОБР() вернет такое значение случайной величины х, для которого P(X F.ОБР.ПХ() использовалась функция FРАСПОБР() .

Вышеуказанные функции можно взаимозаменять, т.к. следующие формулы возвращают одинаковый результат: =F.ОБР(0,05;k1;k2) =F.ОБР.ПХ(1-0,05;k1;k2) = FРАСПОБР (1-0,05;k1;k2)

СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

Моменты случайной величины

Моменты случайно величины описывают различные аспекты характера и формы нашего распределения.

#1 — первый момент случайной величины — среднее значение данных, которое показывает место распределения.

#2 — второй момент случайной величины — дисперсия, которая показывает разброс распределения. Большие значения имеют больший размах, чем маленькие.

#3 — третий момент случайной величины — коэффициент асимметрии — мера того, насколько неравномерным является распределение. Коэффициент асимметрии положителен, если распределение наклонено влево и левый хвост короче правого. То есть среднее значение находится правее. И наоборот:

Асимметрия

#4 — четвертый момент случайной величины — коэффициент эксцесса, который описывает то, насколько толстый хвост и насколько острый пик распределения. Этот коэффициент показывает, насколько вероятно найти точки экстремума в данных. Чем выше значение, тем вероятнее выбросы. Это похоже на разброс (дисперсию), но между ними есть отличия.

Коэффициент эксцесса трех кривых

Как видно на графике, чем выше значение пики, тем выше коэффициент эксцесса, т.е. у верхней кривой коэффициент эксцесса выше, чем у нижней.

Сводные таблицы

Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.

Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.

Как работать

Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.

1. Откройте файл с таблицей, данные которой надо проанализировать.
2. Выделите диапазон данных для анализа.
3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
4. Должно появиться диалоговое окно «Создание сводной таблицы».
5. Настройте отображение данных, которые есть у вас в таблице.

Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.

Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

Как работать

1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
3. Выделите диапазон данных для анализа.
4. На вкладке «Вставка» есть кнопка 3D-карта.

Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

Prometheus

С перцентилями можно столкнуться при использовании и . Подробнее о том, как они выглядят, было в прошлой части. Гистограмма — это бакеты, которые вы считаете в своем приложении, а prometheus можно потом попросить посчитать из них перцентили. Причем если бакеты одинаковые, а теги разные (например, гистограмма с разных реплик или слоев), то можно без проблем их посчитать вместе. Summary — это перцентили, которые вы уже посчитали в приложении, а prometheus просто сохраняет их как числа. Обычно считается с помощью семплирования, поэтому точнее, чем бакеты. Ее можно просто в лоб вывести на график, но нельзя агрегировать! Причина, упрощенно, в том, что у вас разные выборки, поэтому результат агрегации будет отражать непонятно что. Вот показательный пример:

Не агрегируйте Summary

Пример, который демонстрирует, что с виду одинаковые Summary с двух реплик нельзя как-то сложить вместе: предположим у вас две реплики и , они получают HTTP-запросы и записывают время обработки.

Каждая пишет в prometheus метрику типа Summary с 95-м перцентилем и с тегом, чтобы различать с какой реплики сняты данные.

Вы думаете, что неплохо бы нарисовать один общий график «95й перцентиль от времени обработки HTTP» и делаете такой запрос к prometheus:

— потому что нужна же какая-то функция, чтобы сложить вместе два значения, почему бы не взять среднее арфиметическое между ними? Не сумму же. В результате получается , вроде бы все нормально, да? А теперь давайте по-честному посчитаем 95 перцентиль на общих данных от и :

Честный 95-й перцентиль равен , а от двух метрик получился , то есть вы видите что-то на графике, но это совершенно бессмысленное значение. Если вам нужно агрегировать summary, подумайте, для чего вы на них смотрите. Например, в случае со временем обработки HTTP, скорее всего вас интересует, не превышает ли 95-й перцентиль какой-то порог. Поэтому можно взять максимум из метрик вместо :

Вы будете видеть один график, и если какая-то из реплик работает в основном медленно, то это будет видно. Конечно, решение не идеальное. Например, может привести к false-positive алертам: тормозит только одна реплика, а не все приложение, но вам уже звонят посреди ночи.

Рецепты

Можно запомнить несколько простых рецептов, чтобы сделать метрику, отражающую большинство данных, и не думать (ведь так хочется не думать, а просто сделать уже, чтобы работало!):

Для начала попробуйте сделать гистограмму.
Сделайте бакетов пять, вроде «отлично/хорошо/сойдет/плохо/недопустимо».
Если вообще не представляете, какие нужны бакеты, придется взять Summary.
Вам скорее всего нужен 95-й перцентиль (вне зависимости от типа метрики).
Сравните визуально 95-й перцентиль и медиану

Медиана покажет что-то максимально усредненное, 95-й перцентиль покажет изменения с большей точностью, но спрячет выбросы.
Если у вас Summary, не агрегируйте ее или делайте очень осторожно, никакого !
У Summary неплохо бы понимать параметры семплирования, читайте доку к вашей библиотеке для метрик.

Приложения

Когда Интернет-провайдеры счет «взрывная» пропускная способность интернета 95-й или 98-й процентиль обычно отсекает верхние 5% или 2% пиков пропускной способности каждый месяц, а затем выставляет счет по ближайшей ставке. Таким образом, нечастые пики игнорируются, и покупатель получает более справедливую оплату. Причина, по которой эта статистика так полезна при измерении пропускной способности данных, заключается в том, что она дает очень точное представление о стоимости полосы пропускания. 95-й процентиль говорит о том, что 95% времени использование ниже этого количества: поэтому в оставшихся 5% времени использование превышает это количество.

Врачи часто используют вес и рост младенцев и детей для оценки их роста в сравнении со средними национальными значениями и процентилями, которые находятся в графики роста.

85-й процентиль скорости движения на дороге часто используется в качестве ориентира при настройке ограничения скорости и оценка того, является ли такой предел слишком высоким или низким.

В финансах стоимость под риском — это стандартная мера для оценки (зависящей от модели) величины, при которой не ожидается снижения стоимости портфеля в течение заданного периода времени и с заданным значением достоверности.

Нормальное распределение и процентили

Представление правила трех сигм . Темно-синяя зона представляет наблюдения в пределах одного стандартного отклонения (σ) по обе стороны от среднего значения (μ), что составляет около 68,3% населения. Два стандартных отклонения от среднего значения (темно-синий и средний синий) составляют около 95,4%, а три стандартных отклонения (темный, средний и светло-синий) составляют около 99,7%.

Методы, приведенные в разделе определений (ниже), являются приблизительными для использования в статистике малых выборок. В общих чертах, для очень больших популяций, следующих нормальному распределению , процентили часто могут быть представлены ссылкой на график нормальной кривой. Нормальное распределение откладывается по оси со стандартными отклонениями , или сигма (σ{\ displaystyle \ sigma}) единицы. Математически нормальное распределение простирается до отрицательной бесконечности слева и положительной бесконечности справа

Обратите внимание, однако, что только очень небольшая часть особей в популяции выйдет за пределы диапазона от –3 σ до +3 σ. Например, с человеческим ростом очень немногие люди превышают уровень роста +3 σ .

Процентили представляют собой площадь под нормальной кривой, увеличивающуюся слева направо. Каждое стандартное отклонение представляет собой фиксированный процентиль. Таким образом, округляя до двух десятичных знаков, −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-й процентиль — на положительной бесконечности, хотя во многих практических приложениях, таких как результаты тестов, применяются естественные нижние и / или верхние пределы.