Place %s in a 0-9 10-19 etc. bracket

Question

I have around 8k+ rows of data that is comparing numbers, and giving a % pulled. Random example pulled out of the air...

Place Orders w/drink w/oDrink %w/oDrink
Mcdon   100    25       75       25%

My brackets are:

The 25% would go into the 20-29 bracket. I am just trying to figure out if there is a function I can use to make it distinguish that?

That depends on your setup. You can use MATCH() with just a list of the lower limits of each bracket. — Scott Craner, Sep 28 '17 at 14:43
Why does the value ***9*** exist in two different brackets ??? — Gary's Student, Sep 28 '17 at 14:44
Gary, that was a typo. They tend to happen time to time. Scott, it would go in the 10-19 bracket. — Xiodrade, Sep 28 '17 at 14:46
You just need the starting point for your bracket and use `VLOOKUP` with a `TRUE or 1` instead of `FALSE` on the 4th parameter. Something similar to this (https://stackoverflow.com/a/46433148/4388883) — ian0411, Sep 28 '17 at 14:48

score 3 · Accepted Answer · answered Sep 28 '17 at 14:50

3

Create a table with just the starting values and the desired output:

Then a simple VLOOKUP:

=VLOOKUP(E2*100,G:H,2)

answered Sep 28 '17 at 14:50

Scott Craner

148,073
10
49
81

Just curious, as it doesn't really matter, but why did you do 0-90 for the table, instead of 0-90% and not have the `E2*100` in the formula? Just personal preference? – BruceWayne Sep 28 '17 at 15:01
@BruceWayne it was easier, since I had to create the table. – Scott Craner Sep 28 '17 at 15:02

Kresimir L. · Answer 2 · 2017-09-28T15:13:00.380

You can also use this UDF. Please enter code below in regular module.

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

    Select Case rng.Value * 100
        Case 0 To 9.9999
            strReturn = "0-9"
        Case 10 To 19.9999
             strReturn = "10-19"
        Case 20 To 29.9999
             strReturn = "20-29"
        Case 30 To 39.9999
             strReturn = "30-39"
        Case 40 To 49.9999
             strReturn = "40-49"
        Case 50 To 59.9999
             strReturn = "50-59"
        Case 60 To 69.9999
             strReturn = "60-69"
        Case 70 To 79.9999
             strReturn = "70-79"
        Case 80 To 89.9999
             strReturn = "80-99"
        Case Else
             strReturn = "90+"
    End Select
    BucketNumber = strReturn
End Function

You can now use this formula in worksheets just like regular ones.

Your Select Case numbers may not be valid as the OP wanted *'The 25% would go into the 20-29 bracket'*. That would be **0.25**, not **25**. — , Sep 28 '17 at 14:59

Place %s in a 0-9 10-19 etc. bracket

2 Answers2