How to group data by age range?

Question

Given data list with two columns: 'Division' and 'Age.'

username       year_of_birth
Albert Albo    1977
Bob Bilo       1974
Conan Cornic   1989
Don Duan       1954
Etan Etin      1967
Fabio Forio    1976

I want to put this data into a Pivot Table and group the ages into specified ranges; however, I'm having issues figuring out how to get around grouping them into set increments that don't vary. My first range would need to be 18-24, my next would be 25-29, then 30-34, 35-39, and so on until I hit 64. Then, I would have 65+ all grouped into one, like so:

How could I make it work ?

Answer 1

A simpler (also single formula) might be:

=ArrayFormula(vlookup(year(now())-B2:B+1,Larry,2))  

where year of birth is in ColumnB. This though does require a named range (Larry) of:

This repeats the assumption that, wanting month, day, time, everyone is treated as having been born at the very start of the year_of_birth.

A contingency is included for under 18s where 0-17 in the array might be replaced by invalid or such like.

Answer 2

Just for fun, let's see if we can make it in a single formula

Creating a pivot from here is trivial.