Scoring System

Regal24

New Member
Joined
Jan 2, 2010
Messages
7
Hi,

I am trying to create a simple scoring system for sports that use a "legs" and "sets" format (e.g. tennis, darts etc.)

I would like the "sets" cell to automatically update as the "legs" cell reaches the required amount of legs to win a set. After that I would like the "sets" cell to continue to count upwards when/if another set is won. Is this possible?

Thanks in advance!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Regal. It's gonna be tough to get help short of you providing a better explanation. I, for one, don't really have a clue about "legs" and "sets". Break it on down for all of us unsporty simpletons.
 

Regal24

New Member
Joined
Jan 2, 2010
Messages
7
Ok here goes.

The winner of a match is decided through the best of a given number of sets. E.g. best of 3 sets would be the first person to win 2 sets; best of 5 would be the person who wins 3 sets; best of 7, 9, 11...... etc. To win a set, a player must win a determined amount of legs which are also played out in a "best of" format, usually best of 5.

In darts for example, a match may be the best of 7 sets (i.e. first to win 4 sets and each set is played the best of 5 legs (i.e. first to win 3 legs). Hope I've managed to clear that up.

What I'm looking for is a system where I can change the value of the "legs" cell and when it reaches the desired amount of legs to win a set, the "sets" cell would automatically update.

Cheers.
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
I get you. I think. How will you account for multiple players? The example below only considers a single participant.

Select a cell that will hold the total number of legs and name is "LegTotal"
Select a cell that will hold the current number of legs won and name it "LegsWon"
Select a cell that will hold the current number of sets won and name it "SetsWon"

<A HREF="http://cid-ea73b3a00e16f94f.skydrive.live.com/self.aspx/Mr%20Excel%20Example/Scoring%20System%20Using%20Legs%20and%20Sets.zip" TARGET="_blank">Example Download: Scoring System Using Legs and Sets.xls.zip </A>

Paste this snippet into the worksheet or download the example that is a bit more functional...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("LegsWon")) Is Nothing Then
        If Target > (Range("LegTotal") / 2) Then
            Target = 0
            Range("SetsWon") = Range("SetsWon") + 1
        End If
    End If
End Sub
 
Last edited:

Regal24

New Member
Joined
Jan 2, 2010
Messages
7
Ok thank you.

I followed your instructions and nothing really happened. I must add I'm comfortable with using excel but only at a above novice level. I downloaded your file....what do I do with it now?
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Change the number of legs won and the sets will auto increment. That's what you asked for...
 

Regal24

New Member
Joined
Jan 2, 2010
Messages
7
Yea but the file I downloaded didn't open with excel as default. When I chose to open it with excel I got like 50 rows of random characters.
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
It's a standard zip file. Windows should be able to handle it without the help of any third party apps? I'll post back in a moment with an unzipped workbook...
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
<A HREF="http://cid-ea73b3a00e16f94f.skydrive.live.com/self.aspx/Mr%20Excel%20Example/Scoring%20System%20Using%20Legs%20and%20Sets.xls" TARGET="_blank">Scoring System Using Legs and Sets.xls </A>
 

Watch MrExcel Video

Forum statistics

Threads
1,098,875
Messages
5,465,217
Members
406,419
Latest member
ldp124

This Week's Hot Topics

Top