Bowling applications of Excel: A primer

Posted by: Richie V.

Bowling applications of Excel: A primer - 05/05/13 09:49 PM

I've been planning this thread for some time. smile

I assume most of you have at least a basic knowledge of Excel, and, for these purposes, I will also assume you have Excel 2010, which is the version I use.

My first post will address the basic formulas and functions used when tracking your scores. Obviously, the SUM and AVERAGE functions are involved, but, if you're building a log that doesn't track fractions of a whole pin in it, you do have the TRUNC function available to you, making the formula you'll use for the average column as follows:

TRUNC(AVERAGE(range))

where range is the range of cells containing your games.

Also, remember that formulas can be copied and pasted as you add scores, but the pitfall is related to cell references, of which there are two types, relative and absolute. A normal cell reference of D3 is relative, so when you copy down the formula AVERAGE(D3:F3), it will paste as AVERAGE(D4:F4) and only give you that night's average. To get your full average, you'll need an absolute reference to D3, entered in the formula as $D$3.

Finally, a lot of you might want to track what series score you need next week to raise your average to the next full pin. The formula for calculating it is more involved, but exists, as follows:

(TRUNC(AVERAGE(range))+1)*(COUNT(range)+3)-SUM(range)

Trust me, it's tricky for me to build it...it does involve your knowledge of pre-algebra math (namely the order of operations). The 3 in this formula can be replaced with whatever number of games you bowl in a series (4 for most of you for whom it is not 3)
Posted by: Richie V.

Re: Bowling applications of Excel, a primer - 05/05/13 10:10 PM

In my prototype bowling log that I'll base next season's log off of, I also track per-pair averages. First, I'll assume you have the pairs in column A, the games in columns B, C, and D, and the series totals in column E. In a separate part of the worksheet, put the pairs again in column A. There is an AVERAGEIF function in Excel, but this won't work, so you need to build the following formulas in columns B and C:

B: COUNTIF($A$x:$A$y,Az)*3
C: SUMIF($A$x:$E$y,Az,$Ex:$Ey)

Where x and y are the boundary rows of your games and z is the row containing the pair you wish to calculate the average for. How do you get the average? Just do normal division on cells Bz and Cz, thus Cz/Bz, and putting the result in Dz. smile
Posted by: Richie V.

Re: Bowling applications of Excel, a primer - 05/05/13 10:26 PM

This post will address what to do if you want to make cells with certain types of values (like your high game or series) stand out. Conditional formatting is what this is all about, and Excel 2010 has a button for conditional formatting on the "Home" tab on the ribbon. I won't go into all of the rules, but conditional formatting can be used to make, say, the top 3 games stand out in a worksheet you might use to run pot games. If you want to make #1, #2, #3 each a different color (if you're paying the top 3, for instance), you can use the LARGE function with the place as the second argument. The conditional format can then be pasted into other ranges with the Format Painter.

