Around the Year in 52 Books discussion

Omg Emily, that spreadsheet is awesome. Would you mind if I borrowed some elements?

Have at it!

I also have a blank spreadsheet that I made to distribute on my blog. You can get it in my blog post here: https://www.bourquesbooks.com/trackin...

I took most of my stuff off and tweaked it so anyone can use it!

Emily wrote: "I also have a blank spreadsheet that I made to distribute on my blog."

This is fabulous! I hate setting up the statistics part of my spreadsheet so I will definitely be using yours. Thanks.

No problem! I’m happy to share! Just let me know if you run into any formula problems. You shouldn’t have to edit anything on the Stat tab.. it should auto fill when you enter information.

Ok, so I have a question for all you math geniuses and spreadsheet wizards. When calculating the average publication date for the books you've read, the "average" formula yields a result that doesn't make sense to me.

For example, one book published in 1932, one book published in 2001, and three books published in 2017... the average is 1997. But shouldn't it be something more like maybe 2013 since the majority of the books were 2017? Those older publications should just bring it down a little, right?

I'm so baffled. Help! :)

dalex wrote: "Ok, so I have a question for all you math geniuses and spreadsheet wizards. When calculating the average publication date for the books you've read, the "average" formula yields a result that doesn..."

I guess I'm not sure what you mean because if I add 1932 + 2001 + 2017 + 2017 + 2017 and divide the sum by 5, I do get 1997. Keep in mind, 1932 is almost 70 years from 2001 so it's going to bring down the average quite a bit.

Similarly, if you scored a 20 on a test, with a 90 and 3 100s, given that all the test averages are weighted equally, you're going to end up closer to 80%, not in between 90 - 100.

Not quite the same effect, but similar.

Also not sure if that helps at all. I guess all I can defend is that the formula isn't wrong!

When I do the averages on a calculator, it comes out to 1996.8, so the formula should be correct. I can’t explain why, I just know that it works 😂 The 1932 would bring down the average significantly.

Thank you Samantha and Emily.

As I pondered about it a bit more my brain recalled some terms from math classes long long ago and as I researched I realized that what I was actually wanting the spreadsheet to do was to calculate the "median" - the number that would fall in the middle of a range of numbers. Redoing the formula as median instead of average gave me a number that makes more sense to me.

And this is why I am not a statistician. :lol:

The straight average will be moved a lot by even one outlier. However, if you wanted something that better reflected the mode (ie most common) publication date, you could devise a weighted average. I'm going to refer you to the wikipedia article if you want an in depth explanation, because I am not the best at explaining math stuff.

https://en.wikipedia.org/wiki/Weighte...

dalex wrote: "Thank you Samantha and Emily.

As I pondered about it a bit more my brain recalled some terms from math classes long long ago and as I researched I realized that what I was actually wanting the sp..."

Gotcha! I was thinking you may be thinking median in which case it would be 2017 but was thrown off by the 2013!

Thank you for the info about weighted average. I found an example about how to do that when recording grades for a classroom and it makes perfect sense. I may tinker with my spreadsheet to see what kind of result that formula would yield.

Samantha, the 2013 was a random number that made sense to me, not anything mathematically accurate (because I suck at math). :)

Me, again, with the questions. :)

Is there a way to create a formula that will automatically check a checkbox? What I mean is if I have a series of checkboxes on Sheet 3 and I want the boxes to get checked off if I type something on Sheet 1, is that possible and how do I do it?

There is, but I don’t know how 🙃🤷🏻‍♀️

Oooooh apparently it's really easy in Google sheets! https://www.benlcollins.com/spreadshe...

Emily wrote: "Oooooh apparently it's really easy in Google sheets! https://www.benlcollins.com/spreadshe..."

I saw that blog in a quick search earlier today and was going to explore more in depth when I got home from vacation later this week if no one else figured it out yet. You’ll have to let us know if that works!

Emily wrote: "Oooooh apparently it's really easy in Google sheets! https://www.benlcollins.com/spreadshe..."

Easy, huh? That's one of the pages I looked at when researching and if the answer is there, I do not see it. :)

Looking at the comments on that blog link, and playing around in Sheets myself, I don't see a way to do it. There isn't a way to "trigger" the checkbox based on the contents of another cell. It has to be manually triggered by clicking on it, or typing in a word/phrase if you set up data validation.

You couldn't do it with an IF statement? So that if a certain cell (or range) equals x (or a certain number), it checks the box?

I can talk to my spreadsheet friend tomorrow at work and see what he thinks. I know he has set up check boxes that refer to cells, but I don't know if you can do it so that it checks the box if you meet a certain criteria.

What Emily said will work. Something like
=IF(isblank(targetcell), “”, “X”)
Replace targetcell by clicking on the place you are looking at. If it is blank, the cell with the formula will be blank too, if there’s anything in it, you’ll get an X. You might be able to replace the X with a check mark using copy and paste or something, not sure.

