Zebra Huddle™

Non-Skating Officials => General NSO Discussion => Topic started by: FNZebra on March 08, 2014, 12:07:04 am

Title: Errors in the March 2014 StatsBook
Post by: FNZebra on March 08, 2014, 12:07:04 am
Some of you may have already noticed a little something that appeared late today on wftda.com/stats

You may share any of this on social media.

Here are the details about the March 2014 StatsBook, for use with the March 01 2014 Rules:

Read Me now has full filenames visible. Info about team colors coming from the Score sheet added.


IBRF has note about submitting WFTDA scores w/in 24 hrs, and proper locations. Most references to "bout" have been changed to "game".


Score - Removed residual merged rows from the old box point tracking format. Identical when printed.
Formulas changed to accommodate different row #s and NP count is done from this tab instead of SK.
Conditional formatting to warn when entering Skater number that doesn't exist on IBRF.


Penalties - Added 2 more penalty boxes for each Skater (Maximum recordable is now 9 penalties.).
Visual indicator when Skater has Fouled Out.
Added boxes to track bench staff expulsions; support for codes N and G only for bench staff expulsions, per current rules.
Code for foul out changed from PM to FO.


Lineups - Extensive changes and formula updates.
Opposing Jammer laps no longer being counted.
Revised codes to be entered for indicating box visits, including jam stoppage for injury.
Conditional formatting to warn when data-entry of Skater number that doesn't exist on IBRF.


Expulsion-Suspension Form - Extensive changes. Better labelling, colors are Tournament compliant.


Bout Summary & Penalty Summary
Extensive changes to formulas.


Official Reviews - Changed to allow recording of the up to 8 ORs total for a game which are now possible. Added a "Keep Review?" box.


Actions & Errors - No intentional changes


Bout Clock - Added 2nd optional OR to top header


Penalty Box - Added 2nd page of sheets for each team.


Whiteboards - No intentional changes


Dual Trackers and NOTT Score - Added data validation. If you try to enter data, a pop-up will remind these sheets should not be used for data-entry.


PT - changes to allow for bench staff expulsions; support for codes N and G only for bench staff expulsions, per current rules.
The Penalty Minute sum was changed to count penalties as half minutes rather than minutes.
Code for foul out changed from PM to FO.


LU and SK - Extensive changes to formulas.


Now. all that said, I'm sure I've mucked something up someone else had done, or missed something in testing. Please use this thread to record those things you find.

Please provide your feedback like so:

Quote
    I was doing this....


    I expected this....


    Instead, I saw this....

And if you provide specifics -- the full name of the sheet, cell addresses, broken formulas, your suggested improved formulas -- it will help us to fix things even faster.

Also, for those of you who still use Excel 2003/2004, we are officially dropping support for those versions. It's been a decade already. Plus, Excel 2004 for MacOS can go die in a big, bright fire.

Consider upgrading to LibreOffice 4.2.1 or newer. We will continue to support that open-source alternative as best we can. We do not warrant that all fabulous new features in the March 2014 StatsBook will behave exactly as desired (like conditional formatting might be wonky in Libre), but we'll try to make certain all of the calculations behave.
Title: Re: Errors in the March 2014 StatsBook
Post by: Major Wood on March 08, 2014, 04:42:14 am
Also, for those of you who still use Excel 2003/2004, we are officially dropping support for those versions. It's been a decade already. Plus, Excel 2004 for MacOS can go die in a big, bright fire.

So can all versions of any Office product since 2007. I'm still bitter about the ribbon.
Title: Re: Errors in the March 2014 StatsBook
Post by: FNZebra on March 10, 2014, 08:56:02 pm
And we have an actual reported error now.

Error: On the Penalties tab, the cells to indicate Game A/B status have no formulas to do so.

Fix: In the Penalties tab, add the following formula to cell AB2:
=IF(ISBLANK(IBRF!$K$3), "", "GAME " & IBRF!$K$3)

In cell BD2, add the following formula:
=AB2

The correct formatting is already applied to these cells.

(And for the extremely detail-oriented, you might also update all other sheets to use the formula indicated for AB2 -- They mention "BOUT " not "GAME ". This bug has been marked.)
Title: Lotus' compatibility short cut
Post by: Traffic Jam on March 10, 2014, 11:05:35 pm
Not an error but i had to change the the short cut key for "Lotus' compatibility" in excel 2007 so i could enter the "/" into the lineup tracker box cell without having to click into the formula box and type "/"

Excel Options->Advanced
Lotus compatibility located at the bottom.
Title: Re: Lotus' compatibility short cut
Post by: FNZebra on March 11, 2014, 05:26:37 pm
Not an error but i had to change the the short cut key for "Lotus' compatibility" in excel 2007 so i could enter the "/" into the lineup tracker box cell with having to click into the formula box and type "/"

