I've been planning this thread for some time.

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)

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.

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.

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).

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

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.



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.

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

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"

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).

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!