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

Question

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:

BMI ≥ 18.5 - 24.9
BMI ≥ 25 - 29.9
BMI ≥ 30 - 34.9
BMI ≥ 35
BMI < 18.5

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

score 5 · Accepted Answer · answered Feb 13 '19 at 13:00

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")), )

player0 · Answer 2 · 2019-02-13T14:09:32.313

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", )))))

edited Feb 13 '19 at 14:09

answered Feb 13 '19 at 12:45

player0

124,011
12
67
124

score 0 · Answer 3 · answered Feb 13 '19 at 13:15

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)), )

player0 · Answer 4 · 2019-02-13T14:11:54.157

0

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", ))))), ), ))

edited Feb 13 '19 at 14:11

answered Feb 13 '19 at 13:37

player0

124,011
12
67
124

score 0 · Answer 5 · answered Feb 13 '19 at 14:05

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"), ))

pnuts · Answer 6 · 2019-05-31T13:35:09.547

-1

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")

edited May 31 '19 at 13:35

answered Feb 13 '19 at 05:21

pnuts

58,317
11
87
139

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

6 Answers6