Very Specific Spreadsheet Question: Ranges
June 19, 2012 5:19 PM Subscribe
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.
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.
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
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
posted by Yowser at 5:31 PM on June 19, 2012
Best answer: 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 [1 favorite]
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 [1 favorite]
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]
=rounddown(A2,-1)&"s"
posted by brainmouse at 5:40 PM on June 19, 2012 [1 favorite]
Response by poster: 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
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
posted by I_Love_Bananas at 6:39 PM on June 19, 2012
This thread is closed to new comments.
posted by Yowser at 5:30 PM on June 19, 2012