mercredi 3 octobre 2018

Google Sheets - Group By If/Then Buckets, Calculated Values

In Google Sheets, I'm trying to write a Query (not a stand alone Pivot Table nor script), which groups by a calculated value based on an if/then statement.

Here's my sample data:

Name    Days
Bob     0
Ed      1
Frank   2
Joey    4
Deluth  7
Henry   12

Grouping by column B is fairly straight forward:

=QUERY(A1:B7,"SELECT B, COUNT(B) GROUP BY B")

which outputs:

Days    count
0       1
1       1
2       1
4       1
7       1
12      1

But what I'm trying to do is group the days in to specific buckets. The buckets and desired output would be this desired output:

Days    count
0       1
1       1
2-5     2
6-9     1
10+     1

So it's almost an "if 0 then '0', if 1 then '1', if >1 AND <=5 then '2-5', etc., with a Group By at the end? How would this be written in QUERY format?

Aucun commentaire:

Enregistrer un commentaire