2024 Team Competition Tracker / Google Sheet Central

[Nyssa]

Hi @Ian. What are the parameters for the book-length categories?

- Short Story
- Novella
- Novel
- Tome

[Timelord Iain, Tech Support]

Nyssa wrote: "Hi @Ian. What are the parameters for the book-length categories?

- Short Story
- Novella
- Novel
- Tome"

100 pages
250 pages
600 pages

Those are the breakpoints... they can be changed pretty easily...

[Nyssa]

Thank you. I don’t want to change them; I was just curious.

[Timelord Iain, Tech Support]

It’s hard to breakdown… if you want to get granular, there’s also novelettes and microfiction

[Nirkatze]

Santa Iain, I'm going to need more rows in my spreadsheet soon... I don't want to touch it and mess things up...

[Timelord Iain, Tech Support]

All done... adding rows isn't too hard... for safety, I add them in between working rows, rather than right at the end, to lower the chances of the sheet trying to exclude the new rows from formulas at the top... then you just need to copy the formulas in columns LMNOP... to do that, you just highlight the cells in a few rows (to make sure the sheet knows to increment the row number in the formulas instead of straight copying the exact formula), and then use the larger blue square in the bottom right corner to drag/copy the formula down to the end of the sheet... and done...

I added 100 extra rows...

[Timelord Iain, Tech Support]

In the past, I've had some issues with people adding extra rows at the top or something, and then the formulas at the top exclude the first 1-2 book rows... not entirely sure what causes that, but I assume it's similar to the risk of adding rows at the very end of the sheet...

[Nyssa]

Ian, is it too early to make requests for next year's Google Sheets?

I have two:

1. Can the Source column be turned into a drop-down list, and 2. Can the BR Column be reverted to Y/N? Please and thank you.

Everything else is excellent!

However, I initially deleted some rows in January, February, and March because I forgot the months were collapsable.

[Timelord Iain, Tech Support]

It's a bit early, but I'll keep it in mind... when I make the 2025 sheet I'll make a new thread, but I'll keep this in mind and other can feel free to mention similar...

Nothing you mentioned will be hard to implement... drowdown lists are the easiest... I've implemented a bunch of those in my sheet to simplify repetitive inputs...

[Nyssa]

Timelord Iain wrote: "It's a bit early, but I'll keep it in mind... when I make the 2025 sheet I'll make a new thread, but I'll keep this in mind and other can feel free to mention similar...

Nothing you mentioned will..."

Thank you!

[Monika]

Iain, any chance I can ask you a favour? Can you please change audio on my sheet into graphic novels? I totally forgot to ask you about it at start of year silly me, and I don't do audio when I do sometimes read graphic novels LOL! Thank you in advance :)

[Timelord Iain, Tech Support]

Monika wrote: "Iain, any chance I can ask you a favour? Can you please change audio on my sheet into graphic novels? I totally forgot to ask you about it at start of year silly me, and I don't do audio when I do ..."

For this year's sheet?... or next year?

[Monika]

this year sheet please :)

[Timelord Iain, Tech Support]

Monika wrote: "this year sheet please :)"

Made the change, and fixed some other things I noticed, formatting, number conversion, hardcoded formulas that should have been pointing to a cell value for easy duplication, etc... just cleaned things up in general... don't think I broke anything, but let me know if you suddenly notice something...

[Monika]

Seems fine thank you Iain!!!

[Nyssa]

Hi Ian, there seems to be an error on my current sheet - Column H; Row 392.

#DIV/o! ~ Function DIVIDE parameter 2 cannot be zero.

[Timelord Iain, Tech Support]

Nyssa wrote: "Hi Ian, there seems to be an error on my current sheet - Column H; Row 392.

#DIV/o! ~ Function DIVIDE parameter 2 cannot be zero."

I could re-write the code/formula to avoid that error, but it doesn't hurt anything, since those are personal monthly stats... divide by 0 stops being an issue, once you read a book each month... if you scroll down, you'll see the same error in the December header for average rating