I tried doing it with Chrissy's formula, using a blank cell (that wasn't formatted for a checkbox), and I can't get that to work either. Can anybody telling me what is wrong with this formula?

=IF(isblank('2019'!Q5), “”, “X”)

I want it to put in an X if there is an X in cell Q5 on sheet 2019 but to leave it blank if cell Q5 is blank.

Thanks!

Im just watching this thread in awe lol, my excel skills suck. I leave the spreadsheeting to you. If I did one it would be the same as if I set up a table in Word hahaha.

dalex wrote: "I tried doing it with Chrissy's formula, using a blank cell (that wasn't formatted for a checkbox), and I can't get that to work either. Can anybody telling me what is wrong with this formula?

I just tried this exact formula in both Excel and Google Sheets and it worked in both places:
=IF(ISBLANK(Sheet2!A2),"", "X")

Maybe spaces between commas is an issue? Not sure. Try copy and pasting the above, and just replacing the cell reference?

Thank you so much Chrissy! It must have been something with spacing or capitalization or something because when I c/p the formula from your post it works! Yay!

Tracy, I understand. I can do a goodly bit with spreadsheets but formulas are not my friend. (I'm still trying to figure out what "foo" means, as in IF(A2 = "foo","A2 is foo") LOL.)

=IF(ISBLANK('SHEET'!CELL),"□", "✓")

This should work to give you a check mark or empty box. Note that it's not the "official" checkboxes of Google Sheets, but it at least gives you the symbol if that's what you like. And should also note that that blank box would count as the cell being filled in, so if you're using a count or sum function, it would count that empty box.

dalex wrote: "Thank you so much Chrissy! It must have been something with spacing or capitalization or something because when I c/p the formula from your post it works! Yay!

Tracy, I understand. I can do a good..."

I bet it was not recognizing smart quotes. And "foo" just means whatever placeholder variable value to programmers, I gather. if there are two things sometimes you'll see foo and bar.

OMG I just realised I erased my book spreadsheet from google drive by mistake and emptied the bin!!! I freaked out, but just contacted customer support and they were able to recover it for me pffeww....

Just wanted to share my panic of the day with you guys haha

Sophie wrote: "OMG I just realised I erased my book spreadsheet from google drive by mistake and emptied the bin!!! I freaked out, but just contacted customer support and they were able to recover it for me pffew..."

Nothing is ever truly gone from the internet... worked in your favor today!! Glad it was recoverable!

Anyone good with random functions? I have one set up now that’ll give me a single random choice from a list. I’m looking to set one up that gives me five unique choices from a list. Can’t figure that part out! Anyone got a solution? This’ll be helpful for anyone who wants to use a Google sheet for a “jar of prompts” challenge, too.

EDiT: never mind. I found a spreadsheet from my archives that had exactly what I needed. I’ll share more later during my holiday break when I’m on my computer again, but essentially you make column A your list. You fill =rand() in column B. You put an index/rank function in column C.

You're a spreadsheet rockstar, Steve!

Emily wrote: "You're a spreadsheet rockstar, Steve!"

You flatter me; but really, I'm a Google search rockstar!

So to make this work...
Put your list in column A.
In column B, fill "=RAND()" down the column.
In column C, fill "=INDEX(A:A,RANK(B1,B:B))" for as many cells as you want choices. So if you want the sheet to choose 3 random things from column A for you, fill in 3 cells (B1 will need to change to B2 and B3 but the sheet should automatically do that if you're filling down).

Note that this is a "volatile" function so that means that any time you reload the file or you edit ANY cell in the spreadsheet, it's going to recalculate and give you new random choices. If you want to keep the list it generated, you need to copy the cells and do a "paste values only."

Steve wrote: "Emily wrote: "You're a spreadsheet rockstar, Steve!"

You flatter me; but really, I'm a Google search rockstar!

So to make this work...
Put your list in column A.
In column B, fill "=RAND()" dow..."

Here's what it looks like in action: https://docs.google.com/spreadsheets/... (It's the "ebook chooser" tab if it doesn't automatically send you there). I'm going to play with it more in a few days, but currently it's pulling from my "TBR books" tab where "yes" is the value for if the book is available as an ebook somewhere.

Saw this tweet today. Thought you'd all enjoy it: https://twitter.com/justinmilleresq/s...

I love that Steve. I had three different people tag me in that meme 😂

Hi spreadsheeters!

I've created a new audiobook category in my stats this year, because last year was the first year I really listened to audio regularly and it felt like cheating to just count pages for those when I actually listened.

I'm just having a bit of trouble with categorizing my audio books, since I haven't really tracked them.

I currently have it divided up like this:

Short (<5 Hours)
Medium (5.1-11 Hours)
Long (11.1-14 Hours)
Very Long (14.1-17 Hours)
Doorstop (>17 Hours)

But I've listened to two books this year, and the first one was 15.75 hours, while the other is 18... so I feel like I should raise the bar for the long ones.

How do you have yours categorized? Any suggestions?

Emily,
I think it depends on the audio books you listen too but based on my general audio book use I'd change it to something like this:

Very Short (<5 Hours)
Short (5.1-11 Hours)
Medium (11.1-18 Hours)
Long (18.1- 25 Hours)
Very Long (25.1-35 Hours)
Doorstop (>35 Hours)

Part of it is just that audio books take several hours longer to listen to than to read the print version which is why your doorstop of 17 plus seems too low to me. Then again, I also read really long books so a 17 hour audio books seems like an average book length.

That's what I was thinking, Jillian! Most of the audiobooks I've picked up in the last month have been 15-18 hours, so I thought that should be more medium, but I haven't really paid attention to it, so I couldn't figure out how to divide the categories.

Thank you for that suggestion!

I think the longest audiobook I listened to was 24 hours and that was too long for me! Most of mine are between 10 and 12 hours and I'd say they were medium length books? Maybe compare some of the page counts compared to hours...some narrators are just slowpokes.

Although I'm using someone else's template this year and they have:
0-4 hours:
4-8 hours:
8-15 hours:
15-25 hours:
25-40 hours:
40+ hours:

I listened to Anna Karenina which came in at 35 hours and that was plenty long enough for me lol. I think I've decided to go with:

Short (<10 Hours)
Medium (10.1-17 Hours)
Long (17.1- 23 Hours)
Very Long (23.1-30 Hours)
Doorstop (>30 Hours)

(My spreadsheet stats formatting is keeping me limited to 5 categories only lol)

That seems like a good plan.

Last month, I finished Les Misérables which was over 60 hours 😳 and listening to it at an increased speed it was too long.

Oh wow, 60 hours. The longest audiobook I ever listened to was It at 45 hours.

I’ve created my spreadsheet for 2022 reading, & it’s working great. However, I use Apple Numbers, & I can’t figure out a formula for COUNTUNIQUE. I’ve googled solutions & am at a loss. I’m just wanting to count the number of unique authors I’ve read during the year. Does anyone use Apple Numbers & have an idea how to do this?

Liz wrote: "I’ve created my spreadsheet for 2022 reading, & it’s working great. However, I use Apple Numbers, & I can’t figure out a formula for COUNTUNIQUE. I’ve googled solutions & am at a loss. I’m just wan..."

Not a Numbers user but this seems to be a solution: https://apple.stackexchange.com/quest.... Sounds like a two step solution instead of a single formula function like you’d get in Excel or a Google Sheets.

This is not related to books/reading but I know there are some spreadsheet geniuses here so I thought I'd ask for some help.

On a google spreadsheet I created, I have a column with letters designating days of the week. I set up a formula to count how many instances there are of each letter. For some reason the formula will not read the letter T! I can substitute absolute anything else for the T and the formula will read it.

Anybody have any ideas? Thanks!

dalex wrote: "This is not related to books/reading but I know there are some spreadsheet geniuses here so I thought I'd ask for some help.

On a google spreadsheet I created, I have a column with letters designa..."

T is a function in Google Sheets, so maybe it's confused?

Jaime wrote: "T is a function in Google Sheets, so maybe it's confused?"

Aha! That makes sense. Thank you!

For all my spreadsheet friends, just a heads up that tomorrow I will be removing the 2023 Plan tab and opening up the A-Z tabs for 2023... trying to get the spreadsheet ready before the baby gets here.

I know some of you like to jump on those threads early to claim your spot, so I'll likely be doing that tomorrow morning (around 7-8am CST), if you want to set an alarm lol

Emily wrote: "Have at it!

I also have a blank spreadsheet that I made to distribute on my blog. You can get it in my blog post here: https://www.bourquesbooks.com/trackin...

I took most of my stuff..."

Emily, I just want to say a HUGE thank you for sharing your personal blank spreadsheet! It is fantastic!! I played around with it today and pre-add my expected January reads just to get a feel for how it works and am so excited to start reading and continue using.

Yay! I'm so glad it works for you!

Here’s my spreadsheet! It’s been fun going through my TBR shelves and pulling books that fit the challenge. I have them all in my living room now, ready to go!

https://airtable.com/invite/l?inviteI...

Kelly wrote: "Here’s my spreadsheet! It’s been fun going through my TBR shelves and pulling books that fit the challenge. I have them all in my living room now, ready to go!

https://airtable.com/invite/l?invit..."

Kelly, is there a way to view your sheet without making an account? It’s prompting me to create one to view it.