I have a list of values in Google Sheets for example:

10
14
36
43
64
110
92
103

and I want to change it to a range of

0-20, 21-40, 41-80, 81-120

so that it outputs

2
1
2
3

(two values in the range 0-20, one value in the 21-40 range, two values in the 41-80 range, and three values in the 81-120 range.)

2 Answers

2
Tom Sharpe On

You can do it in one step with the Frequency function FREQUENCY(data, classes):

=frequency(A2:A10,{20,40,80,120})

enter image description here

Note that Frequency creates one count per class, plus an extra count for values which exceed the highest class value. You can suppress this if you want to, but it could be a useful check for outliers.

1
player0 On
=QUERY(ARRAYFORMULA({A1:A, IF(LEN(A1:A), 
 IFERROR(VLOOKUP(A1:A, {{0,  "0-20"   };
                        {21, "21-40"  };
                        {41, "41-80"  };
                        {81, "81-120" }}, 2), ),)}),
 "select Col2, count(Col2) 
  where Col2 !='' 
  group by Col2 
  label count(Col2)''")

0

alternatives: https://webapps.stackexchange.com/a/123741/186471