[Nyssa]

I’ve seen the one for the star rating but this one is for the number of pages read. December shows correctly, it says 0 pgs/day instead of the error.

[Nyssa]

Timelord Iain wrote: "Nyssa wrote: "Hi Ian, there seems to be an error on my current sheet - Column H; Row 392.

#DIV/o! ~ Function DIVIDE parameter 2 cannot be zero."

I could re-write the code/formula to avoid that e..."

Thank you for fixing it! :)

[Timelord Iain, Tech Support]

I think it fixed itself… and I was looking at another column?

[Virginie]

Timelord Iain wrote: "I think it fixed itself… and I was looking at another column?"

It's the "MIN(DAYS(TODAY(),B392)+1,30)" where B392 is the first day of the month that was evaluated as 0 I think. This part of the formula is supposed to calculate the number of days we are in the month. If TODAY() is before the month, the number goes in the negatives. So for one day (the last day of the previous month, October 31 here), DAYS(TODAY(),B392) is equal to -1... Factor in the +1, and you've got a division by 0, hence the error.

It fixed itself since the date changed lol.

That's my best guess in any case, I'm surprised we haven't seen it before since it should last the whole day. Unless there are some weird timezone shenanigans on top of it... Fun :)

[Timelord Iain, Tech Support]

Yea… it’s one of those edge cases I know I could fix, but the gain is minimal, and more code layers to process every time we update something so why bother?

[Virginie]

Yup, agreed. I honestly went to look at it out of curiosity more than anything else haha. I'm a bit fascinated by the spreadsheet's evolution over the years. I was looking at a formula on my own spreadsheet yesterday, wondering how the hell it works as if I didn't write the damn thing xD

[Timelord Iain, Tech Support]

Virginie wrote: "Yup, agreed. I honestly went to look at it out of curiosity more than anything else haha. I'm a bit fascinated by the spreadsheet's evolution over the years. I was looking at a formula on my own sp..."

As I might have mentioned months ago, my biggest contribution was November/December-proofing the whole thing, so now anyone can keep using the sheet until New Years' without damaging the posterity of the official point totals...

Most everything else is just helping people have columns they ask for...

I'm much prouder of the evolution of the FBR Buddy Read Request spreadsheet, and the addition of new tabs that sort requests into a functional BR Tracker for everyone to Read-Only... I tinkered with a 3rd tab to manually include Book Clubs, and then we turned around and immediately asked people to stop making so many Book Clubs and Series threads... they're a relic of the Dark Years when the mods weren't around to make threads all the time, before Yanique and I were recruited...

Figuring out how to limit the Tracker to 30 days ago to the future was the work of a fair bit of googling SQL queries...

[Nyssa]

Timelord Iain wrote: "It's a bit early, but I'll keep it in mind... when I make the 2025 sheet I'll make a new thread, but I'll keep this in mind and other can feel free to mention similar...

Nothing you mentioned will..."

Follow-up question (because I can never leave well enough alone - sorry)

Would it be possible to make the Audio category a drop-down as well, or would the coding for the points make that problematic?

Example (the words would be used in the drop-down):

Audio = 100% audio
Immersive = 50% audio
C/G = Comic/Graphic Novel = whatever percent is set for this category
Book (or Eye Read) = 0% audio

[Virginie]

Nyssa wrote: "Timelord Iain wrote: "It's a bit early, but I'll keep it in mind... when I make the 2025 sheet I'll make a new thread, but I'll keep this in mind and other can feel free to mention similar...

Noth..."

Done :)

EDIT: I added the new options to the point formulas but kept the existing ones. That way, if you want to go back, you can.

EDIT 2: Hum, I just realized that this might have been a request for 2025 and not a "can you please do this now?".... Well. It's done now. Oops

[Timelord Iain, Tech Support]

YAY… Virginie’s back to take work off my shoulders ;)

[Choko]

