Around the Year in 52 Books discussion
Off Topic
>
2019 Spreadsheets - Tips and Tricks?
date
newest »
newest »
I like that mine calculates average pages read per day and hours listened per day. I also like keeping track of author race/ethnicity.I can’t think of anything I’ll get rid of - but I might try automating the crossposting to my specific challenge pages, so I don’t have to do that by hand.
I’m interested in seeing others’ methods! I can’t share from my iPad but will try to add a link when I’m back home where my laptop is.
A lot of my spreadsheet work is borrowed from others, but I do have a couple of things that I haven't seen on other spreadsheets around here: https://docs.google.com/spreadsheets/...Sheet1 is basically a pace calculator. One section is a yearly goal pace calculator so you can see how close you are to hitting your goal based on your current reading pace. I also have two other sections: one for keeping pace on reading all the books we own, and one for calculating how long it would take to read all the books currently on my TBR.
The second sheet is a random chooser. Basically you can put a set of books in the A column, and the randomizer will choose a book for you. Works if you're stuck on "I'm not sure what I want to read next." You can also put a list of prompts on there and choose which prompt you want to tackle next if you're not doing an in-order challenge.
The third sheet is a modified TBR export from Goodreads. I also track where it's available in my local libraries, and if it's available as an Overdrive audiobook from my libraries. The top rows are a tracker to see how much of my TBR and owned books I've accomplished.
(I have a spreasheet that I use for tracking the tasks/goals, but there's nothing fancy there so I'm only sharing this sheet that is different from other sheets I've seen).
Mod
Here's my spreadsheet that I use to track my reading. The stats automatically update as I add to the first tab.
https://docs.google.com/spreadsheets/...
I track all of the lists I follow on a different sheet because it was taking too long for this one to load haha!
Steve, I love the pace calculator stats and totally stole them for my spreadsheet! Thanks!
dalex wrote: "Steve, I love the pace calculator stats and totally stole them for my spreadsheet! Thanks!"Steal away! :)
Emily, I'm playing with your stats sheet and made one minor change that you may want to incorporate. For the average per month in B9, I changed to an averageif with =AVERAGEIF(B25:B34,">0"). I did that so I can see my average of what I've read at any given point without being impacted by the 0's of months to come.
And another minor thing: the "on shelf" count in "source of book" is starting at T1 instead of T2 so it's counting the header as a value.
Mod
I’m totally steeling your pace calculator! I set myself an ambitious goal of 150 books this year, so actually seeing how long I get stuck on one single book might motivate me to read more while travelling.
I stole from various spreadsheets to create mine. It's still a work in progress, I'll probably add some graphical visualizations.I've added some cost analysis in order to check whether my spending habits on books are reasonable or not. Let's see how this goes!
Mod
I think I originally based this on your own spreadsheet, so thank you! Steal away if something can be of use.I'm still playing around with the predictive pacing, of course it's wildly based on guesses but with more data I should be able to make it a bit more accurate.
Manu wrote: "I stole from various spreadsheets to create mine. It's still a work in progress, I'll probably add some graphical visualizations."I really like your pace calculations (time per day reading - hours, minutes) and added those to my spreadsheet. Thanks for sharing!
Bookriot shared their tracking spreadsheet. There might be some great ideas to borrow! (Personally, I'm going to figure out how to integrate the pie charts into my spreadsheet.)
Mod
Emily wrote: "I like it. I was thinking of how I could put charts on my tracker (kind of like Manu's progress bar) without messing up the formatting I already have, but I think I'd have to make a whole new tab f..."The "progress bar" is a sparkline formula, a mini-chart designed to fit into one cell. You could put the charts on the same page as your stats by inserting them at the bottom, below the information that is already there. You could probably add lines, like in the middle of your stats sheet, and insert a chart. The charts just sort of sit on top of the spreadsheet grid so it shouldn't affect the formatting.
I added charts to my spreadsheet on a separate tab. I'd never quite been able to puzzle out how to set them up but I copied the Bookriot chart sheet and customized it by analyzing the information they'd already entered.
I like the idea of the BookRiot tracking spreadsheet, but would like to fiddle a bit with the data in it to suit my own tracking a little better. But where exactly does the data "live"? As an example, if I wanted to change data in the "Source" column - I've no interest in tracking that, but instead I'd like to track the author's nationality.I thought I'd worked out how to do it (I changed the Data Validation (I think that's what it was called) in the first worksheet, but then the data in the chart disappeared. I didn't think it would, because it was essentially just called something different, but I was wrong.
Jody wrote: "I like the idea of the BookRiot tracking spreadsheet, but would like to fiddle a bit with the data in it to suit my own tracking a little better. But where exactly does the data "live"? As an examp..."You have to make changes on the Results sheet. So, like on Cell B31, you would have to change the formula from "purchased" to "American" (for example). Once all of that section on the Results sheet has been changed to pick up the changes to the Data Validation on the Tracking sheet the charts will show your information. I hope that makes sense! It's kinna hard to explain.
Jody wrote: "I like the idea of the BookRiot tracking spreadsheet, but would like to fiddle a bit with the data in it to suit my own tracking a little better. But where exactly does the data "live"? As an examp..."I made these changes on a copy of the Bookriot spreadsheet - you can see those changes HERE. Maybe seeing what I was trying to explain will help.
dalex wrote: "So, like on Cell B31, you would have to change the formula from "purchased" to "American" (for example). "💡 Total lightbulb moment - thank you! I was looking at the A column in the results sheet.
Thank you so much! Off to play some more.
Thanks again for your help, and for sharing that spreadsheet. I love it! I've fiddled with it to suit my own stuff to track (and my own colour scheme that I'm used to for my tracker 😆). This is going to be so much better than two separate worksheets!
Mod
Here's my version. I removed a few things that I wasn't interested in tracking, and added some things that I was (and edited some of the other things). Once I got started, it was so much fun!
Mod
I've added a Visual Stats tab and I'm super excited about it!
I’ve done almost no reading today, but have spent a whole lot of time on my spreadsheet! It’s an investment, right??I may still make some more changes but for now I’m pretty happy with it! I even worked out how to make my own charts. Feeling very accomplished. 💪🏻
I stole the xxx days remaining idea from you. Although I couldn't get the datevalue formula to work, so I did it slightly different. Thanks for sharing.
I like that countdown, plus last year I added in an estimated yearly page total, based on what I’d already read so far in the year - I had a page goal rather than a books goal and that helped me stay on target.
Your spreadsheet looks great Jody! Glad you were able to figure everything out.
I think I'm going to work on a spreadsheet today. Usually I don't bother but it will be good practice brushing up on my excel....I haven't used it in forever so I've forgotten how to do the formulas and all those fun bar graphs and pie charts....
I need your help, spreadsheet gurus.The Book Riot spreadsheet that I'm using is tracking my audiobook hours weirdly. I haven't touched anything with that particular tracking, but I can't for the life of me work out what the hell it's doing. When I select all of the data in the column it adds up to 119 hours, but in the results spreadsheet it's saying 119 days.
Here's my spreadsheet. Can anyone please help?
Mod
Mod
Hi JodyI couldn't find the original Book Riot spreadsheet, so I am not sure if it will be exactly the same but one way to solve it is to input the duration of the audiobooks on the tracking sheet in a different way. Unfortunately it means you will have to redo the ones you already did.
So you go to the tracking sheet and you go to one of the cells where you want to input the duration of the audiobook, then you select Format -> Number -> Duration and then you will have to retype the duration so if an audio book was 4 and a half hours you will have to type 04:30 (as in 4 hours, 30 minutes), in stead of 4.5. If you do this for all of them the total will be correct.
Maybe there is an easier or better way, but this is the only way I could find to also make the 4 days 19 hours xxx correct. If in the total sheet you are ok with just seeing 119 hours, you can just change the format of that cell to the same as your original number format in the tracking sheet.
Irene, the original Book Riot spreadsheet is linked further up in this thread, if you want to take a look at it. 😀I had initially changed all of the hours to that format, but then I got formula errors in the final column of the tracking spreadsheet, where it tracks hours per day. 😬 I might have done something wrong, so I tried again, and for some reason, when I enter 0:19:75 it then changes it to 0:20:15 - same with the others, it adds on half an hour.
ARGH.
Because an hour has only 60 minutes, if you write 75 maybe it assumes it is an hour and 15 minutes? Try it with a smaller number?
Jody I copied your file and tried it:Check if this is what you want?
https://docs.google.com/spreadsheets/...
Irene's solution looks good! I'm not sure I would have figured that out on my own! I've never used the =INT command before.
Jody, I'm not sure why you changed the format from the original spreadsheet. I've copied Bookriot's spreadsheet as well but kept the original format of 00:00:00 (hours : minutes : seconds) and it works well, so maybe you should just change it back, just as Irene has done?It looks like you might have mistaken the original format and thought it was days : hours : minutes?
I didn't change anything with the calculations, just tried different ways of inputting the data. For some reason, calculating time just breaks me. I think because there were no examples in the Book Riot, I was just using the numbers as whole number (eg. 4.5 as four and a half hours), rather than the way they've input the time.Thanks all!
If you're anything like me, now that 2019 is right around the corner, you've been trying to figure out what you want to change, keep, and rearrange on your personal spreadsheets.
I'm always looking to improve mine, so I have a few questions for everyone.
1. What is the favorite feature that you used on your spreadsheets this year?
2. What is one thing you are getting rid of for next year?
Link your sheets (if you can!) and let us all revel in the beauty of the data tracking!