Where's the eyes? Corrected per-40 spreadsheet available.

[JRRRJ]

I know I make misteaks on the spreadsheet on occasion. I always hope someone will catch them & let me know.

But nooooooo, it's never happened. And no one noticed there weren't any stats at all for our opponent in the Xavier game. Well, there are now, and you can download the spreadsheet here.

Also, note an enhancement: I've added a Minutes column to the "Stats per game" section of the Per-40 page.

Finally, would someone who knows more about spreadsheet formatting than I do tell me why Aubrey's 3FG% in the per-40-minutes section has red text with a pink background? I can't find it.

 

[bertieboy]

The cell with red text and a pink background is that way be cause of "conditional formatting". Delete that CF and it will appear as the others.

 

[bertieboy]

In addition there are empty cells in rows 23 and 24 and something seems really wrong with that big IF statement you have in row 25. I am not sure if the problem is your spreadsheet or the program I am using, Libre Office. Please let me know.

 

[maccca3232]

Finally, would someone who knows more about spreadsheet formatting than I do tell me why Aubrey's 3FG% in the per-40-minutes section has red text with a pink background? I can't find it.

You have conditional formatting on only that cell (I30).

1. Click that cell
2. Select 'Conditional Fomatting' drop-down under 'Home' tab at top.
3. Select 'Manage Rules'.
4. Click formula
5. Press 'Delete Rule' button
6. Click 'OK' button.

 

[Ex Nutmegger]

Thank you Chief Didacticist. Very interesting info. Paige really shines. Her one “growing edge“ is A/TO ratio. But, as others have pointed out, that is mostly because our three shooters are adjusting their hair bands or eating their lunch while Paige is serving up bullet speed dimes. ET’s pay attention people.

Also, Anna’s %’s are nowhere near as bad as I thought they would be. It’s just that her total numbers are too low. That’s easy to fix.

 

[HuskylnSC]

Well done. thanks for all your work.

Just an aesthetic comment. I would not have percentages in the per 40 minute or the per game sections. To my way of thinking they are more appropriate in the cumulative section. The numbers would be the same but the concept of the percentage of the averages is specious to me.

Also, you could set a default value to all your computational fields thereby removing blank cells.

 

[JRRRJ]

You have conditional formatting on only that cell (I30).

1. Click that cell
2. Select 'Conditional Fomatting' drop-down under 'Home' tab at top.
3. Select 'Manage Rules'.
4. Click formula
5. Press 'Delete Rule' button
6. Click 'OK' button.

Thanks to you and bertieboy! I never looked at the conditional formatting drop-down because I've never intentionally applied a conditional formatting rule in my life. Guess there must be some command-key combo that I hit accidentally to create this one.

Thanks again.

 

[JRRRJ]

In addition there are empty cells in rows 23 and 24 and something seems really wrong with that big IF statement you have in row 25. I am not sure if the problem is your spreadsheet or the program I am using, Libre Office. Please let me know.

The empty percentage cells in in rows 23 and 24 of the Exhibition sheet (and others in both sheets) are because there are zero attempts and I chose to differentiate between no attempts (blank cell) and no successes (0.0%).

Which cell are you calling out as wrong in row 25? If it's column B, it's the same formula as all the column B's in the 'Stats per 40 minutes" section. The formula in that cell should be:
=IF(OR($A7="UConn",$A7="Opponent"),B7/200,IF(OR($A7="",$A7="TEAM"),"",B7/40))
which says "If A7 contains 'UConn' or 'Opponent' this cell is B7/200 else if A7 contains 'TEAM' or nothing this cell is blank, else this cell is B7/40".

I have not tested the spreadsheet with the Libre Office program. Can you post or PM me a screenshot of what you see as a problem?

 

[JRRRJ]

Well done. thanks for all your work.

Just an aesthetic comment. I would not have percentages in the per 40 minute or the per game sections. To my way of thinking they are more appropriate in the cumulative section. The numbers would be the same but the concept of the percentage of the averages is specious to me.

Also, you could set a default value to all your computational fields thereby removing blank cells.

If you know a way to allow a space (for those who haven't attempted any FG/3FG/FT) in a pivot table sum I'd probably go with your aesthetics. I couldn't find one. Formulae such as =IF('3FGA'>0,'3FG'/'3FGA',"") don't work, even if I format the 3FG%s cells as text. It just results in #VALUE! when 3FGA is zero.

I'm not sure what you mean by "removing blank cells".

Thanks for the comments.

 

[HuskylnSC]

If you know a way to allow a space (for those who haven't attempted any FG/3FG/FT) in a pivot table sum I'd probably go with your aesthetics. I couldn't find one. Formulae such as =IF('3FGA'>0,'3FG'/'3FGA',"") don't work, even if I format the 3FG%s cells as text. It just results in #VALUE! when 3FGA is zero.

I'm not sure what you mean by "removing blank cells".

Thanks for the comments.

Try change result of the OR to 0

Your formula was
=IF(OR($A10="",$A10="TEAM",$F10=0),"",E10/F10)
make it
=IF(OR($A10="",$A10="TEAM",$F10=0),0,E10/F10)

that could work

 

[JRRRJ]

Try change result of the OR to 0

Your formula was
=IF(OR($A10="",$A10="TEAM",$F10=0),"",E10/F10)
make it
=IF(OR($A10="",$A10="TEAM",$F10=0),0,E10/F10)

that could work

Your example does work in the cumulative (pivot table) section, but I don't want 0 as a result if no shots have been taken. I only want that if all shots taken have been missed, which is why I'm using the first example formula in the per-40 and per-game sections, where it works fine.

The cumulative (pivot table) section doesn't appear to allow blank cells when the defined operation is summation.