Excel Spreadsheet for Match Scorekeeping?

[Killick]

Having a little difficulty creating a spreadsheet on excel that will total point score and X count as a formula. Is there someone on this site that can point me in the correct direction?

 

[Stumpy1]

Here is one way to do it. You just enter each match score as the score, a decimal point, and the x count.

Example: 189-5X is 189.05
Do three columns that way and then the formula =sum(A2, B2, C2) and you will have the score and the decimal points will be the X count Example: 589.23

IF you are shooting less than 100 shots per match, you need to zero pad the decimal one digit, so a 190-10X is entered as 190.10 and a 190-2X is entered as 190.02

I am attaching a 3 x 600 scoresheet in excel

Shout out if you need more help, or if I completely missed the point.

Frank

 

[Doug Beach]

Or enter the score, and x’s in separate cells, and sum accordingly.

 

[kzin]

100-10 format and separate sums

 

[Killick]

Here is one way to do it. You just enter each match score as the score, a decimal point, and the x count.

Example: 189-5X is 189.05
Do three columns that way and then the formula =sum(A2, B2, C2) and you will have the score and the decimal points will be the X count Example: 589.23

IF you are shooting less than 100 shots per match, you need to zero pad the decimal one digit, so a 190-10X is entered as 190.10 and a 190-2X is entered as 190.02

I am attaching a 3 x 600 scoresheet in excel

Shout out if you need more help, or if I completely missed the point.

Frank

Frank,
I like it. And if I want to keep a tally for a whole season I can just do 3 decimals.

 

[Turbulent Turtle]

Sorry to be so late to answer; Hey, it's the weekend.

If you want to be able to enter the score as "189-5x", here is a formula to convert that to 189.05 automagically:
The input is in B1.
=VALUE(LEFT(B1,FIND("-",B1)-1)&IF((LEN(B1)-FIND("-",B1)-1)>1,".",".0")&MID(B1,FIND("-",B1)+1,LEN(B1)-FIND("-",B1)-1))

I you want to convert that number back to 189-5x format, this is the formula:
The value to convert is in c1:
=TEXT(INT(C1),"####")&"-"&TEXT((C1-INT(C1))*100,"##")&"X"

Of course, you can get fancier and have the formula accept 189-5X or 189.05.
The input value is in B2:

=IF(ISNUMBER(B2),B2,VALUE(LEFT(B2,FIND("-",B2)-1)&IF((LEN(B2)-FIND("-",B2)-1)>1,".",".0")&MID(B2,FIND("-",B2)+1,LEN(B2)-FIND("-",B2)-1)))

Here is a little spreadsheet with the formula. Just change the values in b1 or b2.

I suggest you keep the scores in nnn.nn format as it makes it a lot easier to keep track, add up and so on. If you store it in nnn-nnX, it would be like doing accounting in Roman numerals.

 

[Brian H]

Our club is working a program built in access thatll auto populate all the categories and classifications as well. Pm me if you want to know more.

 

[Gary MacGuyver]

Frank,
I like it. And if I want to keep a tally for a whole season I can just do 3 decimals.

I like this-I'm going to use it.

 

[Thunderstorm]

Has anyone done a season-long aggregate spreedsheet? I'm looking to take the top 4 scores for the year and come up with a year championship top shooters.

 

[prwhite]

How do you keep the trailing zero visible throughout the spreadsheet? I have instances where a number like 196.10 shows as 196.1. It’s not a problem until a report is printed and the shooter sees their agg. as 591.3.

 

[Stumpy1]

At the top of your spreadsheet, approximately in the middle, you will see this. Two 'buttons' on the right side of the pic. One button will increase the decimal points visible, the other will decrease the decimal points visible.

Hope this helps,

Frank

 

[6ShotsOr5?]

How do you keep the trailing zero visible throughout the spreadsheet? I have instances where a number like 196.10 shows as 196.1. It’s not a problem until a report is printed and the shooter sees their agg. as 591.3.

You need to use a 2 decimal number format assigned to all the cells that you want to display this way. The one that is displaying as 196.1 is a general number format. Read about number formats and how to change them in the Excel help system.

 

[6ShotsOr5?]

I also use the system with one, 2 decimal number such as 50 and 3 X being entered as 50.03, and the scoring cells formatted as “#00.00”.

It’s useful to know that you don’t have to type the “.00” part to enter score with no X’s. Example for a score of 48 and 0 X’s, you just enter “48”.

I like to use the data validation features in Excel. If the maximum score on a target is 50.05, you can have Excel alert you if a number greater than 50.05 was entered - that must have been a mistake.

Another common error is that people forget the extra 0 so three X’s are entered as “.3” instead of “.03” for example. You can use a conditional format that is checking for a decimal part greater than .05, and use red shading in the input cell to alert you that you entered the value wrong. Use the MOD function to in the conditional formatting formula to find the decimal part of the input value.