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.

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)

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)