BowlingFans.com, The site for the fans, by the fans....
Sponsored Links




ChatBox:

Sponsored Links


Page 3 of 3 < 1 2 3
Topic Options
Rate This Topic
#178644 - 05/16/13 05:41 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
Learning more "new" stuff...

One of the bowling score tracking sites I use tells you the percentage of time you've shot certain scores, and this is where another application of the COUNTIF function comes in. An expression can be used as the second argument of the function, so, if you wanted to count the number of times you've shot 600 (but not 700 or more), you can use this formula:

COUNTIF(range_of_series,">=600")-COUNTIF(range_of_series,">699")

I used this to build a table of the percentages of games and series in my prototype log, which has been updated; keep in mind you'll probably want your total count in a separate cell to avoid a circular cell reference for when you do your division, and don't forget to format the values representing your percentages as percentages. smile

FYI, if you didn't know, you must lead any formula with an equals sign (=) when entering them in a cell.


Edited by Richie V. (05/16/13 05:49 PM)
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
#10100 - 1 second ago Sponsored Links
Sponsored Links Online   content
Legend

Registered: Fri Aug 27 2004
Posts: 10100
A/S/L: Mountain View, CA
Top
#180940 - 09/05/13 05:16 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
Sort of bumping this for the people that want to start keeping Excel logs (or maybe do a log on Google Docs) of their bowling this season, but I do have something to add: If you're keeping track of your pair averages and want to start calculating them as you add them, you can set up the whole thing, but you may get the #DIV/0! error when you do the division to get your average early on; to suppress the division by zero error, use the IF function. Ex. you have your count in D40 and your pins in E40, set up the following formula:

=IF(D40=0,"",E40/D40)

The pair of quotes tells your spreadsheet to leave the cell blank.

EDIT: Meant to mention this, too...sometimes, you'll want to hide the contents of cells (as I did this year with my total games and series in the section that calculates my percentage of 200, 250, etc. games (and series); you do this by using the custom format ;;;