Just slopped by to thank the guys/girls who started and keep going this wonderful Team Reading Competition and Spreadsheet! This has made BRs so much more fun and manageable!!! You guys are awesome and we are truly grateful for all the time and work you put into making our group experience so awesome 👍👍👍!

[Virginie]

Choko wrote: "Just slopped by to thank the guys/girls who started and keep going this wonderful Team Reading Competition and Spreadsheet! This has made BRs so much more fun and manageable!!! You guys are awesome..."

Awww, thanks Choko <3

[Soo]

Yeah FBR tracker is part of the fun! 💖

[Nyssa]

Thank you Virginia!!
It was requested for 2025, because i thought it would be too late in the game to implement it now, but I am happy to start using the changes today!!

And ditto what Choko said!
I’m excited to participate again next year.

[Virginie]

Nyssa wrote: "Thank you Virginia!!
It was requested for 2025, because i thought it would be too late in the game to implement it now, but I am happy to start using the changes today!!"

You're welcome :)
Iain was going to copy your sheet and empty it for next year anyway, so any changes we do now are changes we won't have to do later. Speaking of... Do you want me to turn your BR column to Y/N for this year as well? I didn't do anything since I didn't want to replace the dates by "Y" and lose the information without your approval.

@Iain: I changed the template for Nyssa in the Participants Data tab to "copy Nyssa" since the sheet now has unique features

[Tonari no Emily]

Choko wrote: "Just slopped by to thank the guys/girls who started and keep going this wonderful Team Reading Competition and Spreadsheet! This has made BRs so much more fun and manageable!!! You guys are awesome..."

Ditto this! <3

[Nyssa]

Virginie wrote: "Nyssa wrote: "Thank you Virginia!!
It was requested for 2025 because I thought it would be too late in the game to implement it now, but I am happy to start using the changes today!!"

You're welc..."

#1 I am so sorry for misspelling your name. I was using the app and couldn't edit/correct it.

#2. Yes, Please change the BR column.

#3. Can you please change the Source column to a drop-down list as well?

Please let me know if the names need to be shortened, and please use the first option if you see two sources (i.e., AM/LI = AM = Amazon/Kindle).

Source:
SH = Already Owned
AM = Amazon/Kindle
Audible
AU = Author Site
B&N/Bookstore
Hoopla
KU = Kindle Unlimited
LI = Libby/Library


Thank you, thank you, thank you!

[Virginie]

Nyssa wrote: "#1 I am so sorry for misspelling your name. I was using the app and couldn't edit/correct it."

No worries haha. My own phone would autocorrect my name to Virginia when I first got it x)

Nyssa wrote: "#2. Yes, Please change the BR column."

Done :)

Nyssa wrote: "#3. Can you please change the Source column to a drop-down list as well?"

Done as well. I resized the column to accommodate the data.

[Nyssa]

Virginie wrote: "Nyssa wrote: "#1 I am so sorry for misspelling your name. I was using the app and couldn't edit/correct it."

No worries haha. My own phone would autocorrect my name to Virginia when I first got it..."

OMG! Thank you so much!! It's wonderful!!

Can I change a category's name or add a category later, or would I have to ask for help?

Edit: I figured out how to add categories but do not know how to edit existing ones. Trying to do so causes an error.

I want to remove Audible from the Amazon/Kindle category. It was meant to be separate.
I would also like to change the "Books" category to "Paper."

[Virginie]

Yes, you can do both. At the bottom of the dropdown, you have a little pencil icon on the right to edit. Once you've made your changes, a popup will appear to ask if the changes should be applied to this instance or all instance. Be sure to select all instances.

EDIT: tell me if it's not clear, I'll try to add screenshots to show you in that case

[Nyssa]

Virginie wrote: "Yes, you can do both. At the bottom of the dropdown, you have a little pencil icon on the right to edit. Once you've made your changes, a popup will appear to ask if the changes should be applied t..."

When I try to change the name of a category, I get the following error:

Invalid: Input must be an item on the specified list

