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




ChatBox:

Sponsored Links


Page 1 of 3 1 2 3 >
Topic Options
Rate This Topic
#178498 - 05/05/13 09:49 PM Bowling applications of Excel: A primer
Richie V. Offline
Virtual League Secretary,
Virtual League Champion x4


Registered: 02/21/08
Posts: 4567
A/S/L: 51/M/Brockton, MA
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)


Edited by Richie V. (05/05/13 11:04 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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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
#178499 - 05/05/13 10:10 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: 4567
A/S/L: 51/M/Brockton, MA
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
_________________________
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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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

Top
#178500 - 05/05/13 10:26 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: 4567
A/S/L: 51/M/Brockton, MA
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.


Edited by Richie V. (05/05/13 10:29 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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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

Top
#178501 - 05/05/13 10:38 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: 4567
A/S/L: 51/M/Brockton, MA
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).
_________________________
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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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

Top
#178502 - 05/05/13 11:55 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: 4567
A/S/L: 51/M/Brockton, MA
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


Edited by Richie V. (05/06/13 12:00 AM)
_________________________
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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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

Top
#178504 - 05/06/13 03:45 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: 4567
A/S/L: 51/M/Brockton, MA
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.


Edited by Richie V. (05/06/13 04:10 AM)
_________________________
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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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

Top
#178523 - 05/07/13 02:10 AM Re: Bowling applications of Excel: A primer [Re: Richie V.]
FuriousSam Offline
Action Bowler

Registered: 09/29/03
Posts: 299
A/S/L: 32/M/Lodi, NJ
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
_________________________
Bowling keeps me sane, yet I am crazy about bowling...

Motiv Forza
Motiv Venom Shock
White Dot

BowlSK

Top
#178531 - 05/08/13 03:41 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: 4567
A/S/L: 51/M/Brockton, MA
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
_________________________
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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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

Top
#178532 - 05/08/13 04:06 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: 4567
A/S/L: 51/M/Brockton, MA
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


Edited by Richie V. (05/08/13 04:26 AM)
_________________________
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: 167 HS: 556 HG: 207
Sunday Niters - Avg: 178 HS: 610 HG: 257
Composite Avg: 172

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

Top
#178540 - 05/08/13 03:00 PM Re: Bowling applications of Excel: A primer [Re: Richie V.]
sk8shorty01 Offline
Virtual League Champion x2

Registered: 01/05/09
Posts: 5159
A/S/L: 30/M/Merritt Island, FL
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!
_________________________
Storm Amatuer Staff Member
BowlersMart.com Staff Member
Logo Infusion Staff Member
IAB Staff Member

PBA Member
USBC Level 1 Certified Coach
Head Coach - Cocoa High School

Career 300 games - 5
Career 800 series - 6
High Scratch Series - 823

Top
Page 1 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
Possible Olympic Events?
by SteveH - 0 seconds ago
Fall/Winter Leagues 2017-18 thread
by wronghander - 47 minutes 21 seconds ago
Storm/Roto Grip MatchMaker Live
by BOSStull - Today at 05:35 AM
approved ball cleaners
by Dennis Michael - Today at 03:20 AM
wanted a 1 ball roller bowling bag
by Richie V. - 10/20/17 06:16 PM
trouble sliding
by Rand - 10/20/17 01:30 AM
HY ROAD NANO
by djp1080 - 10/19/17 07:05 PM
Starting dots/boards?
by djp1080 - 10/19/17 01:42 PM
Storm Hy-Road Surface
by SteveH - 10/18/17 10:06 PM
2017 World Championships (Men & Women)
by Richie V. - 10/13/17 04:43 AM
Finger Swelling
by Dennis Michael - 10/12/17 06:44 AM
People who talk [email protected]#$
by goobee - 10/11/17 09:51 PM
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 - 2017 - 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.