Fantasy Buddy Reads discussion

Another year, another spreadsheet...

The teams have been announced, names have been changed, and I'm finally ready to unveil the 2023 spreadsheet, 7hrs from midnight in my time zone...

https://docs.google.com/spreadsheets/...

As with years past, I leave coloring to you guys, but if you have column/formula/etc requests, send them here and I'll accommodate, as best I can, in the coming weeks...

Old sheets for inspiration:

2022: https://docs.google.com/spreadsheets/...
2021: https://docs.google.com/spreadsheets/...
2020: https://docs.google.com/spreadsheets/...
2019: https://docs.google.com/spreadsheets/...

I tried team coloring sheets for some people that never customized, but gave up as some things were pasting and some weren't... in the end, the only person I recolored was Chris...

In the future, I might just delete default sheet people and make them a new sheet from template, of their new team color... might be easier, even if I have to recode gid links in the Participant Data, real quick, at the end... only about half the participants have default sheets, I think... everyone else customizes to some extent...

@Diana: I tried copying your colors from 2019, and gave up as well... the problem here was we've added data and streamlined formulas in the last 4 years, and copying your old preferences was breaking too much stuff... so I just gave you a new default sheet...

Overall, working on the sheet today made me appreciate, again, how awesome our sheet is... changing team names was super easy... most team name cells are just copied from the first 4 cells with rankings, on the main page... the only other time team names need to be input, is for team member assignments on Participant Data...

Thank you for all your hard work!

Thanks Iain! No worries. I'm happy to customize myself based on the current format :)

Diana Stormblessed wrote: "Thanks Iain! No worries. I'm happy to customize myself based on the current format :)"

The main changes, the last couple years, was the addition of November/December to the sheet, for people that want to use the sheet after the competition ends, and formula changes to ignore November/December in any formula sent to the Participant Data tab... also, to cut down on formula repetition, alot of the top row formulas just add together data from the monthly section now, instead of checking the rest of the sheet... this is how it knows to stop in October, or include November/December for fun, in the extra November/December row, near the top...

The rest, is just changes to make my life easier each year, I think... more data input once, and copied to all sheets, like the current year cell, and which team you're on... I input that stuff in Participant Data, and as long as your name matches a name in the list, everything gets passed back and forth...

Thanks for your work on this Iain! Much appreciated.

Thank you for getting everything set up, Iain!

No problem...

Made some changes for Monika, asked for in team thread... I think that was the only request I had from a few weeks ago, that she reminded me about, this morning... unless I'm forgetting another...

Iain I think we all are allowed to have 'blond moments' :) plus I as hardly IT enthusiast am just total pain for you and other guys knowing how to deal with them spreadsheet :) Have a great Sunday!

Thanks Iain. I have one question about mine. The column after buddy read usually is listed as challenge but is blank now. I can fill the title in myself, but just wanted to make sure it's still functioning as the challenge column?

Tammie wrote: "Thanks Iain. I have one question about mine. The column after buddy read usually is listed as challenge but is blank now. I can fill the title in myself, but just wanted to make sure it's still fun..."

Everything looks fine... not sure what's going on there... not sure if there's oddities going on when I duplicated last year's sheet, or my keyboard triggered... I found a number of sheets where the participant name was replaced with "S" for some reason...

The challenge point formula is further to the right, in the points section, so everything should still work... I re-input the Challenge? header...

Timelord Iain wrote: "Tammie wrote: "Thanks Iain. I have one question about mine. The column after buddy read usually is listed as challenge but is blank now. I can fill the title in myself, but just wanted to make sure..."

OK thanks!

@Iain. Can I get my same set up as last year? or like Choko has??? No rush, just looks different than last year and like the breakdown of novellas, tombs, etc and number of books per month.

Again,, no rush and thank you!!

Could I also get my same set up as last year? No rush for me either and thank you so much!

Everyone has the sheet they had last year... the Monthly section is just collapsable to save space... there's a +/- button on the left side of the sheet to expand/collapse the rows... I went in and opened them on both your sheets, while I was checking to make sure that was all that was wrong (since there have been other oddities this year)...

Timelord Iain wrote: "Everyone has the sheet they had last year... the Monthly section is just collapsable to save space... there's a +/- button on the left side of the sheet to expand/collapse the rows... I went in and..."

Ah, sorry, I should have checked that. Thank you!

Thank you Iain! Excited to start filling in new rows...

Thanks, Iain, for the new spreadsheet. Can't wait to add some books.

Thank you Iain for all of your hard work on these spread sheets!!

Could I make a request for mine? To follow the coding for the 2022 build of my sheet? It just added some cool monthly stats and grids at the top. Let me know if this could work. Thanks much! :D

Editing my below comment. I copied it over myself and think it's fine. Hopefully it still works fine.

Iain, sorry to be a pain, but is it possible for me to copy Jenna's spreadsheet? I was going to try to update it myself but i don't want to screw anything up. I want her colors and her audiobook vs written section :-) So basically copy the whole thing.