Edit: Wait, the error disappeared when I chose the new portion from the list! Thank you so much! :)

[Virginie]

Yes, the way it works, you get a warning if you enter a value other than one in the list. Since you changed the list, all the cells with the previous values must have given you warnings. You have to manually change the values to make it match.

[Virginie]

I did an emergency fix for your audio column. You changed "Book" by "Paper" on some books, and it provoked errors since the points formula was written for "Book". Changed it to "Paper" for now.

[Virginie]

Ah, I just saw your edits. My bad for the Amazon/Kindle and Audible categories. I probably did my copy paste a bit too fast :p

[Nyssa]

Virginie wrote: "Yes, the way it works, you get a warning if you enter a value other than one in the list. Since you changed the list, all the cells with the previous values must have given you warnings. You have t..."

Virginie wrote: "I did an emergency fix for your audio column. You changed "Book" by "Paper" on some books, and it provoked errors since the points formula was written for "Book". Changed it to "Paper" for now."

Ah. Okay. Yes, I tried editing "paper" to "visual" (to see if I could), and it messed up the points.

Virginie wrote: "Ah, I just saw your edits. My bad for the Amazon/Kindle and Audible categories. I probably did my copy paste a bit too fast :p"

Thank you so much for working with me on this. I appreciate it.

[Virginie]

You're very welcome :)
Don't hesitate to tell me if you want the categories in Audio changed (since those are linked to the points formulas).

Alternatively, I could probably create a table where you pre-enter terms like you did and associate a %audio to it, and modify the formula to take it into account...? That's probably not worth it right now, but it's an option if you want to add categories with a different %audio in the future.

[Nyssa]

Virginie wrote: "You're very welcome :)
Don't hesitate to tell me if you want the categories in Audio changed (since those are linked to the points formulas).

Alternatively, I could probably create a table where y..."

Thank you again. The only change I would like to make in the audio category is to switch from "Paper" to "Visual" since I read both paper and e-books. I don't know why I didn't think of it earlier.

Everything else in that column looks excellent!

[Virginie]

Done :)

I couldn't settle on a word I liked for my personal spreadsheet (the one I copy the data in once the year is over to have several year in 1 place), and decided to use emojis instead: 👂🏼 vs 👁️
It looks kinda weird but I like it lol

[Nyssa]

Virginie wrote: "Done :)

I couldn't settle on a word I liked for my personal spreadsheet (the one I copy the data in once the year is over to have several year in 1 place), and decided to use emojis instead: 👂🏼 vs..."

Thank you, thank you, thank you!

[Timelord Iain, Tech Support]

I do call them eyebooks and earbooks, to differentiate all the possible eyebook types into one word... but that's mostly for discussion purposes on here...

Glad you guys got everything sorted...

My mental headspace is limited these days... my knee(s) have been bothering me for a month now... finally went to the doctor last week and had some xrays... didn't find anything seriously wrong... gotta wait a week or 2 for orthopedic followup and my coincidental yearly checkup around the same time... in the meantime, I suffer, take pain releavers... Rest, Ice, Compress, Elevate... and sleep alot because bed is comfortable-ish...

[Nyssa]

Timelord Iain wrote: "I do call them eyebooks and earbooks, to differentiate all the possible eyebook types into one word... but that's mostly for discussion purposes on here...

Glad you guys got everything sorted...

..."

I am so sorry you've been hurting, Ian. I wish you the best of luck with your follow-ups and a complete and speedy recovery.

[Virginie]

Timelord Iain wrote: "I do call them eyebooks and earbooks, to differentiate all the possible eyebook types into one word... but that's mostly for discussion purposes on here...

Glad you guys got everything sorted...

..."

Well, I did leave you to do everything for years, so it's only fair I try to take some "work" off your shoulders when I'm around :)

I hope you feel better soon. Rest well!

[Choko]

Iain, I feel your pain! My knees hurt all the time, but I am too heavy for the knee replacements I desperately need and am having a hard time losing weight to get them done...