Fantasy Buddy Reads discussion
Archive [General]
>
2024 Team Competition Tracker / Google Sheet Central
Yeah sure, no complaints here. Spreadsheet twins! I finally got around to fixing the bar graphs/percentages on my 2023 sheet to accurately include the November and December months, but I don't think those changes got copied, so let me tweak that first. I think that's the only buggy thing I put off fixing, but my apologies if you run into others. ^_^;;
Okay, that should be better. Also if you use any of the recordkeeping right of the point totals, I'm sorry for all the cumbersome check boxes. I basically just copy/pasted the reread formulas when I set it up and didn't want to change too much, since I'm not a spreadsheet wizard like Iain. I've been meaning to try to convert them to a simple y/n format, but that would mean... figuring out how to do that. (^_^;;)
No worries at all Mel!!! I'm grateful you're letting me be spreadsheet twins with you in the first place. 🥰 As a side note, I too use checkmarks on my personal spreadsheet since i never figured out how to do y/n either 🤣
For some reason my total book points isn't changing on my spread sheet. The row next to each book I've marked as finished has the book points but the total up above is staying the same.
Mod
I swear this happens to you every year... are you adding rows to the top of the book data section?... I think when that happens, it ends up in a dead zone and isn't included in the top formulas... I had to change all the top formulas to start at row 5 instead of row 7...
Mod
Deleted your old sheet and moved you to the Mel clone... you lost your ratings column, gained all the other stuff... I stuck your existing ratings in the publication column, when I was moving all your book data, but that isn't really a solution unless you want me to start changing things...
Timelord Iain wrote: "Mei ☽︎ wrote: "No worries at all Mel!!! I'm grateful you're letting me be spreadsheet twins with you in the first place. 🥰 As a side note, I too use checkmarks on my personal spreadsheet since i ne..."No problem, Iain!! I can forego the ratings column. 😊 Thanks so much for the help!!!
Mod
I mean... if you don't need anything special, you can always just add ratings on the very far right... the only formula I had for it was monthly average...
Personally, it's a tool I created because I fall days/weeks/months behind on ratings/reviews... so, at least if I remember nothing else about the book and why I felt the way I did, I still know what I think it's worth rating, when I go to catch up...
It does seem to happen every year, lol. I didn't add any rows but I dragged two of them up to the top this time.
Thanks for fixing it again this year.
Timelord Iain wrote: "I mean... if you don't need anything special, you can always just add ratings on the very far right... the only formula I had for it was monthly average...That's a good point!!! I ended up doing that. Thanks again! 😂
Ooh, good point, I may end up doing that myself. It's sort of just an odds and ends notes space now. Although...Iain, is there any way that I could cannibalize the audio % column to become a ratings column? I never do audio, so it's pretty superfluous for me, but I don't want to mess with any points calculations.
Mei, I'm also very very tempted to copy you and change my Death Counter to a Spice Meter. 🌶️😂 Like, I find the death count amusing, but the spice meter could be... useful.
Mod
Iain, is there any way that I could cannibalize the audio % column to become a rati..."
@Mei: For a spice meter, you should probably change the monthly summary from SUMIF to AVERAGEIF to determine the average monthly spice vs the total monthly deathcount before...
@Mel: the repurpose column J, we just need to remove all references of it from the column O points formulas...
OLD:
=IFS(K18="y",F18*0.0025,J18="y",F18*0.01,ISNUMBER(J18),F18*0.0001*(J18+(100-J18)*1.25),(J18="n")+(J18=""),F18*0.0125)
NEW:
=IFS(K18="y",F18*0.0025,(K18="n")+(K18=""),F18*0.0125)
That strips out all references to audiobooks, and shifts the last chunk to just check the GN? column instead when giving full ebook/eyebook points...
Change O18 to that, then drag the corner to copy the formula all the way down...
Then we just need to repurpose the monthly section of J to be something relevant to the new data, like the monthly ratings average in my sheet...
I can make the column O change, or you can do what I said above...
Iain, do you have any advice for a resource or place to start learning about all this spreadsheet magic? Those formulas look like alien 5th dimensional space language to me, but I'd love to learn.
Mod
That's what I do, when I run into something I don't know off the top of my head... granted, I have some pre-knowledge from college...
Mel wrote: "Mei, I'm also very very tempted to copy you and change my Death Counter to a Spice Meter. 🌶️😂 Like, I find the death count amusing, but the spice meter could be... useful."YESSS Please add a spice meter with me!!🤣
Timelord Iain wrote: @Mei: For a spice meter, you should probably change the monthly summary from SUMIF to AVERAGEIF to determine the average monthly spice vs the total monthly deathcount before...
Oh that's perfect, thanks Iain!!! 😊
I have an error message on my sheet, at the tom in column B under team points it says '#REF!' and this message keeps popping up 'ErrorFunction INDIRECT parameter 1 value is ''Main Page'!C'. It is not a valid cell/range reference.'
Thank you!
Mod
Function INDIRECT parameter 1 value is ''Main Page'!C'. It is ..."
Somehow you lost a formula... Indirect grabs data from a cell to use in another formula... in this case, O3 determines your team data cell in Main Page, based on your team name in A2... and O3 was blank, despite being a hidden cell you'd be unlikely to mess with...
Weird... all sorted now, tho...
Question about DNFs - do we add them with the # of pages read or just leave them off entirely? Apologies if this was asked/answered already....
Mod
Timelord Iain wrote: "# of pages read"That's what I figured, thanks!
@Iain I grouped some cells in error and I tried to see how to ungroup them but nothing is working so can you please assist when you get a moment? Please and thank you :)
Mod
Nothing immediately stands out to me... what cells need fixed?...
Timelord Iain wrote: "Ann-Marie wrote: "@Iain I grouped some cells in error and I tried to see how to ungroup them but nothing is working so can you please assist when you get a moment? Please and thank you :)"Nothing..."
In the light of day nothing looks messed up, please disregard. Sorry!
Is grouping rows and adding monthly stats counters there safe to do by myself or does it need some bigger adjustment to the way things are counted?
@Iain: chatting with Marie directly on the spreadsheet about it.
Ok, we figured things out :)@Iain: Marie wants to try out your template, could you arrange that? She's like to have the "classic" challenge column, not the modified one for series tracking (so I guess the little graph thingy in the monthly stat section would have to be modified too).
Mod
I duplicated my sheet... copied her data over... modified the points formulas to support Graphics Novels... changed the Challenge column to support her challenges (more can be added in column V near the top, to add to the dropdown menu...
Changed how the Audio(hrs) column works to support graphic novels and a bunch of other alphabet letters, similar to Nirkatze's formula... but it still treats all numbers as audiobook hours, and not the % of other sheets... if Marie wants that, tell me, and I'll have to remove more of my hour tracking stuff... (not really sure how well the hours stuff will work when also tracking graphic novels, anyway)...
Haven't fiddled with replacing the graph at the top, yet...
My sheet is/was also setup to input BR start dates in the BR column, as a way to track my BR plans, instead of yes/no... if a date is included in the BR column, it gives BR points (doesn't compare dates for the 2 week rule or anything, since that wouldn't work anyway, since you don't have to finish in 2 weeks only start)... if you want all the data validation removed for dates instead of y/n, that's alot more editing I'll need to do at some point...
This is all I'm gonna do for now, without some more input... I deleted Marie's old sheet and replaced it with this new one... points match, etc...
Thanks, Ian. That sounds like a lot of work, sorry for that. I will see tm what you are talking about when I have the sheet in front of me.
Mod
I feel like I should make a more copy-friendly copy of my sheet... alot of it has been customized for my own specific needs/wants/desires/reading habits... which doesn't entirely match other people's...
There's also the changes google has made to sheets that make changing stuff more annoying...
Alright, I took care of the graph at the top.Thanks for doing all that Iain (and sorry for dropping it in your lap, but you know your sheet better than I do :/)
Timelord Iain wrote: "I feel like I should make a more copy-friendly copy of my sheet... alot of it has been customized for my own specific needs/wants/desires/reading habits... which doesn't entirely match other people's..."Well... Depending on the final product, Marie's sheet might end up that the copy-friendly version :)
I feel you on the highly customized sheet. Mine is a pretty weird too, with the filter on top for read books and the automated hooks for mangas and short stories :p
(And the graph zone that is just a sad sad empty space for now... I really need to take the time to create the graphs :p)
Marie wrote: "Thanks, Ian. That sounds like a lot of work, sorry for that. I will see tm what you are talking about when I have the sheet in front of me."Basically, there are two columns left that you might want to modify since the current ones are customized for Iain's needs.
Format? (hrs) column
This column combines the Audio and GN (Graphic Novels) other sheets have. On those sheets, they enter Y/N/[number] on the Audio one (the number is the percentage of the book you have listened to, if you're doing both audio and eyereading on one book), and Y/N on the graphic novels column.
On your sheet, you can enter:
- "a" for audiobook
- "g" for graphic novels/mangas
- "e" for eyebook
- [number] for audiobook hours (is treated as if you entered "a", used only for Iain's purposes)
Depending on your reading habits, we can modify it.... If you need separated Audio and GN columns (so basically, if you're doing graphic audios or just prefer it that way), I'll just do the normal setup. If you read books with a mix of audio and print, then we'll replace the audiobooks hours thing with the % one (like explained above). We can also add a dropdown menu so you don't have to manually enter the a/g/e but select the correct option instead. Your choice :)
BR? column
Normally a Y/N column. Iain basically made it a date column to use his sheet as his BR tracker. The point calculation isn't a smart thing using the 1 week before/ 2 weeks after rule. As long as your enter a date, you get the BR points.
The only question here is do you want to keep that way (so with the date thingy) or do you prefer to return to the Y/N format ?
@Iain: now that I've looked at it, I could probably make the modifications myself. Cool stuff on the format column, with the audio hours. Are you tracking the total lenght of the audiobook or how long you took to listen to it (so after the crazy speed) ?
EDIT: I know I'm nerding out a little too hard when I'm using bold headers for organize my post....
Not the the fairytale category for a memoir about child abuse and family pretending nothing happened... I am sorry I know you couldn't have known but that's just so wrong 🤣I am fine with the column being for both hours and types. I actually made a type column in my Excel sheet instead of the separate check. I am strict with doing either eyebook or audio.
(I actually got inspired to delve into some functions on my Excel sheet and did an IFS function for counting the points based on the type column. Yes, in retrospect it's simple. Yes, I am still proud of myself lol. Basically, I just wanted to see points in real-time, that includes unfinished books, and detailed daily average so I went through all the pain of adding those round ups to end up with the same points.)
As for the BR. I don't plan to put my planning for each month out as I rarely complete it. I like to put all the books once I read them. I am considering putting the plans on the designated rows at the bottom. I am thinking I could actually turn the unneeded source column into a book start column and make the smart check. *Searches how to do it.* Yeah, shouldn't be too hard for me to figure out. I promise I will only mess with my BR points column and return it back if I fail lol.
Mod
The Source Column of my sheet could also be reworked... that used to be a column I used to track if you could get the books on Hoopla / Audible+/Kindle Unlimited, but I slightly reworked it to just track where I have the book in general (borrow, iTunes, Audiobooks.com, BookFunnel, etc)... so many audiobook sources I've used over the years, and some it's just better to download a file and store in iTunes than use their own app with limited listening speeds...
EDIT: I see you have plans, I'll leave you to it, for now...
I made it work right with the dates but I still need to make it so that it won't put points when the BR date cell is empty. Put it back to the original for now since I will only be back later to work on it.
Mod
I know the formula I just have to see how to mash it together. I just had no time to finish it.
Not that I am not thankful for the advice, I just like to figure out these things myself. Never was person to ask for help before trying so long I have to admit defeat xD And ofc I don't want to experiment too much just to leave you with a mess to fix after...
Mod
Nvm, I decided I don't like the three dates look. Also have issue with it when it comes to series novellas not being started on the correct date as they often come after the main book in the schedule but share the join by date with the main book.Think you could make the BR the default Y/N check? That should be the last thing I will bother you with.
Marie wrote: "Think you could make the BR the default Y/N check?"Done. Anything other than "y" or "Y" will not trigger the BR points.
Mod
So for the weekly BRs that generally happen on Mondays, I count the BR start date for short stories to be friday... or for monthlies it's 2 weeks after the book... that's how I schedule it in my tracker anyway...
I see, for me it makes sense that since I started the main book on time I would just pick up the novella as soon as I am done. Basically counting it anywhere between, to not have any deadlines for the main book besides the join by date of the next one.
Mod
Us non-techy people truly appreciate this ❤️
Even outside of competition purposes, I don't know what I would do without this spreadsheet at this point.
I wasn't super convinced about joining the competition until I saw that spreadsheet... Filling out those first blank rows was so satisfying. I mean I picked up manga again just to have something to put in sooner lol. I wish I could fill out the whole year... Well next year it is. Thank you, tech team for feeding my statistics addiction.
Marie wrote: "I wish I could fill out the whole year... Well next year it is."I mean... You could fill out books you read before joining the competition and force the points at 0 for those books by removing the formulas. I'll remember to put back the formulas for next year if you join again, since I already did that for my own sheet :)
Fantasy Buddy Reads
Books mentioned in this topic
Spy x Family, Vol. 1 (other topics)Spy x Family, Vol. 1 (other topics)
If that's okay with you and Mel, and also is easier for you, I can definitely work with that! 😆