2019 Team Competition Tracker/Google Sheet Central

[Virginie]

@Manju: All done! The graph will automatically complete as you read books :)

[Em Lost In Books]

Virginie wrote: "@Manju: All done! The graph will automatically complete as you read books :)"

thank you! (^.^)

[Diana Stormblessed]

Virginie wrote: "Diana Stormblessed wrote: "So I took a look at your sheet, Virginie, and man do I like it. I love your series vs standalones, audio vs print, and page points vs bonus points bar. I looked at adding..."

I think you already do... Still worth it 😘

[Timelord Iain, Tech Support]

Virginie wrote: "Timelord Iain (Eeyawn) wrote: "Virginie was just an idea machine... the day AFTER she duplicated the template 50x and released the spreadsheet to the group..."

Says the guy that gave ideas to ever..."

I gave 1 idea... you're the sheet they keep looking at and saying:

"Oooh... I want that!!!"

[Virginie]

@Diana: All done :)

[Diana Stormblessed]

Thank you my love! I'll check it tonight. Can't from work :-)

[Narilka]

I'll have what she's having! Can I get help making my sheet like Virginie's? Is it just a matter of copy/paste and the formulas will transfer?

[Timelord Iain, Tech Support]

So I got most of Virginie's formulas setup on my sheet this morning... and also made my own bar graph, comparing shorts/novellas/books/tomes...

But, if you look at it right now, it's like someone vomited all over it... it's a mess of fonts and colors... I just can't make my mind up about much of anything when it comes to the best fonts & colors... which is why I left that to Virginie, when the templates were being made for everyone else :)

My formulas use the monthly tallies to generate the top row totals, kinda like last year, and largely ignore the Graphic Novel column... might be useful, when copying, for anyone that wants Virginie's layout, since Virginie is using a more hardcoded system, where she needs to remove formulas at the end of each month in place of final numbers (because she does a lot of short story anthologies and BRs that last several months, like Bookburners & Superpowereds...

[Timelord Iain, Tech Support]

My sheets always going to be a bit overly colorful... I decided to just lean into it and make my color scheme revolve around the 6 colors I like to use for my rainbow tallies...

The bottom row of the color palette, dark magenta, purple, blue, cornflower blue, cyan & green... with a bit of black for the column headers and point totals...

I decided this hints at the fact I'm secretly on all the teams >:)

[Timelord Iain, Tech Support]

Finally contributing something new to the thread... I created a 3rd option for the dropdown menu in D1:

Pages: daily / predicted (old)
Points: daily / predicted (old)
Planned: pages / points (NEW)

This just tallies up all the Buddy Reads & stuff you've already input into your sheet, but haven't read yet

Formulas:

D2 addition: "Planned: pages / points", SUMIF(D16:D,"",E16:E)
E2 addition: "Planned: pages / points", SUMIF(D16:D,"",L16:L)+SUMIF(D16:D,"",M16:M)+SUMIF(D16:D,"",N16:N)

E2 is a bit longer, because L16:N didn't work, and the internet offers really long alternatives...

Frankly, it'd be nice if my whole sheet just had a Planned/Actual trigger... so I could view my planned pages/points in all the monthly tallies, as well, but that seems like a lot of formula work, implementing extra IF arguments...

[Niki Hawkes, I made it past GOTM... barely]

I spent like a half an hour just staring at the spreadsheet yesterday... in total awe.

[Brian]

That Spreadsheet is BAD ASS!!!!!! Great job!

[Timelord Iain, Tech Support]

It's an addiction... we're constantly coming up with other cool things to do with it...

[Rob]

spreadsheet addiction is a real thing. oh, the rabbit holes you find. This year though I feel more like the supplier and not the addict. :)

[Virginie]

Timelord Iain (Eeyawn) wrote: "Finally contributing something new to the thread... I created a 3rd option for the dropdown menu in D1:

Pages: daily / predicted (old)
Points: daily / predicted (old)
Planned: pages / points (NEW)..."

That's a great idea! But you should know that the Daily / Predicted thing is only on my spreadsheet (and now yours as well); the others have a single cell with 4 options (Predicted pages, pages per day, predicted points and points per day) so the formulas have to be modified a little bit :)

[Virginie]

Rob wrote: "spreadsheet addiction is a real thing. oh, the rabbit holes you find. This year though I feel more like the supplier and not the addict. :)"

So... You're our dealer? Fits the Triumvirat of Evil theme! :D

[Saar The Book owl]

Virginie wrote: "Mmmh. Since you mentioned column H, I'm assuming you're talking about the monthly stats. It's simply a statistic to indicate how many audiobooks you listened to within a month; if you read only ebo..."

Sorry, I've been away for a couple of days, but could you add what you mentioned in Jenna's post:


- Novellas: page count between 1-250
- Books: page count between 251- 600
- Tomes: page count between 601 - infinity (:p)
- All books: all of the above + books entered through the form;
- Series books / Standalones; a book is counted as standalone if you leave column C empty

I don't listen to audiobooks, so that would be a great addition. Thanks for the effort.

[Saar The Book owl]

Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Okay, thanks, Iain :)"

I edited my previous comment with ideas... if you want to track how many doorstopper tomes you read in 2019, for example..."

That would be an interesting idea. I hope to read more of them. So tracking would be good.

[Timelord Iain, Tech Support]

Saar The Book owl wrote: "Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Okay, thanks, Iain :)"

I edited my previous comment with ideas... if you want to track how many doorstopper tomes you read in 2019, for exa..."

Seems like a great statistic... on my sheet, I even made a bar graph that shows the distribution of short stories, novellas, books, tomes...

[Virginie]

@Saar: I can do it, no problem :)
Just so you know though, I might not have the time to get to it before the end of the month/early January. Maybe Iain can do it for you before that? :)

[Anni]

Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Okay, thanks, Iain :)"

I edited my previous comment with ideas... if you want to track how many doorstopper tomes..."

That sounds great! I need something like this as well! :) But sadly right now I'm unable to do anything with my sheet... Buuuh!

[Charlie]

so if I use the form, will that information then appear on my score sheet tab?

[Anni]

Charlie wrote: "so if I use the form, will that information then appear on my score sheet tab?"

Not the information, no. Only the points will be added to your score sheet

[Timelord Iain, Tech Support]

Charlie wrote: "so if I use the form, will that information then appear on my score sheet tab?"

The form inputs all appear in a separate form... your form should have a link near the top, to let you see your form entries, I think... but like Anni said, those don't get copied into your sheet, because the risk of a problem was too high... we experimented early on... instead, the top formulas add your totals from the Form Entries tab... the Form Entries tab does it's own totaling for everybody in the competition, similar to the Participant Data sheet...

[Timelord Iain, Tech Support]

Virginie wrote: "@Saar: I can do it, no problem :)
Just so you know though, I might not have the time to get to it before the end of the month/early January. Maybe Iain can do it for you before that? :)"

I got the gist of it done... the new layout of totals in the top 2 rows, the condensing of the monthlies into columns C-K, and the addition of some graphs in A-B... there might be some column sizing left to fine-tune, condensing/cutting of totals in Row 14, and picking the right colors for the charts... I left the graph colors at what were chosen for Jenna, but picked some colors that matched Saar's for the background... the Audiobook graph also seemed like a waste, so I gave Saar my Novella/Book/Tome graph instead, but that's open to change, I think...

[Charlie]

Just so I don't do it wrong, if i use the form when im out and about but then add my entry to my tab on the spreadsheet later will i give me double points for that book or would it know not to include that book?

[Anni]

Charlie wrote: "Just so I don't do it wrong, if i use the form when im out and about but then add my entry to my tab on the spreadsheet later will i give me double points for that book or would it know not to incl..."

As awesome as excel is, I don't think it can do that... So if you add your books to your sheet after already using the form, you'll get double points.

If you want all the cool statistic features, I think it's better to not use the form and just add your books to your sheet when you have time for it... If you are in a hurry you could also only add a name, the pages, date and the y/n for audiobook and graphic novel and come back later to add series, aurhor, challenge, rating and co.

Also if you need helf, I think there will be always someone there to help you ;)

[Virginie]

Just added a little feature on the Form Responses tab: a checkbox on the far right (column K). If you check it, the book will be ignored in all the point calculations.

So... On your scoresheet, you have a link to see all the books you entered with the form. If you want to add those books to your own scoresheet, you simply have to check the little box to avoid getting double points :D

That way, you can use the form when you're out and about, and then add the books to your scoresheet later when you have some time to enjoy all the new features & stats.

[Virginie]

@Iain: Thanks for making the modifications on Saar' sheet! You really dissected and studied all my weird formulas, heh? :D

[Timelord Iain, Tech Support]

Virginie wrote: "@Iain: Thanks for making the modifications on Saar' sheet! You really dissected and studied all my weird formulas, heh? :D"

That's what I spent Tuesday doing, with my own sheet design...

For Saar's sheet, I mostly just copied a column of stats, hovered over the border, and used the hand to drag it over (this is the best way to move formulas without having Google Sheets shift the formula column associations)... then I need to copy a column and only paste the format, to reset the coloring (dragging rows leaves blank white columns...

For the graphs, I just copied them over from Jenna's sheet and duplicated the red coloring... and then stole my graph for Novellas/etc...

Your sheet did teach me about using Switches & Sparklines, tho :)

