how to convert excel values into buckets

Question

i have a set of data in excel and in one column is a estimate (number of weeks)

i want an excel formula to bucket it into

• Small
• Medium
• Large

where if the value is 0 - 10 then put it Small. If the value is 10 - 20 put it in Medium, etc . . .

if there any elegant way of doing it besides having nested if statements all put together?

Answer 1

May be not quite what you were looking for but how about using conditional formatting functionality of Excel

EDIT: As an alternate you could create a vba function that acts as a formula that will do the calulation for you. something like

Function getBucket(rng As Range) As String
    Dim strReturn As String

    Select Case rng.Value
        Case 0 to 10
            strReturn = "Small"
        Case 11 To 20
             strReturn = "Medium"
        Case 21 To 30
             strReturn = "Large"
        Case 31 To 40
             strReturn = "Huge"
        Case Else
             strReturn = "OMG!!!"
    End Select
    getBucket = strReturn
End Function

Answer 2

The right tool for that is to create a range with your limits and the corresponding names. You can then use the vlookup() function, with the 4th parameters empty (or false) to create a range lookup.