Hugo & Nebula Awards: Spreadsheets and more!

[Sarah]

I'm glad you like that stat; it's a fun one. Although that's based on an average of 2 mins per page, so if you're a faster/slower reader you can tweak it to your own speed.

It does really come off as a challenge, although I wonder how morbid it would be to do a "pages per day so I can read everything before I die" calculation on the spreadsheet. Might just save that one for myself :P

[Bryan, Village Idiot]

Sarah wrote: "...although I wonder how morbid it would be to do a "pages per day so I can read everything before I die" calculation on the spreadsheet... "

I don't think that's to morbid! I've been trying to figure out a way to do that as well...but per book rather than per page. Although I have thought about going through the list and putting a new tab or two with page numbers and audio length for each book...it would be fun data!

I might start on that once I get some things done at work.

[Sarah]

Bryan - I've already added page numbers on mine, and it was a lot of work, so feel free to copy it back onto yours (just make sure to sort both sheets the same way before copypasting).

I'll think of an elegant way to do that "before I die" formula. My brain wasn't working this morning.

[Allan]

With an assumed reading rate (min/pg), your completion list and an assumed life expectancy (which is real for me, I just turned 62), it should be an easy calculation. I added a cum pages vs time chart, so I can see where I am relative to last year, but you could easily extrapolate that to the full list. I've also got a bar chart showing % of pages read by year. I'll try to post an update shortly.

[Rebecca]

Conversations like this one make me know I found my people.

[Bryan, Village Idiot]

@ Rebecca: Amen to that!

@ Sarah: Thanks for the heads up, Sarah!

@ Allan: I was thinking the same. A quick Google check shows that the average reading speed is 200 - 250 words per minute or roughly 2 minutes per page.

[Kateblue, 2nd star to the right and straight on til morning]

I love watching you guy talk. I would do more on my spreadsheets, but I had to do to much in my last job, so mine is just minimal. I love the reading sped discussion.

And, Allen, my presumed life expectancy is why I am quitting books when I'm not having any fun. And when I do quit, if I have given it a good try, I check them off on my spreadsheet as "read." So maybe my count is unfairly high, but then, you guys can just tell me so next year when I post my new percentage.

[Allan]

I've uploaded my latest spreadsheet version to the google drive. I've modified Bryan's book table to include source (where I found the book), which drives the automated color coding, plus yearly summaries. I added page counts, which may differ because of versions. I've tried to use the version I own or have identified. I moved the list sorts to their own tab but didn't do anything else with them. Then I created a tab to hold the yearly data and the several charts. I put a "countdown" calculation there too, but took a positive spin - how long it would take to read them all at my current page rate. That restores the book list page to that alone, without all my other stuff on there; better organized. Let me know what you think!

[Allan]

The one thing it doesn't anticipate is the books that get added each year. A dozen books adds about 3600 pages, about a month and a half for me.

[Sarah]

Allan wrote: "The one thing it doesn't anticipate is the books that get added each year. A dozen books adds about 3600 pages, about a month and a half for me."

That's also what I was struggling with, because it seems that the trend is more longer books each year, thus more and more pages per year. That could be because newer books have bigger fonts, more padding, etc.? It could actually be because books are generally longer than a few decades ago. Or it could be that it takes a more hefty novel to get nominated these days.

It's all approximate anyway so it doesn't matter so much. But you should remember to add in the retro Hugos too, not just new ones.

[Allan]

Retro Hugos are in my worksheet and there's a chart showing the average book length by year. At a macro view, there's a pretty steady increase from around 200 pages in 1962 to over 450-ish by 2000. After that, it varies a lot but averages out at 450-500.

[Allan]

