CODEWITHSUNDEEP
if-statementgoogle-sheetsmatchvlookupnested-if
Ian Bong
Ian Bong

Reputation: 45

How to group range of numbers into categories of number ranges in Google Sheets

I have a column of the range of body mass index numbers (BMI) that I want to automatically group into categories or buckets in another column. It looks like this:

colA    colB

BMI     BMI Grouping
23.11   BMI ≥ 18.5 - 24.9
22.66   BMI ≥ 18.5 - 24.9
33.55   BMI ≥ 30 - 34.9
40.12   BMI ≥ 35

I have 5 'BMI Grouping' categories that I want to categorise the range of BMI into:

Please teach me the formula or code needed for Google Sheets.

Upvotes: 3

Views: 7989

Answers (6)

pnuts
pnuts

Reputation: 59495

Please try:

="BMI "&choose(match(A2,{0,18.5,25,30,35,99}),"<18.5","≥ 18.5 - 24.9","≥ 25 - 29.9","≥ 30 - 34.9","≥ 35")

Upvotes: -1

player0
player0

Reputation: 1

you can also use IF logic with ARRAYFORMULA like:

=ARRAYFORMULA(IFERROR(IF(LEN(A2:A), 
 IF((A2:A>0)     * (A2:A<18.5), "BMI < 18.5",
 IF((A2:A>=18.5) * (A2:A<25),   "BMI ≥ 18.5 - 24.9",
 IF((A2:A>=25)   * (A2:A<30),   "BMI ≥ 25 - 29.9",
 IF((A2:A>=30)   * (A2:A<35),   "BMI ≥ 30 - 34.9",
 IF((A2:A>=35)   * (A2:A<1000), "BMI ≥ 35", ))))), ), ))

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

you can use nested IF statements (and drag down) like:

=IF(AND(A2>0,     A2<18.5), "BMI < 18.5",
 IF(AND(A2>=18.5, A2<25),   "BMI ≥ 18.5 - 24.9",
 IF(AND(A2>=25,   A2<30),   "BMI ≥ 25 - 29.9",
 IF(AND(A2>=30,   A2<35),   "BMI ≥ 30 - 34.9",
 IF(    A2>=35,             "BMI ≥ 35", )))))

Upvotes: 1

player0
player0

Reputation: 1

and you can use also CHOOSE & MATCH with ARRAYFORMULA like:

=ARRAYFORMULA(IFERROR(CHOOSE(MATCH(A2:A, 
 {0.1, 18.5, 25, 30, 35, 9999}), "BMI < 18.5", 
                                 "BMI ≥ 18.5 - 24.9", 
                                 "BMI ≥ 25 - 29.9", 
                                 "BMI ≥ 30 - 34.9", 
                                 "BMI ≥ 35"), ))

Upvotes: 0

player0
player0

Reputation: 1

you can use ARRAYFORMULA with virtual VLOOKUP like:

=IFERROR(ARRAYFORMULA(VLOOKUP(A2:A, {{1,    "BMI < 18.5"       };
                                     {18.5, "BMI ≥ 18.5 - 24.9"};
                                     {25,   "BMI ≥ 25 - 29.9"  };
                                     {30,   "BMI ≥ 30 - 34.9"  };
                                     {35,   "BMI ≥ 35"         }}, 2)), )

Upvotes: 0

player0
player0

Reputation: 1

you can use IFS with a combo of ARRAYFORMULA like:

=IFERROR(ARRAYFORMULA(
 IFS(A2:A>=35,   "BMI ≥ 35",
     A2:A>=30,   "BMI ≥ 30 - 34.9",
     A2:A>=25,   "BMI ≥ 25 - 29.9", 
     A2:A>=18.5, "BMI ≥ 18.5 - 24.9",
     A2:A>=1,    "BMI < 18.5")), )

Upvotes: 5

Related Questions