Excel Options->Advanced
Lotus compatibility located at the bottom.

Yes, the default setting "/" does the same thing as hitting the Alt key (activates accessibility key tips for driving the Ribbon with keystrokes). You can just delete that shortcut setting, and click OK.

EDIT: Another option is to just type
'/ (that is single quote-slash), which forces Excel to treat the / as text.
Title: Re: Errors in the March 2014 StatsBook
Post by: Lucas on March 12, 2014, 12:47:10 am
Is it not a bug in itself, but the printer-friendly pdf does not correspond to the new StatsBook.
Title: Re: Errors in the March 2014 StatsBook
Post by: FNZebra on March 12, 2014, 03:12:16 am
And... another error that snuck thru testing.

Error: On the Score tab, cells AF89 & AG89 are lacking formulas to calculate the sum of the scores above them. The total (aka final) score remains unaffected.

Fix: In cell AF89, enter the following formula:
=IF(COUNT(AF51:AF88),SUM(AF51:AF88),"")

In cell AG89, enter the following formula:
=IF(COUNT(AG51:AG88),SUM(AG51:AG88),"")
Title: Re: Errors in the March 2014 StatsBook
Post by: FNZebra on March 12, 2014, 03:16:16 am
Is it not a bug in itself, but the printer-friendly pdf does not correspond to the new StatsBook.

Please note the release dates carefully on the WFTDA.com/stats page.
Title: Re: Errors in the March 2014 StatsBook
Post by: FNZebra on March 12, 2014, 09:21:48 pm
And another issue...

Issue: All that nifty stuff about the awesome Conditional Formatting? If you are driving Excel 2007, you won't see it. :(

Fix: use Excel 2010 or higher to experience this joy. Thanks Ballmer! *sigh*
Title: Re: Errors in the March 2014 StatsBook
Post by: Kill C. Grammar on March 13, 2014, 12:49:21 am
One of our skaters found a bit of an issue with the "Points For/Against & Plus/Minus" section of the Bout Summary tab. Specifically, points were not registering for the second half. If you look at the LU cells that should provide that information, they're pulling from the wrong columns on the Lineup tab. They should be pulling jammer point totals from the hidden W and AW tabs, but they are instead looking at the X and AY tabs.

But don't make that correction. The points for the second half in the hidden Lineup tab columns add an extra 0 point jam and knock the last jam off the list. So the points are wrong on the Lineup tab. That's cool, though. You can work around it anyway. The corrections we made are below. I'm just giving you the first line of the range. You'll still need to change the $B### and  $U### numbers to match your current line.

LU O154 - O173
Are:
=IF($B154="","",SUMPRODUCT(--(Lineups!$C$50:$C$87=$B154),Lineups!$X$50:$X$87))
Should be:
=IF($B154="","",(SUMPRODUCT(--(SK!$C$88:$C$163=$B154),SK!$E$88:$E$163)))

LU AH154 - AH173
Are:
=IF($U154="","",SUMPRODUCT(--(Lineups!$AC$50:$AC$87=$U154),Lineups!$AY$50:$AY$87))
Should be:
=IF($U154="","",(SUMPRODUCT(--(SK!$S$88:$S$163=$U154),SK!$U$88:$U$163)))

LU O177 - O196
Are:
=IF($B177="","",SUMPRODUCT(--(Lineups!$C$50:$C$87=$B177),Lineups!$AY$50:$AY$87))
Should be:
=IF($B177="","",(SUMPRODUCT(--(SK!$C$88:$C$163=$B177),SK!$U$88:$U$163)))

LU AH177 - AH196
Are:
=IF($U177="","",SUMPRODUCT(--(Lineups!$AC$50:$AC$87=$U177),Lineups!$X$50:$X$87))
Should be:
=IF($U177="","",(SUMPRODUCT(--(SK!$S$88:$S$163=$U177),SK!$E$88:$E$163)))
Title: Re: Errors in the March 2014 StatsBook
Post by: FNZebra on March 14, 2014, 01:55:48 am
One of our skaters found a bit of an issue with the "Points For/Against & Plus/Minus" section of the Bout Summary tab. Specifically, points were not registering for the second half. If you look at the LU cells that should provide that information, they're pulling from the wrong columns on the Lineup tab. They should be pulling jammer point totals from the hidden W and AW tabs, but they are instead looking at the X and AY tabs.

But don't make that correction. The points for the second half in the hidden Lineup tab columns add an extra 0 point jam and knock the last jam off the list. So the points are wrong on the Lineup tab. That's cool, though. You can work around it anyway. The corrections we made are below. I'm just giving you the first line of the range. You'll still need to change the $B### and  $U### numbers to match your current line.