While we're on pot game sheets, if you're paying for series total and would like to be able to tell your bowlers how many pins behind the lead they are, remember you can use the MAX function in a formula to get the maximum value in the range with the totals, such as MAX(G1:G10) to build a MAX(G1:G10)-Gn formula (n being the row with the individual bowler's total) to get each bowler's pins behind the lead. The sheet can then be sorted in ascending order on the column with the formula to make life easier on yourself.
Posted by: Richie V.

Re: Bowling applications of Excel, a primer - 05/05/13 10:38 PM

My last post will assume you are familiar with Excel's charting features. Some of you may want to make a bar chart showing how frequently you've bowled certain scores. First, look over your games to get a general idea of what range your games fall in, then put a range of scores into a column (In my log, I started with 125 and went in 25 pin increments to 250). In the adjacent column, select the top cell, click the "Formulas" tab on the ribbon, the "Other Functions" button, mouse over "Statistical" and find and choose the "Frequency" function. Select the range with your games, then click the other box and select the range with your game score values (in the adjacent column). Finally, press F2, then CTRL+SHIFT+ENTER to paste the formula as an array. You may then build your chart (don't forget to remove the game scores as values in the chart and make those labels on the horizontal axis).
Posted by: Richie V.

Re: Bowling applications of Excel, a primer - 05/05/13 11:55 PM

I did forget one thing...some of you may want to track over/under, be it over/under average, or over/under "par" (meaning the traditional 200 in tournaments). If you're doing the former, and you have, say, your average as of last week in Gx and this week's total in Fy, subtract TRUNC(Gx)*3 from Fy to get the differential. To get the + and - to show in the column, you'll need this custom number format:

+# ##0;-# ##0
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/06/13 03:45 AM

Originally Posted By: Richie V.
Also, remember that formulas can be copied and pasted as you add scores, but the pitfall is related to Cell references, of which there are two types, relative and absolute. A normal Cell reference of D3 is relative, so when you copy down the formula AVERAGE(D3:F3), it will paste as AVERAGE(D4:F4) and only give you that night's average. To get your full average, you'll need an absolute reference to D3, entered in the formula as $D$3.


Relative cell references have their place, as well: If you're doing any kind of floating average (I use my last 9 games in my log), relatively reference the first cell of the range and the first week of last week's floating average will be removed from this week's floating average when the formula is copied and pasted.

Originally Posted By: Richie V.
Finally, a lot of you might want to track what series score you need next week to raise your average to the next full pin. The formula for calculating it is more involved, but exists, as follows:

(TRUNC(AVERAGE(range))+1)*(COUNT(range)+3)-SUM(range)


Of course, if you calculated your average in another cell in a worksheet you're using this formula in, it is permissible to replace the AVERAGE function in this formula with a reference to the cell your average is in; the same goes for the SUM function if you calculated your to-date pinfall in another cell.
Posted by: FuriousSam

Re: Bowling applications of Excel: A primer - 05/07/13 02:10 AM

Thanks for the Excel lessons Richie! I didn't really think about a flowing average on my spreadsheet. With that being said, this is what I use so far: Bowling Stats

I just enter in the dates and the games. If I were to miss a week, I put something under the Absent column and it skips/keeps the current average (it's been a while).

-- Sam
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/08/13 03:41 AM

I hope you got something out of this thread, FuriousSam. If you have any questions about something with Excel, post (this goes for anyone else, too).

"floating" in my previous post should really be "moving" smile
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/08/13 04:06 AM

I keep learning stuff...you can also display a 0 value in your over/under as the letter E with this number format:

+# ##0;-# ##0;"E"

Showing under (negative values) as red text is possible, too:

+# ##0;[Red]-# ##0;"E"

Also, I wanted people to view my prototype log so they know how I have stuff set up, but was unable to attach it to the thread b/c the board does not allow *.xlsx attachments. The next best thing, I remembered, was my Google Drive, so I'm linking the file below:

https://docs.google.com/file/d/0BzkP1Ib9UJcMU2VZelFwNHduWW8/edit?usp=sharing

The file will open in Google Docs in your browser, but you can do a File>Download (File menu can be found under the file name) and be able to open it in Excel (I'm not sure about Excel 2007, but Excel 2010 will open it in "protected" mode). smile
Posted by: sk8shorty01

Re: Bowling applications of Excel: A primer - 05/08/13 03:00 PM

Richie, you can also make excel pick your range based on your rows but I cannot remember the exact formula to do so.

I do know what it does is count the rows, starting from the top until it finds a row without any info within and then subtracts one to get the total range.

So that would allow you to not have to change your range values if you would like that to be a little more automated. Just a heads up.

If you ever need help, visit excelforum.com and the forums there are awesome!
Posted by: Dan300

Re: Bowling applications of Excel: A primer - 05/08/13 03:22 PM

I'll post a link to my own google spreadsheet. I implement many of the techniques Richie outlines in this post.

https://docs.google.com/spreadsheet/ccc?...amp;usp=sharing
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/08/13 04:24 PM

sk8, I've also found that, after a time, when I enter my games and tab over to my total column, it will automatically insert the formula to sum the columns in that row.
Posted by: FuriousSam

Re: Bowling applications of Excel: A primer - 05/08/13 10:49 PM

Originally Posted By: Richie V.
I hope you got something out of this thread, FuriousSam. If you have any questions about something with Excel, post (this goes for anyone else, too).

"floating" in my previous post should really be "moving" smile


Generally speaking, what's a good range for "moving" averages? 3 weeks? 5 weeks?

-- Sam
Posted by: sk8shorty01

Re: Bowling applications of Excel: A primer - 05/09/13 08:19 AM

I would say the last 21 games is a good round number. There must be a reason the USBC selected that number as what it takes to classify your average as a "real" book average.
Posted by: Mkirchie

Re: Bowling applications of Excel: A primer - 05/09/13 10:13 AM

Richie, thanks mentioning floating averages. For some reason, I never thought to include it in my sheet. I still need to get around to adding average +/- 1 to my sheet, but I haven't felt like tinkering around with it lately. One thing that I have on mine is tracking the average for each individual game in a series, so I have a game 1 average, a game 2 average, and a game 3 average. All of my game scores are all in the same column due to how my sheet is structured, so it was trickier to take the average for every 3rd game. I forget exactly how I accomplished this, I looked it up on excelforum myself and I don't have the file on me right now, but I know it does involve arrays. I tend to tweak it over the summer when I'm not adding league scores.

Mark
Posted by: sk8shorty01

Re: Bowling applications of Excel: A primer - 05/09/13 10:16 AM

I tend to just use bowlsk to do all my stat tracking lol.
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/09/13 02:35 PM

sk8, I use 9 games (3 weeks) as my moving average b/c that's the number of games most leagues require to establish your average in the league.

Mkirchie, if you go to my log (I linked it in a previous post), you'll see I do Game 1, Game 2, etc., averages, as well.
Posted by: Mkirchie

Re: Bowling applications of Excel: A primer - 05/09/13 04:29 PM

Originally Posted By: Richie V.
Mkirchie, if you go to my log (I linked it in a previous post), you'll see I do Game 1, Game 2, etc., averages, as well.

Guess it would have helped if I scrolled down to the bottom. I put my average and other such stats at the top of the sheet to the right of my scores.

Mark
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/09/13 04:55 PM

Originally Posted By: Mkirchie
Originally Posted By: Richie V.
Mkirchie, if you go to my log (I linked it in a previous post), you'll see I do Game 1, Game 2, etc., averages, as well.

Guess it would have helped if I scrolled down to the bottom. I put my average and other such stats at the top of the sheet to the right of my scores.

Mark


I just thought at the bottom of the respective columns would be a natural place to do the game averages. smile
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/11/13 12:45 AM

A thing to keep in mind when setting up per-pair averages is that you can bold your starting lane (I did in my prototype log), but the COUNTIF and SUMIF functions only look at the value in the cell and disregard formatting.
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 05/16/13 05:41 PM

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.
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 09/05/13 05:16 PM

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 ;;;
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 01/02/14 09:07 PM

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.
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 02/27/14 09:02 PM

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
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 08/09/14 07:20 PM

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.
Posted by: mmalsed

Re: Bowling applications of Excel: A primer - 08/09/14 08:08 PM

I think a lot of the functions you use aren't in Google Sheets or LibreOffice. frown
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 08/13/14 04:27 AM

mmalsed, maybe stuff like formatting is done differently in Google Sheets and LibreOffice, but the formulas and functions absolutely work in both.
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 09/09/15 05:50 AM

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
Posted by: Richie V.

Re: Bowling applications of Excel: A primer - 09/07/16 03:47 PM

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.