Maybe the next step on the spreadsheet is adding word count (that's a joke!).

Today I added distribution charts of the books I've read this year and last by page length and by year. Last year I was slightly skewed towards shorter books and this year I'm very heavy on more recent reads.

[Antti]

Oh man, all that talk about "pages left before death" made me almost involuntarily do the calculation: I'm reasonably confident I'll keep up with a long-time average of a book per week at minimum, so that's 50 books per year. I should expect to have something like 40 years left, so that means... 2000 books left before I go.

Two thousand feels like a really small number. Hell, I've got a thousand books in my home: just twice that amount left. It's really not that many.

It's been a long time since I had such a vivid and stark reminder of my own mortality. Thank you for that.

[Bryan, Village Idiot]

Holy Hell, Sarah! Your spreadsheet has taken on a life of its own!

I see that you marked "Up the Walls of the World" and "The Orphan" as a possible error. Up the Walls of the World, we talked about somewhere. I'm not as good as Art in finding old posts. Alice Sheldon (James Tiptree Jr) was nominated for the Hugo, but withdrew. I can't remember why, but I remember we decided that her book still deserved a place on our list.

As for The Orphan, it was nominated for the Nebula.

I really like your spreadsheet. I made some Audio changes and will probably make a few more upgrades...then I think we should make it the official group's spreadsheet! I've added the it to the google drive

[Allan]

Aw, you didn't like my distribution of books by lustrum???

[Sarah]

Bryan wrote: "Holy Hell, Sarah! Your spreadsheet has taken on a life of its own!

Glad you like it Bryan! I'm very happy for the group to appropriate it. Actually that's what I was hoping for :) I've just cleaned up some stray things from the data sheet, and added an explanation on the summary, so now it's good to go.

For anyone using the original spreadsheet, they should be able to paste in my summary tab, and with some small edits it'll start working on its own (so they don't have to re-enter all their old data)... I'll double-check exactly what they need to edit.

Thanks for fact-checking as well. I had been meaning to ask about those.

What audio-related edits did you need/want? Was it just the length? Was this a straight pages *n calculation, or did you find the actual audiobook lengths somewhere?

[Sarah]

Antti wrote: "It's been a long time since I had such a vivid and stark reminder of my own mortality. Thank you for that. "
Glad to help! :) Gotta make sure we're getting what we want from life. I think 2,000 books before I die would be great. I do need to get better at avoiding books that are not worth the effort, but since I'm a librarian I get a lot of practice hah.

Alas, I'm also a completionist...

Allan wrote: "Aw, you didn't like my distribution of books by lustrum???"
I'm afraid I'm still getting lost in yours. Horizontal in addition to vertical scrolling requires some serious exploration. And I notice you've added more colours since last time :P

[Allan]

As far as getting lost, you can ignore most of the tabs but for HN Sce, Charts, Read. I've eliminated most of the horizontal scrolling, except on the Sorts tab, which I haven't done anything with. The colors are now keyed to the source, where I go to find the book when I'm ready to read it. I can't handle straight black and white - I'm a very visual person and colors make it so much easier for me to read. You might look over the Charts - there might be a few that the group might find useful. I don't mind tables of numbers, but charts are better for me to see what's going on and tell where I am.

[Sarah]

Allan wrote: "You might look over the Charts - there might be a few that the group might find useful."
I like the % of pages read/year chart. It's a nice visual thing that I think I can make work on mine.

I notice the "finish clock" there too now, in bright red text haha. Hey, only 5.1 years? You'll still have time to fit in the novellas and shorts at this rate ;)

[Allan]

Lol, well it's kind of an idealistic number. 85 pages per day just on the listed books is very dedicated. In terms of book count, I added almost 9% in 2018, and I'm projecting about 12% in 2019 to get over 30%. So 10% a year puts the finish out about 7 years. The time difference indicates that I've read more of the longer books - no doubt skewed by having read The Wheel of Time.

[Bryan, Village Idiot]

@ Allan: Yea that is really make a commitment! My goal at this time in my life is to read more than is added to the list each year! At least then I won't move backwards.

@ Sarah: 2000 books in a lifetime would be very impressive! I've often thought about going back and trying to figure out which books I read before I started keeping track. Make an "Unknown Year" section. But, I didn't really start reading till I was out of college. I've been trying to keep track of my son's books. He's only 4 months old, so everything is what we read to him...but I think it would fun for him to see all the books read to him...if not, then it will be for me!

As for the Audio part. I did a straight (page count*2) in a column away from the table, then just did a translation into hours and mins in the actual "Audio" column. Audiobooks have different times, due to the reader's speed and anything they edit into the audio. Such as music, intros and exits, and sometimes an interview with the author or about the author. On my person tracker, I've been using the Audible count, mostly because I list to everything.

I want to figure out how to do an If/Then statement where it will automatically subtract read book times/pages. Then we could make an auto completion counter. You two may already have something. I know it can be done, I just haven't done it myself...yet

[Allan]

