Solved

# In Excel how to add formula for Quintile?

Posted on 2016-10-05
Medium Priority
1,804 Views
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
Question by:Bharat Guru
• 2
• 2

LVL 28

Expert Comment

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

Author Comment

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 28

Accepted Solution

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
``````

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

Author Closing Comment

ID: 41836944
Thanks
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.