For some reason my sheet isn't adding my total points up. Sorry, I know you are busy. Just get to it whenever you can.

Tammie wrote: "For some reason my sheet isn't adding my total points up. Sorry, I know you are busy. Just get to it whenever you can."

OK... that one was super weird... but you had a similar problem last year... for some reason, all the formulas started working on row 11 instead of row 5... not sure how that happened... last year it was row 6, and you just didn't use row 5... fixed both years now, for posterity...

Timelord Iain wrote: "Tammie wrote: "For some reason my sheet isn't adding my total points up. Sorry, I know you are busy. Just get to it whenever you can."

OK... that one was super weird... but you had a similar probl..."

I had a similar weird issue with row 83 last year... I skipped it and things were fine after so I never bothered to bug our spreadsheet guru... I thought maybe I had messed things up by trying to copy-paste things around?

I think the problem usually happens when you add rows to the sheet... added rows don't tend to get formulas, especially formulas in hidden rows/columns... and if you add rows above the first row in a formula (in this case row 5, it's excluded, and the formula just moves down below the new rows... I think...

That's my best guess, anyway, and why it's generally better to add rows in the middle of the sheet and drag them where needed... and then you need to expose the hidden column and drag the formulas to make everything work 100% again... (that's usually where I come in)...

The best way to copy formulas is to highlight 2-3 rows, then use the enlarged bottom right corner to drag the cells/formula down... that way, the sheet knows how the formula adjusts cell numbers in each row (and it can copy alternating background colors, if needed)...

Thank you! I thought it was similar to last year, but when it didn't work to just skip row 5 I wasn't sure, lol.

Timelord Iain wrote: "I think the problem usually happens when you add rows to the sheet... added rows don't tend to get formulas, especially formulas in hidden rows/columns... and if you add rows above the first row in..."

That's good to know Iain--I think I totally did add rows and cause problems last year... once I realized I had messed things up, I stopped adding in books early, and only typed them in once I'd started reading or was about to... saved moving things around.

Cell B2 on my sheet is showing a reference error and I tried to play with it but have no idea what's wrong :-(

Probably something I need to look at…

Somehow, you were missing a formula in hidden column O3... I'll need to look into that, since I copied your sheet from the template, and I don't remember that being an issue before...

The formula figures out which cell to grab the points difference from on main page, through some hidden spreadsheet magic, then that cell row can be grabbed by the formula in B2 using INDIRECT cell linking)...

thank you! I missed that there was a hidden column

Diana Stormblessed wrote: "thank you! I missed that there was a hidden column"

hidden columns have little arrows to unhide... you probably see them, now that I pointed out the location to look... you might need to hover on the adjoining visible columns...

So... something weird happened on my spreadsheet between yesterday and today--yesterday all the points at top looked like normal, but today... it only says #N/A all across the top, and I have no idea what happened or what I did...

You changed your Player Name to your Team Name...

Timelord Iain wrote: "You changed your Player Name to your Team Name..."

No idea how I did that... must've had the cursor in the wrong spot when I was typing. >.< Will try to be more careful. Thank you!

Pretty much the only reasonable way it happened, is you accidentally dragged the cell and copied the date from B to A...

Timelord Iain wrote: "Pretty much the only reasonable way it happened, is you accidentally dragged the cell and copied the date from B to A..."

wow, I have no idea how I managed to accidentally do that...

Not sure how possible this is. On my sheet I have the tracker that separates ebook-physical-audio. My only book so far is counting as audio since I listened to 15% of it. Is there a way to make it only count as audio if I listen to more than 50% and have this one count as physical? Just so I can better see at the end of the year how much I listened vs physically read.

Elizabeth wrote: "Not sure how possible this is. On my sheet I have the tracker that separates ebook-physical-audio. My only book so far is counting as audio since I listened to 15% of it. Is there a way to make it ..."

Changed helper formulas... Audio is column G>50, Ebook is column A checked and column G<=50, Paperback is total books (H3) minus ebooks and audiobooks (hidden cell results)... tell me if number seem off in the future...

Thanks!

Elizabeth wrote: "Not sure how possible this is. On my sheet I have the tracker that separates ebook-physical-audio. My only book so far is counting as audio since I listened to 15% of it. Is there a way to make it ..."

Oooh, that's a nice idea. I'm not that complicated, but...

Oh Master of Spreadsheets, would it be possible to have the audiobook column changed to a format column, and from recognizing Y/N to recognizing A, H, L for audiobook, and P or E for eye reading book?

Nirkatze wrote: "Elizabeth wrote: "Not sure how possible this is. On my sheet I have the tracker that separates ebook-physical-audio. My only book so far is counting as audio since I listened to 15% of it. Is there..."

You still want to use it to process points for audio vs eye books, you just want to change the inputs from Y/N to Audible/Amazon, Hoopla, Libby & Papaerback, Ebook?... just curious, what about Tress of the Emerald Sea only being available on Spotify/Speechify?

