Fantasy Buddy Reads discussion

Sha wrote: "thanks for the percentage counters every day i feel like i'm falling more in love with this spreadsheet :3"

You're welcome!

Thanks virginie :D

I have a question about my data. Why is almost everything at zero? I've read three books, but my buddy points, pages read, challenge points, etc. are all at zero.

Tammie wrote: "I have a question about my data. Why is almost everything at zero? I've read three books, but my buddy points, pages read, challenge points, etc. are all at zero."

Looks like you found a bug in the basic sheet... the BR/Challenge totals were using Column O, a column we removed formulas from in the basic sheet, when we got rid of the monthly data at the top, to cut down on calculations...

I went in and fixed that on your sheet and about a dozen other sheets and the Templates, so future additions won't have the problem... I need to look at the switch some more to solve the last cell... not sure if the problem is on your sheet or the Participant Data sheet, yet...

Found the last of it... your pages formula wasn't starting until row 18, again because of the monthly stuff... adjusted that to include rows 4-17, and everything works now... now I gotta copy that a dozen times and everything should be sorted on everyone's sheets...

EDIT: Somehow your sheet was the only one with the Pages bug...

Question for the Spreadsheet Wizards:
If I try to insert a new row, I find that only some of the formulas are updated. For example, total pages read at the top (Column I) will update, but points (Columns K-N) will remain blank.

On one hand, I like the idea of inputting upcoming books into my sheet ahead of time, and then writing the date as I finish. It's easier, and gives that little extra motivation to see potential points. But I also like keeping everything chronological, so if I do a spontaneous read, it would be nice to be able to insert a row, rather than delete and rewrite.

Not a big deal if it's tricky. I can also input as I go. Or simply put up with the other side effect: Getting Asian Kung-fu Generation's "Rewrite" stuck in my head, haha!
♬消して！リライトして！♬

Thank you for fixing my sloppy work, Iain! lol
I noticed the Pages bug a few days ago, and fixed it for everyone with the simplified template (or so I thought). Apparently missed Tammie's :/

I checked and double-checked the monthly template so much that I completely forgot to take care of the simplified one. For a template with so little formulas, it sure had a lot of issues xD

Melissa: it's because the points formulas for this particular row you created are not copied, as well as the hidden formulas for the monthly stats.

One solution would be to copy an entire working row and paste it over your newly created one before filling the book information. To select the entire row, you have to click on the row number on the left. A selected row will have its number 'dark':



Another solution would be to 'slide' rows around. Select the entire row you want to move (like I explained above). If you hover your cursor above the row number when the row is selected, you'll notice it transform into a 'grabby hand'. So click-again-and-maintain on the row number, and you can move it as you want. That's what I'm doing, very practical if you pre-enter your planned books, as you only have to slide up the one you just finished.

Finally, you can use the sorting tool. This one is particularly useful if you have a large number of entry you want to organize in chronological order. First, select the entries you want to sort. You don't have to move the points as the formulas will recalculate, so column A-J. Then, in the menu at the top: Data -> Sort Range. Choose the column by which you want to sort, in your case D, verify A - Z is selected (for chronological) and hit the Sort button.

I use the rearrange and sort methods... you can also fix missing formulas pretty easily... just highlight the point columns and use the slightly bigger square in the bottom right corner to drag/copy formulas down... you tend to need to show the hidden columns for a second to do this, tho...

Timelord Iain wrote: "Found the last of it... your pages formula wasn't starting until row 18, again because of the monthly stuff... adjusted that to include rows 4-17, and everything works now... now I gotta copy that ..."

Thanks for fixing it!

Virginie wrote: "Hey Diana!
It's done, I changed your sheet to the monthly one. If you want additional stats/graphs, just tell me here and I'll implement them as best I can :)"

Thank you!!

Hey, Virginie, wanna help me? :D

I want to rewrite my page points formula, to allow me to use the Audiobook column to track audiobook length. Since I read 99% audiobooks, and don't really do read & listens, we can trim that out if need be, I just want to be able to write 9.25 (and append hrs to the end the way you did with some of your columns, to maintain their status as a number)...

I figure I can do some neat stats with this, and also just tell at a glance which books are more efficient... some 500 page books are 20hrs, and some 700 pages books are 20hrs... most of the shenanigans tend to be the smaller books with abnormally big editions... Song of the Lioness is under 6hrs per book, and listed at close to 300 pages...

Sure. Don't think I don't notice you trying to distract me from reading tough! ;)

I think I did it... Simply rewrote the points formula so that the ISNUMBER condition on the IF triggers a normal audio points calculation (instead of pages in audio*0.01 + pages in print*0.0125 one).
You'll see in the formula but as of now, "y" or number -> pages*0.01, "n" or empty -> pages*0.0125, anything else creates an error.

Changed the number format so that it displays "hrs" after the number. Changed your monthly header formulas to add "y" and ISNUMBER.

Might want to double-check what I did though, as I'm somewhat drunk. Had my traditional monthly "eat-pizzas-drink-wine-talk-about-shit" dinner with my mom lol.

Virginie wrote: "Sure. Don't think I don't notice you trying to distract me from reading tough! ;)

I think I did it... Simply rewrote the points formula so that the ISNUMBER condition on the IF triggers a normal a..."

I think I'm supposed to say YAAS QUEEN, now... is that right Siobhan?

Since we read Jonathan Van Ness's book...

Sounds good, tho... I'll triple check later...

Timelord Iain wrote: "Virginie wrote: "Sure. Don't think I don't notice you trying to distract me from reading tough! ;)

I think I did it... Simply rewrote the points formula so that the ISNUMBER condition on the IF tr..."

Yaassss queen!! 🤣🤣😘

Virginie wrote: "Sure. Don't think I don't notice you trying to distract me from reading tough! ;)

I think I did it... Simply rewrote the points formula so that the ISNUMBER condition on the IF triggers a normal a..."

Looks good to me... I input my audiobook times, and already loving the stat...

I was talking about it in the Jaghut thread, and now Bill would like similar stats... I'd just copy my formula over for him, but it's not that simple, since my formula doesn't have Graphic Novels, and his does...

I figure "we" can give him the audiobook changes, and implement my audiobook hours sumif formula up top in the column that currently just lists # of audiobooks per month...

Thank you so much for the clear explanation, including step by step instructions for different methods, and even a screengrab for the visual. That was... ridiculously well conveyed. As a teacher I salute you.

I'll definitely make use of the sort and slide methods - I feel kind of silly that I didn't even consider dragging and dropping rows around. My knowledge of spreadsheets is essentially set decades ago, watching my father budget on an old Mac. So while I won't be trying to mess with any formulas myself, I'm grateful to you guys for your wizardy on our behalf.

Timelord Iain wrote: “I was talking about it in the Jaghut thread, and now Bill would like similar stats... I'd just copy my formula over for him, but it's not that simple, since my formula doesn't have Graphic Novels, and his does...

I figure "we" can give him the audiobook changes, and implement my audiobook hours sumif formula up top in the column that currently just lists # of audiobooks per month..."

Thanks Iain. Virginie if it is too much work don’t worry about it. I know we are all keeping you pretty busy here.

Timelord Iain wrote: "I was talking about it in the Jaghut thread, and now Bill would like similar stats... I'd just copy my formula over for him, but it's not that simple, since my formula doesn't have Graphic Novels, and his does..."

Sure, will do it.
And yeah, we also moved your columns around a lot. Your scoresheet is like a playground where we experimented on all the formulas we found and all the stats we could think of xD

Alright, changed Bill's page points formulas from:

=IFS(G14="y",E14*0.0025,F14="y",E14*0.01,ISNUMBER(F14),E14*0.0001*(F14+(100-F14)*1.25),(F14="n")+(F14=""),E14*0.0125)

to

=IFS(G14="y",E14*0.0025,(F14="y")+ISNUMBER(F14),E14*0.01,(F14="n")+(F14=""),E14*0.0125)

G is GN column, F audio and E pages. Basically added ISNUMBER to the audio condition, and deleted the following one used for calculating pages audio/print based on %.

Changed the format to make "hrs" appear after the number.

Modified the monthly stats to sum the hours like Iain suggested.

Note: points will be calculated properly whether you put "Y" or a number in the audiobook column, but the monthly stats will only include the books with a number.

Bill wrote: "Thanks Iain. Virginie if it is too much work don’t worry about it. I know we are all keeping you pretty busy here."

It's alright :)
It's only the work of 5 minutes if you know what you are doing (I do). It generally takes me longer to write here and explain (for future me, if she has to do further modifications/solve problems) than actually changing the formulas :p

Melissa wrote: "Thank you so much for the clear explanation, including step by step instructions for different methods, and even a screengrab for the visual. That was... ridiculously well conveyed. As a teacher I ..."

You're welcome.
Hehe, makes me happy that my tutorial writing skills are recognized by a teacher :D

can my book title column be frozen, please? i tried to do it myself but weird things start to happen with the graph.

Ah, that's because graphs can't be in frozen areas...
If you want, I can switch the monthly graphs (which isn't a graph per say, but a sparkline) and your genre graph.

sure :)

Done! Your title column is frozen as well.

thanks. :)

Virginie wrote: "Bill wrote: "Thanks Iain. Virginie if it is too much work don’t worry about it. I know we are all keeping you pretty busy here."

It's alright :)
It's only the work of 5 minutes if you know what yo..."

Thanks Virginie!

Can I have the chart for my challenges too? I want to keep track of how many books I am reading towards a particular challenge just like the genre chart in my sheet...

Done! Left explanations in your scoresheet.

Thanks! :) i liked it.

Can someone please check my short stories/novellas/etc counter? It shows 5 books in all, but it should be showing 9.
Thanks :)

Fixed! Forgot to change the formulas when I re-purposed your GN column into a non-fiction. The short stories/novellas/etc. counter is set to ignore GNs, so it was ignoring books you tagged as non-fiction.

Great, thanks!

my pie chart somehow became invisible i swear i have no idea what happened

it is back! if it was fixed, thank you! if it came back automatically plz ignore the last message.

Haha, it was me! Left you the details in your scoresheet.

12 genres should be quite enough, thank you~

Hey guys, something is up with the spreadsheet. It's not calculating total rank or team points.

Yup. I have the same problem. Issue showed up recently & it's on several people's pages.

Soo wrote: "Yup. I have the same problem. Issue showed up recently & it's on several people's pages."

"You just gotta use the little grey cells." -- Hercule Poirot

In all seriousness, you can follow the problem trail here, quite easily... sounds like multiple sheets have the problem Soo's did a few days ago (I fixed it silently)...

1) Go to Participant Data

2) Figure out which sheet is bugged (usually the Points total column/cell)

3) Go to their sheet and find out which points formula is throwing an error

4) Copy the formula from the row above

...

5) Repeat...

I'm looking into it, now...

Mmmh... My fault. I modified some formulas this afternoon on my scoresheet :p

I had a feeling this one would be rougher, since the source was Virginie's sheet... but she just fixed it... I still have no idea what was wrong... all the point formulas were still working...

Best guess is some weirdness with her special stat graphs down in rows 855+, but I really dunno...

Good work, Gform gurus. =)

Timelord Iain wrote: "I had a feeling this one would be rougher, since the source was Virginie's sheet... but she just fixed it... I still have no idea what was wrong... all the point formulas were still working...

Bes..."

I knew, from the moment I saw the notification for this thread, that the problem was on my own scoresheet hahaha. I was finishing my new formulas, got distracted by a bird (literally, the thing bumped in my window :O) and never went back to check that everything was working properly :p

Hello! can you make Iniya's sheet like mine? She is having laptop issues, so I will be adding books for her for the time being. I am too used to my sheet to look for different column headers in her sheet. Thanks! :)

Done! I literally copied your scoresheet and replaced your book entries with the two that were on hers (and adjusted the Participants Data tab accordingly). She'll have the same graphs, layout, etc.
When she resolves her laptop issues and if she wants to change the graphs & stuff, I'll be happy to help out :)

Random aside: I adore my death counter but more than one of the books I've picked up lately is working really hard to get me to determine exact parameters of death. The last one was set in a world where part of the plot point is death being a sort of revolving door. There were a ton of people dropping like flies in it and I'm not sure if I should count the ones who came back...

Virginie wrote: "Done! I literally copied your scoresheet and replaced your book entries with the two that were on hers (and adjusted the Participants Data tab accordingly). She'll have the same graphs, layout, etc..."

thanks! :D

I just did my first book update - 22 pts. :) My personal goal is to improve on my page count every month by getting more consistent at choosing books over other forms of entertainment. So far so good.