?
Solved

In Excel how to add formula for Quintile?

Posted on 2016-10-05
4
Medium Priority
?
689 Views
Last Modified: 2016-10-10
Ho can I calculate Quintile value (1,2,3,4,5) for value field.

Value             Quintile
1.61            1
1.67            1
1.79            1
2.19            2
2.20            2
2.27            2
2.28            2
2.28            3
2.45            3
2.45            3
2.57            4
2.58            4
3.07            5
3.38            5
0
Comment
Question by:Bharat Guru
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41830800
Use this formula:
=MATCH(A2,PERCENTILE(A$2:A$15,{5,4,3,2,1}/5),-1)

Open in new window

0
 

Author Comment

by:Bharat Guru
ID: 41831796
Is above code will do below? If not I'm looking for something like below.

case   if cell("A2").value is between  PERCENTILE(A2:A15 , 0 ) and PERCENTILE(A2:A15 , .2 )
       1
case   if cell("A2").value is between PERCENTILE(A2:A15 , .2 ) and PERCENTILE(A2:A15 , .4 )
       2
 case   if cell("A2").value is between PERCENTILE(A2:A15 , .4 ) and PERCENTILE(A2:A15 , .6 )
       3
 case   if cell("A2").value is between PERCENTILE(A2:A15 , .6 )and PERCENTILE(A2:A15 , .8)
       4
 case   if cell("A2").value is between PERCENTILE(A2:A15 , .8 )and PERCENTILE(A2:A15 , 1 )
       5
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 41832418
Try this function:
Public Function CustomQuintile(rngFirstCell As Range, strRange As Range) As Long
    If rngFirstCell.Value >= Application.WorksheetFunction.Percentile(strRange, 0) And rngFirstCell.Value < Application.WorksheetFunction.Percentile(strRange, 0.2) Then
        CustomQuintile = 1
    ElseIf rngFirstCell.Value > Application.WorksheetFunction.Percentile(strRange, 0.2) And rngFirstCell.Value < Application.WorksheetFunction.Percentile(strRange, 0.4) Then
        CustomQuintile = 2
    ElseIf rngFirstCell.Value > Application.WorksheetFunction.Percentile(strRange, 0.4) And rngFirstCell.Value < Application.WorksheetFunction.Percentile(strRange, 0.6) Then
        CustomQuintile = 3
    ElseIf rngFirstCell.Value > Application.WorksheetFunction.Percentile(strRange, 0.6) And rngFirstCell.Value < Application.WorksheetFunction.Percentile(strRange, 0.8) Then
        CustomQuintile = 4
    ElseIf rngFirstCell.Value > Application.WorksheetFunction.Percentile(strRange, 0.8) And rngFirstCell.Value <= Application.WorksheetFunction.Percentile(strRange, 1) Then
        CustomQuintile = 5
    End If
End Function

Open in new window


Then use this formula and drag it down:
=CustomQuintile(A2,$A$2:$A$15)

Open in new window

0
 

Author Closing Comment

by:Bharat Guru
ID: 41836944
Thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 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