2018 Team Competition Tracker/Google Sheet Central

[Virginie]

The fact that this happened right after I made changes to my scorecard is a coincidence! :o

[Rob]

Charlie wrote: "Hey all, you might know this as I haven't read through all the comments but just a heads up, the spreadsheet looks a little broken; Tome Raiders is showing as having 0 points on the first page."

Bizarre. No idea why that's happening to two teams. I went back calc'd via a different method. should be good now.

[Timelord Iain, Tech Support]

Seems like the sheet is breaking a lot today... Blood Eagles' tally wasn't working this morning and I had to make a temporary fix... it's one of those weird bugs where when you click on the cell that's broken, and highlight the formula, it shows the correct number, but the cell itself returns 0...

EDIT: Looks like Virginie Rob fixed it with her $ formulas, using the individual tallies on the Scoreboard page, instead of the Team Scoreboard page... and I just realized that Charlie's comment was the last on this 11th page and there are more on the next page...

[Timelord Iain, Tech Support]

Virginie wrote: "The fact that this happened right after I made changes to my scorecard is a coincidence! :o"

Blood Eagles broke after I fiddled with MY scorecard, SO...

[Virginie]

Iain wrote: "Nice... you cut out ALL the hard values now... I didn't know there was a formula to write the month name... is there one to determine the number of days in a month?... 30 days hath September, and a..."

To write the month name, I'm playing with the formatting. The cell value is a date (the first of the month) that is automatically given with =DATE(2018,C4,1). In that formula, C4 is the cell where I put the number of the month I want to track.

To get the number of days within the month, you can use =DAY(EOMONTH(B4,0)), where B4 is the cell previously set up to display the month's name.

EDIT: This example is implemented in my own scoresheet, column L, if you want to take a look.

[Virginie]

Iain wrote: "Also... why do some formulas need "$K:$K" and others just use "K:K"?"

I used $K:$K when I dragged to formulas 'cause it's faster. K:K are used in formulas I typed one by one, before I realized it was idiotic and that I should just drag or copy-paste 😂

Iain wrote: "I really should have simplified some of my formulas to that, since I'm already upping them to j4-j400 since I read so much that I need more than 300 lines... "

Yup, I have the same problem with my own scorecard...
Sidenote: if you do end up using K:K formulas, you should delete all useless rows (401 to 1000) 'cause it's going to check those as well and slow down the calculations. I already did that to every sheet that wasn't locked in an attempt to make 'em faster.

[Virginie]

Iain wrote: "Virginie wrote: "The fact that this happened right after I made changes to my scorecard is a coincidence! :o"

Blood Eagles broke after I fiddled with MY scorecard, SO..."

It broke because it couldn't handle how amazing we are. Obviously...

[Timelord Iain, Tech Support]

Virginie wrote: "Iain wrote: "Virginie wrote: "The fact that this happened right after I made changes to my scorecard is a coincidence! :o"

Blood Eagles broke after I fiddled with MY scorecard, SO..."

It broke be..."

You're our PR manager...

[Siobhan]

Totals are wrong on the Team Scoreboard page now (page 2). Blood Eagles are showing as zero and Tome Raiders only 500 odd. I'm not touching it!!

[Charlie]

Siobhan wrote: "Totals are wrong on the Team Scoreboard page now (page 2). Blood Eagles are showing as zero and Tome Raiders only 500 odd. I'm not touching it!!"

That was my thought Siobhan, I wouldn't even know where to begin.

[Timelord Iain, Tech Support]

Diana is the only score adding on the Team page, I see... a lot of oddities lately... the main scorecard page also has 2 decimal places for 90% of people, for some reason... 1163.70 and the like...

[Timelord Iain, Tech Support]

I leave most fixing to Rob & Virginie... they seem to know more about spreadsheets... I'm rusty and learn through trial & error & Google Magic...

[Siobhan]

Yeah that seems to be the best plan, they will sort it out! XD
I'm good at Excel in the limited capacity I use it at work, but not so much Google sheets on my phone!

[Timelord Iain, Tech Support]

I can't even use Google Sheets on my phone... at least in browser... maybe if I download some app... but on browser, I can't view much, and any time I click a tab it just reloads the first tab... so I gave up on that months ago...

[Virginie]

Siobhan wrote: "Totals are wrong on the Team Scoreboard page now (page 2). Blood Eagles are showing as zero and Tome Raiders only 500 odd. I'm not touching it!!"

I'm tempted to simply delete those sums as we already have the team scores just next to it. I don't know why it's not working expect that summing ranked values is bugging regularly.

[Diana Stormblessed]

Iain wrote: "Diana is the only score adding on the Team page, I see... a lot of oddities lately... the main scorecard page also has 2 decimal places for 90% of people, for some reason... 1163.70 and the like..."

I swear I didn't break it! I haven't even been at my computer in 2 days lol

[Timelord Iain, Tech Support]

Been fiddling with my sheet again... I setup that frozen top 3 rows thing the cool kids have so you can see your totals... too bad there doesn't seem to be a way to freeze the Monthly rows so they stay visible when scrolling down, but can disappear when scrolling up...

