Bowling Game

[Roger King]

Hi,
I'm taking a class in "basic" excel and one of the projects is to program a
spreadsheet to score a bowling game. Am doing fine, except...
How do I get excel to check for strikes and spares. I have an IF(AND)
statement in the first frame that looks for strikes in frames 1 thru 3,
enters 30 if true and one frame's value if false. I cannot seem to get excel
to accept the spare character (/) as it is an operator. My plan was to have
this formula in each frame, checking ahead and calculating as data is
entered. I am trying to use one cell per ball, i.e. cell B3 is the first
ball, c3 the second, and so on. The score tallys in B4-C4 merged, D4-E4,etc.
Does any of this make sense? Am I way off base? Seems if this is a "basic"
class, this shouldn't be this complicated. What am I missing?
Thanks in advance for your advise and assistance
-roger-

 

[Richard Buttrey]

Hi,
I'm taking a class in "basic" excel and one of the projects is to program a
spreadsheet to score a bowling game. Am doing fine, except...
How do I get excel to check for strikes and spares. I have an IF(AND)
statement in the first frame that looks for strikes in frames 1 thru 3,
enters 30 if true and one frame's value if false. I cannot seem to get excel
to accept the spare character (/) as it is an operator. My plan was to have
this formula in each frame, checking ahead and calculating as data is
entered. I am trying to use one cell per ball, i.e. cell B3 is the first
ball, c3 the second, and so on. The score tallys in B4-C4 merged, D4-E4,etc.
Does any of this make sense? Am I way off base? Seems if this is a "basic"
class, this shouldn't be this complicated. What am I missing?
Thanks in advance for your advise and assistance
-roger-

I don't play ten-pin bowls myself, (only English Crown Green bowls),
so am not familiar with the scoring system. If you'd like to write the
rules for a couple of typical frames, and any special conditions which
might apply in the last frame, then I'll attempt to suggest an Excel
interpretation.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[joeu2004]

I cannot seem to get excel to accept the spare
character (/) as it is an operator.

Enter '/ -- that is, single-quote slash.

 

[Harlan Grove]

Roger King wrote...

I'm taking a class in "basic" excel and one of the projects is to program a
spreadsheet to score a bowling game. Am doing fine, except...
How do I get excel to check for strikes and spares. I have an IF(AND)
statement in the first frame that looks for strikes in frames 1 thru 3,
enters 30 if true and one frame's value if false. I cannot seem to get excel
to accept the spare character (/) as it is an operator. My plan was to have
this formula in each frame, checking ahead and calculating as data is
entered. I am trying to use one cell per ball, i.e. cell B3 is the first
ball, c3 the second, and so on. The score tallys in B4-C4 merged, D4-E4,etc.
Does any of this make sense? Am I way off base? Seems if this is a "basic"
class, this shouldn't be this complicated. What am I missing?

As for entering /, run the menu command Tools > Options, select the
Transition tab, clear the entry for 'Microsoft Excel menu or Help key'.

As for your formula for strikes, if you bowl strikes in both first and
second frames then knock over only 4 pins with your first ball in the
third frame, your score in the first frame is 24, not 30.

The only clean way to handle bowling scores in Excel is recording each
ball, so you'd need to accept two entries per frame for first through
ninth frames and three balls in tenth frame. Your formulas should deal
with the simple case first (neither strike nor spare), then the next
simplest (spare, in which case you need only the next ball, which is
necessarily the first ball in the frame, so it's 0-9 or X, and you only
need to treat X as a special case), and finally the most complicated
(strike, in which case you need the next two balls, not the next two
frames, and the first of these can only be 0-9 or X, and if 0-9, then
the second can only be 0-9 or /, otherwise if the first ball is X, then
the next ball can only be 0-9 or X).

Since this is for a course, this seems all it's fair to give you.

 

[Odawg]

Hey Roger,

Try this:

=IF(ISNUMBER(C9),B9+C9,IF(C9="/",IF(E9="X",20,IF(ISNUMBER(D9),10+D9,"")),IF(C9="X",IF(E9="X",IF(G9="X",30,IF(ISNUMBER(F9),20+F9,"")),IF(E9="/",20,IF(ISNUMBER(E9),10+D9+E9,""))),"")))