Solved

How to calculate the  QUARTILE ?

Posted on 2016-10-05
5
59 Views
Last Modified: 2016-10-10
How can I calculate the QUARTILE into 5 section for example QUARTILE(B2:B50,5)

1   < .2
2   < .4
3   < .6
4   < .8
5   < 1
0
Comment
Question by:Bharat Guru
  • 3
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 41830323
You can use the PERCENTILE function instead. You can use PERCENTILE(array,.2), PERCENTILE(array,.4), etc.

In which case they are quintiles (if that's what they're called), not quartiles.
0
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 41830331
=PERCENTILE(B2:B50,5)
0
 

Author Comment

by:Bharat Guru
ID: 41830440
I want to get the result like 1 , 2 , 3 , 4 or 5 base on

case   if cell("B2").value is < PERCENTILE(B2:B50 , .2 )
      1
case   if cell("B2").value is < PERCENTILE(B2:B50 , .4 )
      2
case   if cell("B2").value is < PERCENTILE(B2:B50 , .6 )
      3
case   if cell("B2").value is < PERCENTILE(B2:B50 , .8 )
      4
case   if cell("B2").value is < PERCENTILE(B2:B50 , 1 )
      5
0
 
LVL 12

Accepted Solution

by:
Missus Miss_Sellaneus earned 500 total points
ID: 41830975
Like this?

=IF(B2<PERCENTILE(B2:B50,0.2),1,IF(B2<PERCENTILE(B2:B50,0.4),2,IF(B2<PERCENTILE(B2:B50,0.6),3,IF(B2<PERCENTILE(B2:B50,0.8),4,5))))
0
 

Author Closing Comment

by:Bharat Guru
ID: 41836943
Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question