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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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>
 

Forum statistics

Threads
1,089,578
Messages
5,409,110
Members
403,251
Latest member
BAMORAN

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top