0

Quantile calculation in excel, is there a way to do that? I have values that I want to calculate the quantile(0.05, 0.50, and 0.95). I have tried search online but all i found is quartile calculations, not quantile. Most of info i got online cannot solve the problem. The reason I want this is to cross check values from python calculation results. Looking forward for any help.

In my excel, I have all the values in column "G", as example: I want to calculate the quantile(0.05, 0.5, and 0.95) of all values in "G" column. I want to calculate in excel...is there ways to do that? thanks

enter image description here

i200yrs
  • 23
  • 4
  • Show your input then output to excel cells. – Harun24hr Aug 04 '22 at 05:08
  • In my excel, I have all the values in column "G", as example: I want to calculate the quantile(0.05, 0.5, and 0.95) of all values in "G" column. I want to calculate in excel...is there ways to do that? thanks G 1.09936E-08 1.24831E-08 1.02066E-08 1.10457E-08 9.93788E-09 1.25035E-08 1.06305E-08 1.24135E-08 4.48602E-08 1.23504E-08 1.09763E-08 1.06166E-08 1.14301E-08 1.1502E-08 1.0383E-08 1.16946E-08 – i200yrs Aug 04 '22 at 05:31
  • Put a screenshot here then show your desired output. – Harun24hr Aug 04 '22 at 05:33
  • I updated my post with image to help more clearer info. – i200yrs Aug 04 '22 at 05:42
  • You can use the percentile.inc or percentile.exc functions. These should be equivalent to Python's inclusive or exclusive functions, but as noted here there are many different quantile formulas https://en.wikipedia.org/wiki/Quantile – Tom Sharpe Aug 04 '22 at 07:14
  • There is no quantile calculation in excel...Only quartile is available. – i200yrs Aug 05 '22 at 03:18

1 Answers1

0

An old post, but for anyone else, I've used Percentile in the past and it's worked for what I've wanted to do.

PERCENTILE.INC(G2:G20,0.1)

This will give the 10% value.

jabs
  • 1,694
  • 4
  • 19
  • 36