LU O154 - O173
Are:
=IF($B154="","",SUMPRODUCT(--(Lineups!$C$50:$C$87=$B154),Lineups!$X$50:$X$87))
Should be:
=IF($B154="","",(SUMPRODUCT(--(SK!$C$88:$C$163=$B154),SK!$E$88:$E$163)))

LU AH154 - AH173
Are:
=IF($U154="","",SUMPRODUCT(--(Lineups!$AC$50:$AC$87=$U154),Lineups!$AY$50:$AY$87))
Should be:
=IF($U154="","",(SUMPRODUCT(--(SK!$S$88:$S$163=$U154),SK!$U$88:$U$163)))

LU O177 - O196
Are:
=IF($B177="","",SUMPRODUCT(--(Lineups!$C$50:$C$87=$B177),Lineups!$AY$50:$AY$87))
Should be:
=IF($B177="","",(SUMPRODUCT(--(SK!$C$88:$C$163=$B177),SK!$U$88:$U$163)))

LU AH177 - AH196
Are:
=IF($U177="","",SUMPRODUCT(--(Lineups!$AC$50:$AC$87=$U177),Lineups!$X$50:$X$87))
Should be:
=IF($U177="","",(SUMPRODUCT(--(SK!$S$88:$S$163=$U177),SK!$E$88:$E$163)))

Yes, there does appear to be an error here. We are investigating whether this is a desired solution. Please hold.....
Title: Re: Errors in the March 2014 StatsBook
Post by: DocSkinner on March 18, 2014, 01:50:30 am
[Redacted. Not an error]
Title: Re: Errors in the March 2014 StatsBook
Post by: FNZebra on March 18, 2014, 06:01:25 pm
Some confusion with how the team names are reported on the top of the sheets.

...

Hate to break this to you, Doc, but that is how the StatsBook has worked, all the way back to the June 2010 release, if not earlier. That was way back when refs had to dodge velociraptors on the inside, calling minor and major penalties. And NSOs had to track things called Ghost Points on the Score sheet.

There is a comment in that cell for the IBRF sheet, explaining what it does. It has been there since then, too. Only change to it has been 'bout' to 'game'.
Title: Re: Errors in the March 2014 StatsBook
Post by: DocSkinner on March 18, 2014, 10:02:51 pm
Well, I'll be. I guess this is the first time I have done a non-home team double header. The help dialog just says it would "format the team names especially for double headers".

So if the game is Toaster Town Whole Wheat vs Toaster Town Bagels, the team names will read
"Whole Wheat vs Bagels"

But if the game is Toaster Town All Star vs Water City All Star, the team names will read
"Toaster Town A vs Water City A"

I guess that makes sense, but it sure was confusing when the B teams were playing the A game and the A teams were the B game.
Title: Re: Errors in the March 2014 StatsBook
Post by: Stopwatch on March 19, 2014, 03:17:58 am
Based on last year's experience, I suspect there will be an update coming out in 1-2 months.
Title: Re: Errors in the March 2014 StatsBook
Post by: SilkenTofu on March 19, 2014, 01:12:39 pm
I guess that makes sense, but it sure was confusing when the B teams were playing the A game and the A teams were the B game.

That's why I just use 1 (for the first game of the evening) and 2 (for the second game of the evening).  Then I get the team names, and it's easy to see which game happens first, and therefore which paperwork I need to distribute first.
Title: Re: Errors in the March 2014 StatsBook
Post by: Kilter The Grey on March 20, 2014, 06:09:16 pm
We are a new league (Raleigh Junior Rollers/JRDA) and had our first home bout last weekend. I download the new WFTDA Statsbook, entered the stats from the various NSO sheets and went to the LU tab and Bout Summary  - Data is not there.  The LU fields did not correctly populate, leading to all zero entries in bout summary.
Title: Re: Errors in the March 2014 StatsBook
Post by: theMadStatter on March 21, 2014, 06:53:53 pm
The new standard practice for dealing with how to track box trips vs star passes is inconsistent with the new paperwork.
Quote
During a jam in which a Star Pass has been completed, the entry and/or exit of the Penalty Box on the Lineups sheet should only be recorded on the line of the current Jammer at the time they entered or exited the Penalty Box. For example, if a skater enters the Penalty Box during the initial Jammer’s time as Jammer, it gets recorded on that line. If they exit after the Star Pass has been completed, it gets recorded on the SP line as that of the current Jammer

The problem is there is no specified code for just "exiting" the box: There is "enter" (/), "enter + exit" (X), "start" (S), "start + exit" ($).

So should the SP line have just a "\" or "|" (the other half of the X or $)? (which seems correct, but not a valid code).