Anyway... I just noticed Virginie was using her challenge column to serve 2 purposes... triggering the bonus points AND listing the challenge name/slot... great idea... but the formula is clunky...

So I google'd around a bit and almost went with IsBlank(cell#) to let me put whatever I want in the cell for challenge info... THEN I realized the structure of the bonus points is =if(logicStatement, ifTrue, ifFalse)...

So I just changed it from

=if(F5="y", H5/4, 0)

to

=if(F5="n", 0, H5/4)

... I just swapped the order of the results and got what I wanted...

[Virginie]

Iain wrote: "Been fiddling with my sheet again... I setup that frozen top 3 rows thing the cool kids have so you can see your totals... too bad there doesn't seem to be a way to freeze the Monthly rows so they ..."

Oh! OOOOH! You're a genius! Changing my ugly formulas to this right this moment!

EDIT: modified it to =IF((F5="n")+(F5=""),0,H5/4) to account for empty cells. Will implement it in Fluffers' spreadsheet soon.

Thanks Iain :D

[Timelord Iain, Tech Support]

Virginie wrote: "Iain wrote: "Been fiddling with my sheet again... I setup that frozen top 3 rows thing the cool kids have so you can see your totals... too bad there doesn't seem to be a way to freeze the Monthly ..."

Of course, then I ended up writing 3 extra conditional formatting rules to colorize each of my challenges differently, that way I can see my A-Z challenges and Book Cover Challenges at a glance, separately from all my Incomplete Series Challenge progress... you always recoup the complexity somewhere, it seems...

[Virginie]

Hahaha. I considered doing that, but I'm using books for Incomplete and another challenge sometimes. I started to mess with my brain, so I decided that this part of my spreadsheet would be simple ;)

[Timelord Iain, Tech Support]

I started fiddling some more and basically learned ArrayFormulas break Google Sheets quick...

=ArrayFormula(IF(ISBLANK(C5:C),"",MONTH(C5:C))) in Cell O5 plugs in the result we want in every cell in column O (except for a few issues with DNF cells)... but requires a new column for monthly tallies and their Y values... and every monthly total is reading 0 even tho hovering shows the correct value... it's like the scorecard bugs all over again...

[Virginie]

Mmmh. I fixed the scorecard bug by manually entering B3+...+B25 instead of a SUM(B3:B25). You're right, some formulas just aren't working on Google Sheets with no apparent reason.

[Timelord Iain, Tech Support]

Dragging your month formula down 400 cells was easier than trying to make ArrayFormula work... I wanted to learn to use ArrayFormula since it cuts down on cell dragging to copy formulas...

[Rob]

And the spreadsheet rabbit-hole has been achieved :)

[Timelord Iain, Tech Support]

Me and Virginie are an echoing chamber around here... constantly finding ways to achieve results Harder. Better. Faster. Stronger.

[Virginie]

Iain wrote: "Dragging your month formula down 400 cells was easier than trying to make ArrayFormula work... I wanted to learn to use ArrayFormula since it cuts down on cell dragging to copy formulas..."

This type of bugs usually get me screaming at the screen "WHY ISN'T WORKING!?! I DON'T GET IT! WORK, DAMMIT!"
I'm quite proud of myself for simply using another way to calculate the sum and walking away.

[Virginie]

Iain wrote: "Me and Virginie are an echoing chamber around here... constantly finding ways to achieve results Harder. Better. Faster. Stronger."

Work it
Make it
Do it
Make us

[Yanique, Thread Master]

Virginie wrote: "Iain wrote: "Me and Virginie are an echoing chamber around here... constantly finding ways to achieve results Harder. Better. Faster. Stronger."

Work it
Make it
Do it
Make us"

Harder
Better
Faster
Stronger

[Virginie]

@Iain: Moved stuff around and modified a few things in my spreadsheet (mainly new stats in the first 3 rows). If you want to take a look ;)

[Timelord Iain, Tech Support]

Virginie wrote: "@Iain: Moved stuff around and modified a few things in my spreadsheet (mainly new stats in the first 3 rows). If you want to take a look ;)"

Main reason I don't have Rankings on my sheet is I'm rank #1 by a wide margin, and that seems like bragging... I've been thinking about adding monthly average ratings, tho...

Also, couldn't you just take the daily pages/points from the "Pages Read" tab?... maybe the fact that's a sorted array or whatever makes that difficult...

By the way, you know most Google Sheets fomulas for the date don't include today?... Today is the 170th day of the year, but the Google Sheets formulas just tell you it's been 169 days since January 1st... so you need to add 1 to your division numbers for averages... this is why I wish there was a Julian Date function that would just tell you it's the 170th day of the year, but there doesn't seem to be one every time I start googling...

[Virginie]

Iain wrote: "Main reason I don't have Rankings on my sheet is I'm rank #1 by a wide margin, and that seems like bragging... I've been thinking about adding monthly average ratings, tho..."