Edited by Richie V. (09/05/13 05:28 PM)
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
#183774 - 01/02/14 09:07 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
This thread was started with the league bowler who wants to keep his own logs in mind, but some of us use Excel to run tournaments, too. Excel can, of course, calculate handicap automatically, but there are pitfalls to remember, particularly with regards to bowlers averaging over the scratch score. If you use a formula such as TRUNC((220-C3)*0.8) (where C3 is the cell with the bowler's average), you end up giving bowlers over a 220 average negative handicap. While this will work for some, most tournaments have bowlers averaging over the scratch score bowl scratch. To pull that off, you need the IF function, as follows:

IF(C3<220, TRUNC((220-C3)*0.8), 0)

This way, the IF function returns a 0 value if the average is greater than or equal to 220. If the handicap rule has a different percentage of negative handicap than positive handicap, the IF function can also be used:

IF(C3>220, 220-C3, TRUNC((220-C3)*0.9))

if the handicap rule is 90% of 220 with full negative handicap.

As an added bonus, some of us are entering scores and updating the sheet every game (via the sorting option). Usually, you can put the place number in column A, and use Excel's "smart fill" feature (just put 1 and 2 in consecutive rows, select those cells, mouse over the lower right corner of the selection until you get a black cross, then drag down), deleting all of the values in the column first every time you need to update.


Edited by Richie V. (01/02/14 09:21 PM)
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
#185509 - 02/27/14 09:02 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
Excel is a natural for figuring out financial stuff, and many of us enter brackets in our leagues and tournaments, so another bowling use for Excel is to calculate your profit/loss on your brackets. You can use the first columns for your session stuff, and then calculate your season total profit/loss in another column. What I did in my profit/loss sheet is also figure out my profit/loss percentage, which is a simple division formula formatted as a percentage; remember, I also said earlier you can format your negative numbers as red text, so the following number format comes in handy:

0.00%;[Red]0.00%

As an aside, the person running brackets usually takes out an entry as a fee, and 1/8 = 12.5%, so if you're at least losing less than that percentage, you can say you're doing well. smile
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
#187971 - 08/09/14 07:20 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
Thought I'd "bump" this thread with potential new forum users getting ready for their leagues who could use the help with tracking their bowling in a spreadsheet. The thread subject says Excel, but you can probably do most of this in LibreOffice and Google Sheets, as well.
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
#187972 - 08/09/14 08:08 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
mmalsed Offline
Virtual League Champion

Registered: 10/18/10
Posts: 1381
A/S/L: 43/M/Riverside, CA
I think a lot of the functions you use aren't in Google Sheets or LibreOffice. frown
_________________________
Avg: 206
Season High Gm: 279 / Lifetime: 290
Season High Ser: 762 / Lifetime: 762

16# IQ Tour Pearl/16# Crux/16# Marvel S/15#White Dot

"Gotta kick at the darkness 'till it bleeds daylight"

Top
#188000 - 08/13/14 04:27 AM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
mmalsed, maybe stuff like formatting is done differently in Google Sheets and LibreOffice, but the formulas and functions absolutely work in both.
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
#192101 - 09/09/15 05:50 AM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
Giving this thread an annual "bump" for any new users who might be thinking of using Excel (or other spreadsheet applications) to track their bowling. smile
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
#194980 - 09/07/16 03:47 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4686
A/S/L: 52/M/Brockton, MA
Doing another "bump" for any new users who'd like to know how to keep their bowling statistics in a spreadsheet (including LibreOffice Calc and Google Sheets, for which much Excel stuff also applies).

This is going to be more than a bump, though, because my association ran a tournament last year in which you qualified by your head-to-head wins each game against the field rather than total pins. The wins against the field were calculated with the formula COUNTIF(A1:A20,"<"&A10), with the range A1:A20 representing the field’s scores in one game and A10 representing one bowler’s score in that game. Ties against the field are similarly calculated (using = instead of <), but be sure to subtract 1 from the function’s result, as the function result will include the cell containing the bowler’s score. Wins being worth one and ties worth .5, you'd calculate the percentage against the field from there.
_________________________
Arsenal (all 15 lb.)
Alpha Crux
Marvel Pearl
IQ Tour Fusion
Reign of Fire
Tropical Breeze
Fast
T-Zone

Career Highs
Avg.: 197 (Summer 2008)
Book: 186
Series: 707
Game: 288

Current
Brunswick Mixed - Avg: 168 HS: 615 HG: 255
Composite avg. (w/ Sun. night sub): 170

The Tenth Board: My bowling blog
Richie's BowlSK profile

Top
Page 3 of 3 < 1 2 3



Moderator:  Angel, Community Manager 
Savings That Support BowlingCommunity.com
We need your help!
Rather than begging for donations we're asking you to do one simple thing to help keep these forums running smooth:
When shopping for anything on Amazon.com or eBay please use these links to go to the web sites.

This won't cost you a cent!
You'll still get the exact same low prices, deals and free or low cost shipping; it doesn't change anything for you at all! The items do not have to be bowling related; all purchases made through these links help us! Amazon.com and eBay will pay us a small commission for every sale and it's helping us cover the expenses.

BowlingCommunity.com Recent Posts
Bowler turns 100
by Dennis Michael - 11/19/19 05:04 PM
Our Bowling Ally is now Chuckie Cheese
by djp1080 - 11/18/19 07:59 PM
Major News in the Industry
by Mkirchie - 11/17/19 04:35 PM
Good morning everyone...
by djp1080 - 11/17/19 12:50 PM
Plastic or Urethane?
by Dennis Michael - 11/17/19 12:45 PM
What is the best ball to get?
by djp1080 - 11/10/19 09:02 PM
Which fork on the Hy-Road should I take?
by Mkirchie - 11/02/19 07:05 AM
End of Days
by BOSStull - 11/02/19 06:44 AM
Terms Of Use
Use of this community signifies your agreement to the Community Standards and Conditions of Use.

About BowlingFans.com | Contact Us | Advertise With Us | Site Map
Use of this website constitutes acceptance of our Terms of Use and Privacy Policy. | Material Connection Disclosure

Copyright © 1998 - 2019 - usrbingeek LLC | Copyright Policy
BowlingFans.com, BowlingFans, The Right Approach, Kegler's Connection, Tour411, BallBeat, BowlingCommunity.com, BowlSearch.com, and Bowling News You Can Use are trademarks of usrbingeek LLC. All other trademarks and tradenames are property of their respective owners.