Or should that SP have the entire "X" or "$"? (which seems incorrect, since they didn't enter after the SP)
Title: Re: Errors in the March 2014 StatsBook
Post by: Captain Emo on March 24, 2014, 06:16:04 pm
The new standard practice for dealing with how to track box trips vs star passes is inconsistent with the new paperwork.
Quote
During a jam in which a Star Pass has been completed, the entry and/or exit of the Penalty Box on the Lineups sheet should only be recorded on the line of the current Jammer at the time they entered or exited the Penalty Box. For example, if a skater enters the Penalty Box during the initial Jammer’s time as Jammer, it gets recorded on that line. If they exit after the Star Pass has been completed, it gets recorded on the SP line as that of the current Jammer

The problem is there is no specified code for just "exiting" the box: There is "enter" (/), "enter + exit" (X), "start" (S), "start + exit" ($).

So should the SP line have just a "\" or "|" (the other half of the X or $)? (which seems correct, but not a valid code).

Or should that SP have the entire "X" or "$"? (which seems incorrect, since they didn't enter after the SP)

I am hardly the one to answer this, being the puppy I am, but this is what I get from this situation:
*If a skater goes in pre-Star Pass, I would say they get the "/" for "Skater entered the box this jam".
*Then as that skater is already in the box for the end of the Star Pass (not entering), they would either get a "S" or "$" based on their final position. "S" for "Skater began this jam from the box" if they didn't get out by the end of the jam, or "$" for "Skater began this jam from the box and then exited the box" if they served their time.

Obviously I'm happy to let someone correct me if I'm wrong. That's just how I see it.

~Emo
Title: Re: Errors in the March 2014 StatsBook
Post by: Kilter The Grey on March 25, 2014, 03:13:06 pm
The workbook LU tab (and maybe others) doesn't handle a skater number "0" (Zero).  The totals for jammer and blocker occasions are summed incorrectly but the sheet.

If I change the skater number to another number and make corresponding changes in the score and lineup sheets the summing errors disappear.
Title: Re: Errors in the March 2014 StatsBook
Post by: Sonny on April 21, 2014, 05:13:58 pm
One of our skaters found a bit of an issue with the "Points For/Against & Plus/Minus" section of the Bout Summary tab. Specifically, points were not registering for the second half. If you look at the LU cells that should provide that information, they're pulling from the wrong columns on the Lineup tab. They should be pulling jammer point totals from the hidden W and AW tabs, but they are instead looking at the X and AY tabs.

But don't make that correction. The points for the second half in the hidden Lineup tab columns add an extra 0 point jam and knock the last jam off the list. So the points are wrong on the Lineup tab. That's cool, though. You can work around it anyway. The corrections we made are below. I'm just giving you the first line of the range. You'll still need to change the $B### and  $U### numbers to match your current line.

LU O154 - O173
Are:
=IF($B154="","",SUMPRODUCT(--(Lineups!$C$50:$C$87=$B154),Lineups!$X$50:$X$87))
Should be:
=IF($B154="","",(SUMPRODUCT(--(SK!$C$88:$C$163=$B154),SK!$E$88:$E$163)))

LU AH154 - AH173
Are:
=IF($U154="","",SUMPRODUCT(--(Lineups!$AC$50:$AC$87=$U154),Lineups!$AY$50:$AY$87))
Should be:
=IF($U154="","",(SUMPRODUCT(--(SK!$S$88:$S$163=$U154),SK!$U$88:$U$163)))

LU O177 - O196
Are:
=IF($B177="","",SUMPRODUCT(--(Lineups!$C$50:$C$87=$B177),Lineups!$AY$50:$AY$87))
Should be:
=IF($B177="","",(SUMPRODUCT(--(SK!$C$88:$C$163=$B177),SK!$U$88:$U$163)))

LU AH177 - AH196
Are:
=IF($U177="","",SUMPRODUCT(--(Lineups!$AC$50:$AC$87=$U177),Lineups!$X$50:$X$87))
Should be:
=IF($U177="","",(SUMPRODUCT(--(SK!$S$88:$S$163=$U177),SK!$E$88:$E$163)))

Yes, there does appear to be an error here. We are investigating whether this is a desired solution. Please hold.....


Still Holding.... So what was the final verdict on this one?


 
Title: Re: Errors in the March 2014 StatsBook
Post by: Sonny on April 21, 2014, 05:21:02 pm
The corrections I've made are below. I'm just giving you the first line of the range. You'll still need to change the $U### numbers to match your current line.



AH131 - AH150
Are:
=IF($B131="","",AH154-AH177)

Should be:
=IF($U131="","",AH154-AH177)
Title: Re: Errors in the March 2014 StatsBook
Post by: Nick Bergus on April 22, 2014, 10:05:53 am
I've locked this thread at the request of the masters of the StatsBook. The new versions have their own thread here (http://www.zebrahuddle.com/index.php?topic=4337.msg47261)