CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-querygs-vlookup
Alan Vu
Alan Vu

Reputation: 3

How to change specific values to be a range instead

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

Upvotes: 0

Views: 36

Answers (2)

player0
player0

Reputation: 1

=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

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34400

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.

Upvotes: 2

Related Questions