I'm adding up read/not read pages and books by book year, and by month. When I change the book status to Read (on the H/N Sce tab - I put a "1" under Read and delete Source), it updates all the counting, adding up & charting automatically. I can certainly help you if you can tell me exactly what you want to do, e-mail's probably easiest. Only about 20% of my reading is audio, so I don't worry about that. I also don't worry about book minutes, because I always have 3-6 books going at once and I skip around. Days are good enough for me. I'm going to put a pared down version of my sheet out there so that my miscellaneous tabs for fooling around don't confuse anyone who's interested.

[Sarah]

Bryan wrote: "I want to figure out how to do an If/Then statement where it will automatically subtract read book times/pages."

=SUMIFS(H:H,C:C,"<>")

Totals everything in column H (pages) for rows where column C (read/unread) is empty, giving you a # of pages unread. Seems like the kind of thing you wanted? Just multiply by 2 to turn it into minutes.

[Bryan, Village Idiot]

Hello Everyone! Some exciting news for those of us that love spreadsheets! In a joint effort, Sarah and I have updated the tracking spread sheet to include a lot more data. I've uploaded it to the website now. There is a "HowTo and Credits" tab at the bottom that explains how to use the new features.

We have a few more things we want to do in later versions. We need to do the Audio time like the Pages and maybe even this "Death Clock" count down as well!

Please let us know if you see anything that needs to be fixed or what you would like to see in future updates.

[Oleksandr, a.k.a. Acorn]

Great work! I'm in the process to make a mega-sheet with all major rewards. Currently I try to make a web-scrapping to automate the process

[Allan]

I'll take a look tonight if I can. I keep my custom version but perhaps there are things I can steal.

[Rebecca]

thanks for all your work on this, hadn't looked yet but will once I have internet back uo

[Sarah]

I've cleaned it up a little since the latest iteration, so just hold off until Bryan makes the latest update :)

If there's a particular stat that anyone would like on the master sheet, please suggest.

Eventually I'm hoping to add some pretty graphs for the more visual folks.

[Kalin]

Hey, just want to throw out that it looks like "Ancillary Mercy" by Ann Leckie is listed as a Nebula winner for 2015. It was actually won by Annihilation, which is also listed as a winner.

Don't want to make any edits myself to the original -- not sure I'd have the permissions anyway.

[Sarah]

Hi Kalin, thanks for that! If you find any more errors, even tiny stuff, please post again.

There should be a new version of the sheet coming up very shortly.

[Allan]

I stuck an updated version of my worksheet out there on the google drive, if anyone is interested.

[Sarah]

Allan wrote: "I stuck an updated version of my worksheet out there on the google drive, if anyone is interested."

Cool :) What did you update?

I've just noticed your 'charts' page with the average pages per book per year. It's nice to look at. I don't think it's new though?

I previously tried to automate a prediction for the number of pages in upcoming years, as the trend is for the number of pages to increase, but 2014 really throws things off. And they can't keep increasing forever! A complicated problem that's beyond my spreadsheet skills.

[Allan]

A few small improvements here and there, some book source changes, a couple of small tables on the charts page, such as a tracker for just the winners. When the year rolls over, I'll go through a complete update to add 2020 to the tables and change the colors (deep purple is the current year, mid-purple is previous year, lavender is read before that). I also flipped the group reads tab to move horizontally in years; otherwise the list starts getting too long. I need to come up with a few more charts.

The H-N Source tab, which is also the read/not read tracker from the original master, is color-coded by source. If you put a "1" under "Read", with the year in the last column and blanks in the source columns, the whole line turns deep purple, making it easy to pick out books read in the current year. If you are currently reading one, just put the year in and leave the sources, and the whole line will show bright red. Otherwise, the source determines the line color. I'm pretty visual, and I love to scroll through the color pattern formed. It's also useful in that you can quickly see where you have reading gaps and can plan accordingly.

On the average pages per book by year chart, you can see the steady increase starting in the 60's, but the last 20 years have pretty much leveled off, although with greater variation. I'd focus on predicting from those two decades and I'd adjust 2014 by only counting one Wheel of Time book, or maybe none.

[Kateblue, 2nd star to the right and straight on til morning]

I need the URL for the Google drive. I saved it.

Sorry

[Allan]

2nd URL in the top message of this thread

[Kateblue, 2nd star to the right and straight on til morning]

Thx Allan!

[Allan]

For anyone interested, I uploaded the 2020 version of my customized spreadsheet (v12). The "H/N Sce" tab is the book list. The colors, stats & Charts tab are all controlled by the 8 columns to the right of the page count. Books read are indicated by placing a "1" in the Read column, plus the year in the YR RD column. If not read, a "1" should be placed in one of the Source columns, indicating where that book can be found.