And our discussions pre-release taught me how to use $s and &s in formulas... and to copy/paste... lots of great tools...

[Timelord Iain, Tech Support]

I have to say, I prefer our layout over the default one... just matching up the monthly stats with the column headers, instead of the jumble the default is...

[Timelord Iain, Tech Support]

As a bit of a joke at work this morning, my boss was talking about the changed packaging for the cat food, and I was like

"That looks like Dark Cyan 3, to me..." since I've been fiddling with colors so much in the spreadsheets lately, and he knows that... and double checking now that I'm home, I was right... :)

[Virginie]

@Iain: If I learned anything from this, it's how to quickly correct a formulas on 50+ sheets :p
(of course, it would have been better if I learned to NOT make idiotic mistakes when writing my formulas... :p)

[Timelord Iain, Tech Support]

Virginie wrote: "@Iain: If I learned anything from this, it's how to quickly correct a formulas on 50+ sheets :p
(of course, it would have been better if I learned to NOT make idiotic mistakes when writing my formu..."

Testing is an important part of "programming"... but so is acknowledging that dozens/hundreds/millions of people will do a better job of breaking your hard work than you could ever think to, on your own :)

[Charlie]

Virginie wrote: "Just added a little feature on the Form Responses tab: a checkbox on the far right (column K). If you check it, the book will be ignored in all the point calculations.

So... On your scoresheet, yo..."


thanks!!

[Rob]

Virginie wrote: "@Iain: If I learned anything from this, it's how to quickly correct a formulas on 50+ sheets :p
(of course, it would have been better if I learned to NOT make idiotic mistakes when writing my formu..."

Virginie - mind taking a gander at my sheet to make sure I didn't blow anything up? I was working on reformating a version of yours (and mayhaps for Julia's as welll later).

[Virginie]

Rob - Sure, when I have some free time :)

[Saar The Book owl]

Thanks, Iain, for the effort :) So if I read a series or stand alone, do I need to add the title or will this be added automatically?

[Timelord Iain, Tech Support]

Saar The Book owl wrote: "Thanks, Iain, for the effort :) So if I read a series or stand alone, do I need to add the title or will this be added automatically?"

The graphs on the personal pages only work with books input on your personal page, not the Form input... and the graph assumes any book you don't input a Series Name for, is standalone... Virginie has a version where you put a "/" to denote standalone... the graphs only count books with Read Dates, as well...

We removed the author & series columns from the Form when we decided it was too risky to import form inputs into everyone's sheets... now the form just has the bare minimum for calculating points, and keeping entries discernible...

[Angela, I'm Silverfox lost]

The spreadsheet looks incredible guys. I'm in awe of the work you have all done.

[Saar The Book owl]

Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Thanks, Iain, for the effort :) So if I read a series or stand alone, do I need to add the title or will this be added automatically?"

The graphs on the personal pages on..."

Okay, I think I'll understand it fully when I can use it.

[Timelord Iain, Tech Support]

Saar The Book owl wrote: "Timelord Iain (Eeyawn) wrote: "Saar The Book owl wrote: "Thanks, Iain, for the effort :) So if I read a series or stand alone, do I need to add the title or will this be added automatically?"

The ..."

Nothing better than hands-on experience :)

[Virginie]

That deserves some bold... :p

[Anni]

Virginie wrote: "That deserves some bold... :p"

😂😂😂

[Timelord Iain, Tech Support]

When I first saw these comments, my initial response idea was:



then I realized even that can be given a double meaning...

[Rob]

Timelord Iain (Eeyawn) wrote: "When I first saw these comments, my initial response idea was:



then I realized even that can be given a double meaning..."

What happens in your own home, stays in your own home.

And just for fun...Oh yeah!

[Kathrine]

Okay, so I tried to modify my own sheet by myself so I wouldn't have to disturb any of you excel-gods in your holidays... but lo and behold, I fucked something up.

I was trying to copy from Angela Jr.'s sheet, but something went wrong and now I get a bunch of #VALUE!'s and they scare me... Can anyone help? 😂

[Timelord Iain, Tech Support]

Kathrine wrote: "Okay, so I tried to modify my own sheet by myself so I wouldn't have to disturb any of you excel-gods in your holidays... but lo and behold, I fucked something up.

I was trying to copy from Angela..."

No worries... the problem is that we have to go in and modify the Participant Data fields for you to match the new data locations... no big deal

[Kathrine]

Timelord Iain (Eeyawn) wrote: "No worries... the problem is that we have to go in and modify the Participant Data fields for you to match the new data locations... no big deal"

Ah, thanks and sorry for the inconvenience!

[Kathrine]

Hey, Iain, btw, can I steal your "Shorts | Novellas | Books | Tomes"-thingy? got my answer in excel