# Scoring System

#### Regal24

##### New Member
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?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
L

#### Legacy 98055

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

L

#### Legacy 98055

##### Guest
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 by a moderator:

#### Regal24

##### New Member
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?

L

#### Legacy 98055

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

#### Regal24

##### New Member
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.

L

#### Legacy 98055

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

L

#### Legacy 98055

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

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

Cheers!

Replies
13
Views
613
Replies
0
Views
384
Replies
10
Views
479
Replies
1
Views
169
Replies
3
Views
135

1,171,047
Messages
5,873,466
Members
432,981
Latest member
DMcDaniel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back