CODEWITHSUNDEEP
excelstatistics
Ayubx
Ayubx

Reputation: 665

Basic Statistics - Quartile for an odd data set

I have the following set of data ,

1000 2000 3000 3000 7000

I want to get the first and third quartile. The median is 3000. So according to my calculations,

q1=(1000+2000)/2 = 1500 q3=(3000+7000)/2 = 5000

Excel function Quartile QUARTILE(E22:E26,1) and QUARTILE(E22:E26,3) results q1 = 2000 and q3 = 3000 which i am not able to understand. What logic am i missing ?

Thanks

Upvotes: 1

Views: 213

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34230

The normal quartile formulae give the term number for the lower and upper quartiles in the sorted data as:-

(n+1)/4

and

3(n+1)/4

If you apply these to your data you get 1.5 and 4.5, i.e. the values half way between the first and second, and fourth and fifth, numbers which are 1500 and 5000 as you predict.

Unfortunately Excel's QUARTILE function doesn't do its this way and it's not clear from Microsoft's documentation what QUARTILE does do.

However Microsoft have now provided two separate functions so you have the option of doing it the normal way if you want to.

enter image description here

The short answer to the question 'What is the difference' is that QUARTILE.EXC excludes the median. For the long answer, see this very good article.

Upvotes: 2

Related Questions