Hehe. I've been playing with the team scores to get the difference between team #3 and #4. I had the 'points to #1' previously but it was depressing me x)
Monthly averages are a good idea! I think I gave you a formulas for the number of days per month a few comments ago.

Iain wrote: "Also, couldn't you just take the daily pages/points from the "Pages Read" tab?... maybe the fact that's a sorted array or whatever makes that difficult..."

Yup. I'm also recalculating my ranking instead of getting it from the first tab for the same reason. Also, I was too lazy to change tabs to get the right cell... x)

Iain wrote: "By the way, you know most Google Sheets fomulas for the date don't include today?... Today is the 170th day of the year, but the Google Sheets formulas just tell you it's been 169 days since January 1st..."

I didn't know that! Thanks, I'll make the change :)

[Timelord Iain, Tech Support]

Virginie wrote: "Iain wrote: "Main reason I don't have Rankings on my sheet is I'm rank #1 by a wide margin, and that seems like bragging... I've been thinking about adding monthly average ratings, tho..."

Hehe. I..."

I would think a generic formula for stealing the averages from "Pages Read" would be to find your name by comparing to cell A1 in your own sheet and then copying the relevant cell... I dunno what that formula would look like, tho... at least it would be copyable into any sheet, tho...

And I just realized all the averages were off in the Pages Read tab, after I mentioned this, so I added +1 to all the formulas in column AQ

[Virginie]

Mmmh. Seems complicated just to get an average. Wouldn't it be better to simply recalculate using the points/pages?

This talk made me notice that Sedat & Iniya are not appearing in the ranking on the "Pages Read" tab.

I think I'll stop fiddling with the spreadsheet for now, unless you have some more genius to share :D

[Timelord Iain, Tech Support]

Virginie wrote: "Mmmh. Seems complicated just to get an average. Wouldn't it be better to simply recalculate using the points/pages?

This talk made me notice that Sedat & Iniya are not appearing in the ranking on ..."

An oversight, I'm sure... we added a bunch of people at once a few weeks ago... and I haven't added anyone to anything since I made the Pages Read tab months ago, when I just duplicated the scorecard and changed the formulas a bit... then you or Lundos came along and added some new stuff to the page...

[Timelord Iain, Tech Support]

Virginie wrote: "Mmmh. Seems complicated just to get an average. Wouldn't it be better to simply recalculate using the points/pages?

This talk made me notice that Sedat & Iniya are not appearing in the ranking on ..."

And I agree... sometimes fancy formulas just make everything overly complicated...

[Zaara]

Yanique wrote: "Virginie wrote: "Iain wrote: "Me and Virginie are an echoing chamber around here... constantly finding ways to achieve results Harder. Better. Faster. Stronger."

Work it
Make it
Do it
Make us"

Ha..."

Who knew...who knew spreadsheets could be such good fun?
Please don't you lot stop....

[Em Lost In Books]

Team Tome Raiders scores has disappeared from "Member Ranking by Team" table.

[Virginie]

Fixed

[Bill]

I would greatly appreciate if someone could go into my page and give me a sort by date read. I’m use google sheets to update and it won’t let me sort. When I tried on excel yesterday it would only let me do a temporary sort. Thanks.

[Virginie]

Your sheet is locked :'(

[Bill]

Thanks for trying.

[Rob]

Bill wrote: "I would greatly appreciate if someone could go into my page and give me a sort by date read. I’m use google sheets to update and it won’t let me sort. When I tried on excel yesterday it would only ..."

Fixed. Also unprotected your sheet

[Bill]

Thanks Rob.

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

Hi Rob - new signups have been added:

Bea - Tome Raiders
Slytherpuff - Jaghut Guardians

whenever you have a chance to update the scorecard. Thanks! :)

[Virginie]

Dunno who tried to create Bea's sheet but I was faster 😈

[Rob]

Niki Hawkes wrote: "Hi Rob - new signups have been added:

Bea - Tome Raiders
Slytherpuff - Jaghut Guardians

whenever you have a chance to update the scorecard. Thanks! :)"

Lol, apparently Virginie is trying to beat me to it. created a sheet for Bea at the same time I was doing it. I'll leave her to it :)

[Virginie]

Faster to comment too. MUHAHAHAHAHA

[Rob]

Virginie wrote: "Faster to comment too. MUHAHAHAHAHA"

So much for being Master of the Spreadhseet of Dragons....

*Makes Virginie Knight of High House Google Sheet*

[Virginie]

Rob wrote: "Virginie wrote: "Faster to comment too. MUHAHAHAHAHA"

So much for being Master of the Spreadhseet of Dragons....

*Makes Virginie Knight of High House Google Sheet*"

YAY! Do I get a sword? A dragon? ;)

[Bea]

Rob wrote: "Niki Hawkes wrote: "Hi Rob - new signups have been added:

Bea - Tome Raiders
Slytherpuff - Jaghut Guardians

whenever you have a chance to update the scorecard. Thanks! :)"

Lol, apparently Virgin..."

You are fighting over me? Lol, I feel flattered now :)