Timelord Iain wrote: "Nirkatze wrote: "Elizabeth wrote: "Not sure how possible this is. On my sheet I have the tracker that separates ebook-physical-audio. My only book so far is counting as audio since I listened to 15..."

Yes--rather A for audiobook--that means an audiobook I own somewhere, on any of the platforms (have my own spreadsheet keeping track of those), e for ebook, p for physical copy, and h/l for hoopla & libby. I pretty much only borrow audiobooks from them, and if I do borrow an ebook I'll probably buy it eventually anyway.

I currently have audiobooks on Audible, Chirp, Spotify, and Kobo.

Nirkatze wrote: "Timelord Iain wrote: "Nirkatze wrote: "Elizabeth wrote: "Not sure how possible this is. On my sheet I have the tracker that separates ebook-physical-audio. My only book so far is counting as audio ..."

I think I implemented it... for now, instead of removing the Y/N and 0-100% functionality, it counts both... I can remove Y/N/% later if you want, once you convert your data to the new tracking system and are sure you don't need the old (this will remove some excessive calculations in a few cases... I need to figure out if there's an easier formula for checking for 3-5 possible answers to a question, rather than checking each thing separate and adding together with + operator)...

I also updated your monthly section with the new formula and appended " audio" as a label using Number Formatting (this way the text doesn't count as text, the cell just holds the number and can be added together with math operators still... this might have been overkill, but I don't remember if this column is counted anywhere else in the sheets, and it's better safe than sorry)...

" audio" was needed, because the column didn't make much sense, in the monthly section, with the new Format header...

Old Formulas:

=IFS(G17="y",E17*0.0025,F17="y",E17*0.01,ISNUMBER(F17),E17*0.0001*(F17+(100-F17)*1.25),(F17="n")+(F17=""),E17*0.0125)

=COUNTIFS(O$17:O,O5,F$17:F,"y")+COUNTIFS(O$17:O,O5,F$17:F,">0")

New Formulas:

=IFS(G17="y",E17*0.0025,(F17="y")+(F17="a")+(F17="h")+(F17="l"),E17*0.01,ISNUMBER(F17),E17*0.0001*(F17+(100-F17)*1.25),(F17="n")+(F17="e")+(F17="p")+(F17=""),E17*0.0125)

=COUNTIFS(O$17:O,O5,F$17:F,"y")+COUNTIFS(O$17:O,O5,F$17:F,"a")+COUNTIFS(O$17:O,O5,F$17:F,"h")+COUNTIFS(O$17:O,O5,F$17:F,"l")+COUNTIFS(O$17:O,O5,F$17:F,">0")

Let me know if anything seems like it's giving less points than it should, or something...

Thank you Iain! I just changed all of the format letters. I'm fine with keeping Y/N too. I think the numbers are fine, but I know nothing about spreadsheets, so I'm not following any of the formula stuff you mentioned...maybe someday...

Also not sure what this meant:
I also updated your monthly section with the new formula and appended " audio" as a laber using Number Formatting (this way the text doesn't count as text, the cell just holds the number and can be added together with math operators still... this might have been overkill, but I don't remember if this column is counted anywhere else in the sheets, and it's better safe than sorry)...

But if it's not something I need to know then I'm ok.

Nirkatze wrote: "Thank you Iain! I just changed all of the format letters. I'm fine with keeping Y/N too. I think the numbers are fine, but I know nothing about spreadsheets, so I'm not following any of the formula..."

I meant more if you felt like you weren't getting points you should, I can doublecheck I didn't screw something up... not to check my formulas :D

The label stuff is a bit of a yes/no thing, on need to know... just overexplained a change I made here:





The cells append " audio" to the number returned by the formula, for display purposes only, so the cells can still be used in graph/sum formulas... before I got savvy, I'd append the text in the formula, then be unable to reuse the data without stripping the text back off...

" hrs" is used in my personal sheet, in the audiobook duration column (since I changed my sheet to track that, instead of percentage audio vs ebook/etc)...

Oh yes of course, there's no way I could check your formulas, lol! But I haven't been paying enough attention to really tell if I'm getting the points I should either. >.<

All this spreadsheet stuff is very interesting... and very over my head!

It is very interesting looking at your spreadsheet, and seeing all the bells and whistles...

Most functionality now seen on all sheets, started off as bells and whistles on my sheet or Virginie's sheet (no longer around, sadly)...

Timelord Iain wrote: "Most functionality now seen on all sheets, started off as bells and whistles on my sheet or Virginie's sheet (no longer around, sadly)..."

LOL of course. People look at your sheet and are like--ooh, I want that!

Do you have any way for people to plug in length of an audiobook (ex: 21:46) and have it convert it into pages? I'm so bad at math, and sometimes the given pages for the ebook or paperback just don't look right...

Was thinking it would be nice to replace the Graphic Novel column with an audiobook hours column, and have it dump the pages into the pages column... maybe could add a G or M in the Format column for Graphic Novel or Manga?