The colors have been confusing to some people but here's the key: anything in blue shades was read 2018-2020, purple in years before that. Red indicates Currently Reading. All the other colors are indicators of Source - yellow=owned, green=Open Library, orange=local Library, grey=No Source Found. Tells me where to go when I decide to read something. I don't use audio enough to matter, that's why it's not in my version.

Nothing has changed on the Charts page, other than adding places for 2020. The Death Clock is still there! The Groups tab shows our groups's monthly reads & challenges 2018-2020, colors consistent with the list - that might be useful in the standard spreadsheet. Finally, the "Read" tab is just me keeping track of everything I read, listed or not, and some stats.

[Nooilforpacifists]

Allan,

Thanks! I’ve got lots to look through…

NOfP

[John]

Bryan wrote: "Sarah wrote: "...although I wonder how morbid it would be to do a "pages per day so I can read everything before I die" calculation on the spreadsheet... "

I don't think that's to morbid! I've bee..."

Thanks to this group and the SciFi Fantasy Book Club my TBR just hit 2025. And I'm not done checking books on bookshelf so it might hit 2525.

Corrupting lyrics from the song In The Year 2525 If man is still alive, I might have read my TBR.

[Allan]

Now that is ambitious! I only have 1087, but I might not have all the Hugo/Nebula books or all the history books on my shelves at home. Live long and prosper!

[Bryan, Village Idiot]

I'm messing around with my spreadsheet as well and I added total number of pages and time (Audio), total read, and how many left to go. Plus the % Completed in each part.

Its interesting. Just looking at books, I have 20.21% complete. Pages: 24.48% complete. Audio: 24.57% complete.

In case anyone was wondering, there are 579 books, 214,648 pages, and 10 months 19 days 11 hours & 38 mins.

To put that in perspective: I've been keep track of how many books I've read since Jan 2016. In 3 years, I've read 341 books which is 6 months 0 days 13 hours & 52 mins.

[Allan]

I love seeing people's stats, so thank you. I wish I could track back that far. I only really started detailed tracking in late 2017, when I became determined to read more and started using GR extensively. I've read 208 total books across 18-19 for 68,037 pages (audio is a small proportion for me). I'm now at 32.0% (185) of the 579 books.

I've altered page counts to reflect the book editions I have access to. It's amazing to me that the total is only 205 pages different.

[Kalin]

Bryan wrote: "10 months 19 days 11 hours & 38 mins...."

I'm a proponent of subtracting 8 hours from each day so that the counts for days/months of media consumption time is measured in blocks of 16, rather than 24, accounting for sleep. This would put the number of full days above a year.

[Allan]

That's audio time, right?

[Allan]

Wow, me too! I have a pretty good memory for what I've read, but no way I could get that much detail!

[Allan]

I'd have a couple thousand comic books in there

[Kateblue, 2nd star to the right and straight on til morning]

I have often gotten 10% or even 25% into books and suddenly realize I have seen it before

[Sarah]

Scott wrote: "I thought this site might be of interest:

https://whatihaveread.net/"

This is amazing. It looks like they were quite the SF fan in the 80s, but now read mostly non-fiction. And they had grandkids around 2010.

Have you seen the FAQ?
"boy, do you have some serious reading to catch up on... your literature knowledge is pathetic, and you haven't even read Plato. call yourself educated?"
I suppose they are quite open to scrutiny, but they HAVE read a lot of great books.

I've been tracking my books on Goodreads since late 2018, so not long at all. I don't want to go back and guess what I read and when, because I like to know my data is 100% accurate rather than being more 'complete'.

[Bryan, Village Idiot]

Allan wrote: "That's audio time, right?"

Yes and roughly calculated based on the page numbers.

Scott wrote: "Oh, how I wish I had records going back to when I was a kid!"

I wish I had records like that as well! Funny you would say this, because I started keeping track of my son's books. Maybe when he's older he will be a data and book nerd like his old man.

[John]

Was curious enough to go thru the time and effort and see what I have read of Hugo and Nebula awards novels. Using the bookshelf I am at 32%. Was thinking I had read a lot of award novels and am now conflicted. Not sure if I should be happy at how many I have read, happy at how many more I get to read, or disappointed that I have only read 158 of 590.

Have read a bunch of short fiction (or think I have) so might give the spreadsheet a shot later.