Very Specific Spreadsheet Question: Ranges

June 19, 2012 5:19 PM

How do you make Excel automatically group years (1921, 1925, 1929) into decades (1920s)?

Spreadsheet has two columns: (A) year and (B) books published that year on a particular topic. (A) is a four-digit year, and (B) is bibliographic info on a single book. I need to do a graph that will show how many books that I'm tracking were published by decade.

I can do a pivot table to give me counts of these books published each year. However, I don't know how to make it group by decade: a graph (pie chart, etc.) that will take the counts for 1912, 1916, 1917, and 1919 (not every year will have a count); total them; then output something visual.

I'm so baffled I don't even know what to search for, so even providing me with the correct terminology so I can find it myself would be a great help. I'm doing this in Excel.

posted by goatdog to computers & internet (7 answers total)

=INT(A1/10)*10 will give you the start of each decade.
posted by Yowser at 5:30 PM on June 19, 2012

I think you probably want to use some variant of the COUNT function, specifically the COUNTIF function, which counts the number of cells in a particular range that contain a particular text string. So if you've got a hundred entries, you'd do something like "=COUNTIF(A1:A100"191") to count the number of entries in the 1910s, and "=COUNTIF(A1:100"192") for the 1920s, etc.

You can then use your count cells as the basis for a graph. Me, I'd be tempted to stick these on a second sheet, just to keep the data clean, and output the graphs on a third. This would change your formula, but Excel is pretty good about that sort of thing, so it should be pretty easy to do.

Here's a pretty decent explanation of the COUNT function and its variations, including COUNTIF.
posted by valkyryn at 5:30 PM on June 19, 2012

Also, don't forget to put in the start of every decade with a null value so they won't be neglected by your PivotTable.
posted by Yowser at 5:31 PM on June 19, 2012

There is a "group by" function in pivot tables for dates, however it doesn't go higher than years, I believe.

My best guess would be to create a new column, titled Decade, and have it be (I'm assuming the first year is in cell A2):

=rounddown(A2,-1)

(then fill down).

This will round down your year to the 10ths digit, which is functionally giving it a decade, then make your pivot table on the decade column instead of the year column.
posted by brainmouse at 5:31 PM on June 19, 2012 [2 favorites]

For added fun, if you want to add the "s" for "1920s" instead of "1920", you can make that cell in the new decades column:

=rounddown(A2,-1)&"s"
posted by brainmouse at 5:40 PM on June 19, 2012 [1 favorite]

Thanks everyone! brainmouse's did it with the least amount of learning for me, so I'm going with it.
posted by goatdog at 5:41 PM on June 19, 2012

Elegant, brainmouse! I like.
posted by I_Love_Bananas at 6:39 PM on June 19, 2012