Calculatng bowling averages

Howdy,
Cells a1, a2, a3 contain bowling scores.
Cell a4 is the total of a1:a3
Cell a5 is the total in a4 divided by 3 (total games bowled so far) equaling the bowler's average
that week.

Cells b1, b2, b3 contain the next week's bowling scores.
Cell b4 is the total of b1:b3

NOW, in cell b5, I want the the total pins felled from cell a4 PLUS the total pins felled in cell b4
divided by 6 (total games bowled so far).

I know how to set THIS up, what I don't know is how to extend this formula easily for the next 28
weeks! When I drag the formula, it no longer starts with the data in a4. It jumps ahead by one
column each week. I have to manually change the range of cells I want to add together then change
the number that is divided into that range.

Here is a sample starting in cell a4. I entered this manually. It's the formula in row 5 that is
giving me the fits. How can this be done with dragging the cells or entering a different formula?

Thanks....(I'm outta breath now....)

Michael



=SUM(A1:A3) =SUM(B1:B3) =SUM(C1:C3) =SUM(D1:D3) =SUM(E1:E3)
=SUM(A4)/3 =SUM(A4:B4)/6 =SUM(A4:C4)/9 =SUM(A4:D4)/12 =SUM(A4:E4)/15

Hi!

Try this in cell A5:

=IF(A1="","",ROUND(SUM($A$1:A3)/COUNT($A$1:A3),0))

Copy across as needed.

The formula will return blank until an entry is made in
the cells in row 1.

This is also more flexible than using a hardcoded divisor
that you would need to change if someone didn't bowl all 3
games.

Biff

>-----Original Message-----
>Howdy,
>Cells a1, a2, a3 contain bowling scores.
>Cell a4 is the total of a1:a3
>Cell a5 is the total in a4 divided by 3 (total games
bowled so far) equaling the bowler's average
>that week.
>
>Cells b1, b2, b3 contain the next week's bowling scores.
>Cell b4 is the total of b1:b3
>
>NOW, in cell b5, I want the the total pins felled from
cell a4 PLUS the total pins felled in cell b4
>divided by 6 (total games bowled so far).
>
>I know how to set THIS up, what I don't know is how to
extend this formula easily for the next 28
>weeks! When I drag the formula, it no longer starts with
the data in a4. It jumps ahead by one
>column each week. I have to manually change the range of
cells I want to add together then change
>the number that is divided into that range.
>
>Here is a sample starting in cell a4. I entered this
manually. It's the formula in row 5 that is
>giving me the fits. How can this be done with dragging
the cells or entering a different formula?
>
>Thanks....(I'm outta breath now....)
>
>Michael
>
>
>
>=SUM(A1:A3) =SUM(B1:B3) =SUM(C1:C3) =SUM(D1:D3)
=SUM(E1:E3)
>=SUM(A4)/3 =SUM(A4:B4)/6 =SUM(A4:C4)/9 =SUM
(A4:D4)/12 =SUM(A4:E4)/15
>
>
>
>.
>

On Sun, 13 Mar 2005 03:38:43 GMT, [email protected] (Michael) wrote:

>Howdy,
>Cells a1, a2, a3 contain bowling scores.
>Cell a4 is the total of a1:a3
>Cell a5 is the total in a4 divided by 3 (total games bowled so far) equaling the bowler's average
>that week.
>
>Cells b1, b2, b3 contain the next week's bowling scores.
>Cell b4 is the total of b1:b3
>
>NOW, in cell b5, I want the the total pins felled from cell a4 PLUS the total pins felled in cell b4
>divided by 6 (total games bowled so far).
>
>I know how to set THIS up, what I don't know is how to extend this formula easily for the next 28
>weeks! When I drag the formula, it no longer starts with the data in a4. It jumps ahead by one
>column each week. I have to manually change the range of cells I want to add together then change
>the number that is divided into that range.
>
>Here is a sample starting in cell a4. I entered this manually. It's the formula in row 5 that is
>giving me the fits. How can this be done with dragging the cells or entering a different formula?
>
>Thanks....(I'm outta breath now....)
>
>Michael
>
>
>
>=SUM(A1:A3) =SUM(B1:B3) =SUM(C1:C3) =SUM(D1:D3) =SUM(E1:E3)
>=SUM(A4)/3 =SUM(A4:B4)/6 =SUM(A4:C4)/9 =SUM(A4:D4)/12 =SUM(A4:E4)/15
>
>

Well, another approach:

In A5 enter the formula

=AVERAGE($A$1:C1)

and drag it down as far as needed.

If you want to suppress output if there are no games in the row, then use:

=IF(COUNT(A1:C1)<>3,"",AVERAGE($A$1:C1))


--ron

Well I sure do appreciate the responses! My old brain would have never even thought of formulas
like these!

Very much appreciated!
Michael



On Sun, 13 Mar 2005 08:31:10 -0500, Ron Rosenfeld <[email protected]> wrote:

>On Sun, 13 Mar 2005 03:38:43 GMT, [email protected] (Michael) wrote:
>
>>Howdy,
>>Cells a1, a2, a3 contain bowling scores.
>>Cell a4 is the total of a1:a3
>>Cell a5 is the total in a4 divided by 3 (total games bowled so far) equaling the bowler's average
>>that week.
>>
>>Cells b1, b2, b3 contain the next week's bowling scores.
>>Cell b4 is the total of b1:b3
>>
>>NOW, in cell b5, I want the the total pins felled from cell a4 PLUS the total pins felled in cell b4
>>divided by 6 (total games bowled so far).
>>
>>I know how to set THIS up, what I don't know is how to extend this formula easily for the next 28
>>weeks! When I drag the formula, it no longer starts with the data in a4. It jumps ahead by one
>>column each week. I have to manually change the range of cells I want to add together then change
>>the number that is divided into that range.
>>
>>Here is a sample starting in cell a4. I entered this manually. It's the formula in row 5 that is
>>giving me the fits. How can this be done with dragging the cells or entering a different formula?
>>
>>Thanks....(I'm outta breath now....)
>>
>>Michael
>>
>>
>>
>>=SUM(A1:A3) =SUM(B1:B3) =SUM(C1:C3) =SUM(D1:D3) =SUM(E1:E3)
>>=SUM(A4)/3 =SUM(A4:B4)/6 =SUM(A4:C4)/9 =SUM(A4:D4)/12 =SUM(A4:E4)/15
>>
>>
>
>Well, another approach:
>
>In A5 enter the formula
>
> =AVERAGE($A$1:C1)
>
>and drag it down as far as needed.
>
>If you want to suppress output if there are no games in the row, then use:
>
> =IF(COUNT(A1:C1)<>3,"",AVERAGE($A$1:C1))
>
>
>--ron

That is one super-duper formula! It worked and I appreciate your time!
You just made bowling more fun as well as Excel!

Michael


On Sat, 12 Mar 2005 21:22:16 -0800, "Biff" <[email protected]> wrote:

>Hi!
>
>Try this in cell A5:
>
>=IF(A1="","",ROUND(SUM($A$1:A3)/COUNT($A$1:A3),0))
>
>Copy across as needed.
>
>The formula will return blank until an entry is made in
>the cells in row 1.
>
>This is also more flexible than using a hardcoded divisor
>that you would need to change if someone didn't bowl all 3
>games.
>
>Biff
>
>>-----Original Message-----
>>Howdy,
>>Cells a1, a2, a3 contain bowling scores.
>>Cell a4 is the total of a1:a3
>>Cell a5 is the total in a4 divided by 3 (total games
>bowled so far) equaling the bowler's average
>>that week.
>>
>>Cells b1, b2, b3 contain the next week's bowling scores.
>>Cell b4 is the total of b1:b3
>>
>>NOW, in cell b5, I want the the total pins felled from
>cell a4 PLUS the total pins felled in cell b4
>>divided by 6 (total games bowled so far).
>>
>>I know how to set THIS up, what I don't know is how to
>extend this formula easily for the next 28
>>weeks! When I drag the formula, it no longer starts with
>the data in a4. It jumps ahead by one
>>column each week. I have to manually change the range of
>cells I want to add together then change
>>the number that is divided into that range.
>>
>>Here is a sample starting in cell a4. I entered this
>manually. It's the formula in row 5 that is
>>giving me the fits. How can this be done with dragging
>the cells or entering a different formula?
>>
>>Thanks....(I'm outta breath now....)
>>
>>Michael
>>
>>
>>
>>=SUM(A1:A3) =SUM(B1:B3) =SUM(C1:C3) =SUM(D1:D3)
> =SUM(E1:E3)
>>=SUM(A4)/3 =SUM(A4:B4)/6 =SUM(A4:C4)/9 =SUM
>(A4:D4)/12 =SUM(A4:E4)/15
>>
>>
>>
>>.
>>