Spreadsheet for bowling games?

[LizandraT]

I'm trying to keep track of a league's scores, but would like to keep the
frame-by-frame scores as well. I don't know how to get the correct logic to
work for the entire game (mainly because I know very little about Excel), but
have found an example that works up until the last frame. Then it only works
if you happen to get a spare or strike in the 10th frame, otherwise there's a
#NAME error. The code looks like this for the 10th frame only:

=IF(V4="X",IF(W4="X",IF(X4="X",T5+30,IF(ISNUMBER(X4),T5+20+X4,"")),IF(ISNUMBER(W4),IF(X4="X",T5+20+W4,IF(ISNUMBER(X4),T5+10+W4+X4,"")),"")),IF(ISNUMBER(V4),IF(W4="/",IF(X4="X",T5+20,IF(ISNUMBER(X4),T5+10+X4,"")),is(ISNUMBER(W4),T5+V4+W4,""))))

Since I know so little about Excel logic, I can't tell where the hang up is.

Any help would be greatly appreciated!

 

[T. Valko]

there's a #NAME error.

...is(ISNUMBER(W4),T5+V4+W4,""))))

I'll bet ya a Coke that "is" is supposed to be IF !

There is a sample bowling file here:

ftp://members.aol.com/hrlngrv/

 

[LizandraT]

I guess I owe you a Coke!!! Thank you so much!!!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

I guess I owe you a Coke!!! Thank you so much!!!

 

[LizandraT]

Ok, I'm trying to make some more adjustments. Would you mind sharing some
more info? Would it be better to take that off-list?

Rachel

 

[Spiky]

If you take it off the list, how will anyone else benefit?

 

[T. Valko]

Hmmm...

Not sure what you mean.

 

[LizandraT]

Guess I was figuring that not everyone might be interested in a bowling
scorecard and I should leave the board a bit more open for the important
stuff. (And I might feel less like a nervous little kid up in front of a big
crowd...) ;-)

 

[LizandraT]

Just that I have a few more changes to the codes that I'd like to make (but
don't really need to) and I was wondering if I might be able to get your
input on those. Since I doubt bowling scorecards are in very high demand on
here, I was wondering if it might be best for me to ask those questions in
private emails instead of holding up traffic on here.

 

[T. Valko]

Feel free to ask any Excel questions you want. That's what this place is
for!

 

[LizandraT]

That's very true! I'll start asking tomorrow, I think. It's been a heckuva
day... Thanks! I'll be talking to you later!

Rachel

 

[LizandraT]

Ok, here's the current scenario: I’ve got about 4 different things I’d like
to be able to change in the current code.

1.) Any gutter balls must currently be input as “0†and I’d like to be able
to use the standard “-â€. Will that be complicated?
2.) Whenever I try to type a spare “/â€, it does not appear in the box and
the cursor jumps up to the File tab. The only way to type it in is to double
click on the individual cells. Is there a way to stop that? I know others
that don’t have this problem.
3.) The last frame isn’t accepting spares as “/â€. I have to type in the
number. I’m sure it has something to do with the coding, but as I really
don’t know how to read it or write it very well at all, I’m not sure what
needs to be tweaked.
4.) I’d like to be able to find the daily averages and also average the most
recent 15 games or so. Is there a way to do that without too much trouble?

I’d like to be able to upload an attachment of a blank worksheet, but don’t
see where to do that on here. Do I have to just input each different code
into a field like this instead?

Thanks!

Rachel

 

[T. Valko]

Answers:

1. probably!

2. This can be handled 2 ways. You can precede the / entry with an
apostrophe like this: '/. The apostrophe will not be displayed in the cell.
The other method requires you to turn off/change a setting. Navigate to
Tools>Options>Transition tab. Under Settings>Microsoft Excel menu or help
key, either change that setting from the / to new character or delete the /
character and leave that empty. Just know that leaving that empty you will
not be able to access the menus with a hot key.

3. complicated!

4. depends on your layout

Question: Did you download the sample file from the link I provided in my
other reply? The formula for the 10th frame in that file is exactly the same
formula you posted (including the "is" should be IF error).

The author of this file is an Excel "guru" if there ever was one and I
suspect that there's a reason the / can't be used in the last cell of the
last frame. Accounting for it probably exceeds the nested function limit.

If you want to send me a copy of your file I'll see what I can do. No
guarantees!

I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

 

[T. Valko]

Update!

I just thought of something wrt #1, entering a dash for gutter balls...

You can use a custom number format of General;General;-;General.

You still need to enter the gutter ball as 0 but it will display as a dash.

 

[LizandraT]

Yay! Your suggestion for #2 about the ' before the / works perfectly! The
file you had suggested was what I had started with way back when, so great
minds must think alike! I’ll send a file that has one person's set of scores
as of a few weeks ago since I've kind of been slacking on entering the new
stuff. I'll also throw on an extra game on the end of it that has the
funkiness in the 10th frame. Right now I’ve got one file with a different
sheet for each person’s scores, so I’ve got about 12 sheets at the moment.
Oy!

Rachel