asked on

Excel - Find if a value is in the top 25 %, 50% or 75% of the total value

Is it possible with VBA
1. get the sum of column 3,
2. and then in column 4 determine if value in column 3 is
- in the top1-25% range (If so put an A in the cell)
- in the 26- 50% range (If so put an B in the cell)
- in the 51- 75-% range (If so put an C in the cell)
- orin the bottom 25% (If so put an D in the cell)

Patrick Matthews

Assuming you mean you have a series of numbers in Col 3, and you want to "bucketize" them by quartile...

Sub DoTheMath()
 
    Dim LastR As Long
 
    With ActiveSheet
        LastR = .Cells(.Rows.Count, "c").End(xlUp).Row
        .Range("d2:d" & LastR).Formula = "=VLOOKUP(PERCENTRANK(C2:C" & LastR & _
            ",C2),{0,""D"";0.2500001,""C"";0.5000001,""B"";0.7500001,""A""},2)"
        .Range("b" & (LastR + 2)) = "Total"
        .Range("c" & (LastR + 2)).Formula = "=SUM(C2:C" & LastR & ")"
    End With
 
End Sub

Open in new window

byundt

Assuming all numbers are positive, and without VBA, you might use the formula:
=CHOOSE(1+4*C2/SUM(C$2:C$100),"D","C","B","A")

The equivalent macro is shown below.

Brad

Sub Ranker()
Dim dSum As Double
Dim rg As Range
Set rg = Range("C2") 'First cell in range to be summed
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp)) 'All the cells with data
dSum = Application.Sum(rg)
rg.Offset(0, 1).FormulaR1C1 = "=CHOOSE(1+4*RC[-1]/" & dSum & ",""D"",""C"",""B"",""A"")"
rg.Offset(0, 1).Formula = rg.Offset(0, 1).Value
End Sub

Open in new window

Patrick Matthews

Brad,I guess it all turns on what those percent values are supposed to mean. I took them as meaning percentiles, but who knows... :)Patrick

cd_morris

ASKER

Mathew:
Your solution gave m mostly A's a few C' and D's but no C's.

Byund:
Your solution gave me all D's
RankData.xls

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial