Fantasy Buddy Reads discussion
Archive [General]
>
2019 Team Competition Tracker/Google Sheet Central
date
newest »
newest »
Mod
I didn't bother doing it for the graphic n..."
that seems much easier than the way I did the 2 columns I fixed...
Can someone please take a look at my sheet, I'm not sure if it's adding up all my points. My graph for this month is showing no points even though I've read quite a few books.
Alright, "fixed". Your points were all correctly counted, the problem was with the graph. The SPARKLINE function displays the lowest value as 0 by default; had to fiddle with the options for ymin.Unfortunately, it's not limited to your sheet, Charlie. It's on everyone's sheet :/
EDIT: Nevermind, I fixed it for everyone. Really love the CTRL+F, search & replace thingy :)
Mod
..."
I just thought it couldn't show this month until the month was over... wasn't it like that all year for whatever current month it was?... and is this formula how you fixed that?
My bar chart isn't showing any points for October either if anyone could have a look.
Siobhan wrote: "My bar chart isn't showing any points for October either if anyone could have a look."Works just fine for me. I just updated the entire spreadsheet for every participant in one go like a barbarian, so maybe it'll take some time to refresh on your end :p
Timelord Iain wrote: "I just thought it couldn't show this month until the month was over... wasn't it like that all year for whatever current month it was?... and is this formula how you fixed that?"Nah, it was always updating in real time. The problem was that the graph's default setup is ymin = lowest value, and it only showed points/page/books above that min value. I just changed it to ymin = 0 in the SPARKLINE options:
SPARKLINE(ArrayFormula(SPLIT(C4:C13," Points: ")),{"charttype","column";"ymin",0})
Mod
Ah yea... I thought some of the graphs looked weird...
Virginie wrote: "Alright, "fixed". Your points were all correctly counted, the problem was with the graph. The SPARKLINE function displays the lowest value as 0 by default; had to fiddle with the options for ymin...."
Thank you so much!
Yeah... It wasn't a problem before since the lowest value was 0 (October's score) but since we hit the last month...I didn't notice since I added November & December to my graph :)
Virginie wrote: "Siobhan wrote: "My bar chart isn't showing any points for October either if anyone could have a look."Works just fine for me. I just updated the entire spreadsheet for every participant in one go..."
Sorry Virginie! I had the tab opened before you commented saying you'd fixed it for everyone and didn't refresh!
Mod
Never noticed before, because we weren't 3-4 days from the end... it also doesn't count today, but that makes sense, since once you start inputting data for today it would overestimate...
Fixed! Didn't even use Find & Replace as the predicted pages/points are all calculated in the Participant Data tab, then inputted into the individual scoresheet.
Mod
Sorry! I'm not sure what I messed up but there's a #REF! error message showing in various parts of my tracker. I was super lame and updated my tracker completely tonight. Not sure what I messed up but it probably happened when I copied/pasted something.
Again, very sorry! I didn't mean to break it.
I *think* I fixed it but that's a guess on my part. All of the regular info on the tracker for Book Title/Series/Date Read/Pages/etc is correct. I checked it a few times. The equation stuff that is suppose tally it all up may be messed up on my tracker. It looks like it works the way it suppose to now.
Mod
I was super lame and updated my tracker completely tonight. Not sure what I messed u..."
#REF errors mostly resolve themselves at the top of the sheet... they happen every time someone's point total changes, while the sheet is processing the new data and figuring out ranking changes... this tends to be a minor problem with how much data is in the sheet and gets worse as the year progresses...
Any error in the personal point columns on the right (either existing errors we fixed in other sheets and missed on yours, or just from adding extra rows) tend to be fixable by just highlighting a working set of point formulas near the top of the sheet and dragging/copying them all the way to the bottom...
Point Totals: Figures you'd show up at the 11th Hour and steal first from me, after I spent the last 28 days on a mad dash to steal it from Julia ;)
(I knew you were about 60,000 pages ahead of me for the year, just wasn't sure you were going to update your sheet... I can imagine how long it took, considering my own catchup days, with GR ratings/reviews, and the few times I ran a couple weeks behind on the spreadsheet)
All that being said, you can probably earn yourself another 100-150 points by filling in the challenge point section... for me, tho, that takes a lot longer than filling in the book section, which is why I'm simplifying down to the Series challenge next year, and stopping with the book cover and all the other challenges...
Nah. My (mostly) solo, audiobook marathon earned whatever points I earned. Next year, I'll try to participate in a few more BR's and maybe a cover challenge.
Mod
I've been working on improving my Incomplete Series spreadsheet... simplifying some things, implementing a single sheet that is split into sub-sheets with a QUERY, etc...
And I just found out that you can put a formula in a cell such as =if(H3 <> "Abandoned",(F3-E3)&" book/s", "") which prints out how many books you are behind in a series (for sorting) if it's not an abandoned series (<> means not equal)...
I'm getting sidetracked... you put that formula in a cell, press enter to trigger the formula, highlight the cell, then double click the thicker bottom right corner (that you can use to drag a formula) and it'll automatically copy the formula all the way down the column to the bottom, without having to waste time dragging...
So are the mods going to announce the official winners? :) :) (sorry if I've missed something).
Mod
Not sure what's going on with the Year 3 Draft, either... I think the mods intend to do that again next year, but the logistics of scheduling it is rough...
Mod
=countif(A:A,A3)>1
You use that as a custom formula to prevent duplicates in a column... you can plug is in as data validation or custom formatting to just highlight your mistake. A3 is just the first cell you're formatting/validating, and it changes the value automatically depending on connected cells (my range is A3:A942 here, since the top 2 rows are headers and stats)...
I needed something to quickly check I wasn't creating duplicates in my Incomplete Series spreadsheet, since I have nearly 375 series in the sheet and counting... probably over 425-450 once I update for 2019...
Mod
Mod
Update from Mods: we are currently figuring out Year 3 of the challenge. Most of us are very busy right now so more updates will be coming as we sort things out. :)
Jenna wrote: "Update from Mods: we are currently figuring out Year 3 of the challenge. Most of us are very busy right now so more updates will be coming as we sort things out. :)"Yay, a new edition of the competition :)
Can't speak for Iain, but (as you probably suspected), I'm offering my services as spreadsheet
(well, reusing what we already have should be super easy with the way we set it up, even with tweaking to accommodate eventual rule changes)
Mod
Yay, a new editi..."
Yea... I'm not sure how much changes/upgrades are needed for next year, other than possible streamlining, I dunno...
Maybe an updated base page to whatever the best version was used by someone for this year used universally? (Leave that up to you all :)
Mod
Not mine specifically, but Virginie's base version for 2019, with my changes to measure read counts as Shorts / Novellas / Books / Tomes, with some space on the left for custom bar graphs by request, and monthly stats on the right with less repetitive text...
There are about a dozen sheets you can copy that from, that don't include all my extra formulas that further complicate the matter ;)
My version with the book breakdowns at the top, and the second layer of monthly stats throughout the sheet as collapsible month headers, adds a ton more data, but requires constant adjustments to rows and stuff to keep clean year-long... part of it can be solved with extra hidden rows that can be expanded as needed, but it still requires a bit more spreadsheet know-how than the intermediate sheets we modified for people in January/etc...
I think the biggest undertaking for 2020, now that I'm thinking about it, will probably be building an improved baseline, then going back and reapplying people's 2019 requests ahead of time, assuming they want the same info in 2020... some people asked for the update top section and graphs, others wanted extra data columns... each subtly changes the formulas we all use, and risks errors creeping in... we still got time to sort it all out, tho, I'm sure :)
Mod
I might still need to add a new data column, we'll see... otherwise, my sheet is about data'd out, I think... altho I'll inevitably come up with something to fiddle with... I already know I want a bit more genre listing space... my UF re-reads overtook my pie graph this year until I made re-reads a "genre"...
Because I have 0 knowledge of how to do the sheets, i'll just be grateful for whatever people give me! I do like all the extra stats I has this year though, was really fun to look at.
Charlie wrote: "Because I have 0 knowledge of how to do the sheets, i'll just be grateful for whatever people give me! I do like all the extra stats I has this year though, was really fun to look at."Ditto!
Timelord Iain wrote: "I think people preferred the version I used (not to toot my own horn much)...Not mine specifically, but Virginie's base version for 2019, with my changes to measure read counts as Shorts / Novell..."
Agreed, this is the 'simplest' version with cool stats.
Should we, maybe, ask for feedback in the team threads (lots of non-Excel-savvy people don't venture in the google sheets central)? Ask people what they used and didn't use (the hyperlink in the month name to the first book read that month, for example).
There may also be a few people that didn't look at the monthly stats at all, and would rather have a super simple sheet.
The most asked for modifications were (I think): improved 'header' with novellas/books/tomes distinction, simplified monthly stats (without the text) & customs graphs on the left part (standalone/series, audio/print, etc), genre column & associated graph, publication date column & associated graph. Nothing too hard to implement, especially if we know in advance who wants what :)
Timelord Iain wrote: "I actually have plans to refine my sheet a bit more for 2020, to make my series tracker graph better... since I'm simplifying my challenge participation, I can just use a COUNTIF on my challenge co..."You could use a mix of Challenge & (existing) series column to track your series. Separate series name & number in 2 column, and play with that. I saw there was something like that on Karishma's sheet.
Another improvement for your sheet: modify the BR points formula so that you can enter Y/N/date in the BR column (the date being the official start day of the BR). That would avoid cannibalizing another column (the author one?) to keep track of start dates like you did this year.
I agree - Iain's version would work just fine for me or the basic version of it that Niki is using (albeit with a color-styling that represents the team whatever that may be). No need to break out the novellas and short stories for most people since it doesn't impact point-scoring calcs. A page is a page unless it's an audiobook or graphic novel.The one thing I will never do again is pre-log my books. Pain in the butt reshuffling them when I read them out of my intended order and I think I've had to fix my sheet's calcs a time or two because of it.
Mod
I think Niki is mostly using the 2019 default... Laura has a clean version of the upgraded look, with her brown color scheme... Angela Jr. has a hybrid that's halfway in between...
Pre-logging can definitely be a pain, but the fastest way to fix is to just highlight a large chunk of your sheet (from book title to Rating on your sheet, and Sort Range -> Date Column (Column J on your sheet))... sort A-Z, and it'll rearrange all the books without even touching the formulas, formulas recalculate, any book with a blank date column is shuffled to the bottom... I did this so many times throughout the year, and did it to Julia's sheet when I added the collapsible grouping by month sometime in October... did it so I could check her monthly pagecounts, since her top formulas hadn't been fixed yet...
Only highlight the out-of-order section, so the sorting goes faster...
Yep - I did it a ton of times this year. I think once or twice I missed a row or highlighted the wrong row and had to redo/undo it. Nothing big, just annoying :)Sorry - was too lazy to fix the monthly and most of the time that section was collapsed so I forgot it was even there :)
Rob wrote: "The one thing I will never do again is pre-log my books. Pain in the butt reshuffling them when I read them out of my intended order and I think I've had to fix my sheet's calcs a time or two because of it."You can move an entire line by clicking on the line number on the left: once to highlight it, then drag it wherever you want to put it. All the formulas change accordingly, no additional work needed (especially since that way, you get the entire line, formulas in hidden columns included).
That's what I'm doing to shuffle books.
Very cool. I think I am mentally inhibited by old-school excel functionality limitations. I don't recall being able to drag and drop without breaking everything.
Mod
Sorry - was too lazy to fix the mo..."
You want annoying?... I think I found a full-on bug... I was fiddling with my sheet last week, and fixing missing formulas in the hidden month column for newly added rows, and something about opening the column shifted a bunch of data validations for my rating stars got shifted to the challenge column, but only for about 1/2 my rows... still annoying, since my monthly summary rows break up my ability to fix the problem with one highlighted change... undoing didn't fix... I think it was a combination of opening a hidden column while having a ton of rows collapsed in a group, or something...
Mod
Well, yeah, but I find that way annoying for fully jumbled rows... that and the delays on row coloring and how URL linked cells go blank for a minute or 2... so I drag rows for small changes, and Sort Range for large changes...
Iain - You may have crossed the threshold from annoying to excruciating depending on how long it took you to find and fix it :)or in graphic terms.
"Oh I have to re-sort this or fix a formula I found an error in about two seconds. How annoying"
to
"ARRRRRRRRghh - throws keyboard out window*
Mod
or in graphic terms.
"Oh I have to re-sort this or fix a formula I ..."
Yea... I meant more annoying because...
At a certain point, a solo sheet would be easier fixed with a reversion, but in a group sheet like this, undos change other people's things and not just your own... basically if you don't undo in 60 seconds or less, you're probably deleting people's book inputs...
I'm pretty sure each user is generating their own 'chain of edits' and thus, you can't mess with others' works by hitting undo.
Rob wrote: ""ARRRRRRRRghh - throws keyboard out window*"
Fantasy Buddy Reads
Books mentioned in this topic
The Way of Kings, Part 1 (other topics)The Way of Kings, Part 2 (other topics)
The Way of Kings, Part 1 (other topics)
The Way of Kings, Part 2 (other topics)
The Way of Kings, Part 1 (other topics)
More...
I didn't bother doing it for the graphic novels and short stories counter though, since Julia hasn't read any.