Scoring System

[Regal24]

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!

 

[Legacy 98055]

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]

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.

 

[Legacy 98055]

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...

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

 

[Regal24]

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?

 

[Legacy 98055]

Change the number of legs won and the sets will auto increment. That's what you asked for...

 

[Regal24]

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.

 

[Legacy 98055]

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...

 

[Legacy 98055]

<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>

 

[Regal24]

Ah that's wicked thank you ever so much for your help!

Cheers!