Help with a Ladder

[Zulu]

Hi
I do the ladder for the darts club I play darts for and have a
webpage that has all the fixtures and results of all the teams for
Monday and Wednesday night darts but I would like some help with the
ladder if anyone could help that would be great. The problem is I don't
use excel very well and I find it takes up a lot of my time with sorting
the ladder and manually inserting the results. Is their anyway I could
make things a little faster?

Here’s an e.g of the ladder.

P= Played W=Won L=Lost F=points for A= points against Pts= points

P W L F A Pts %
Team 1 2 2 0 13 9 4
Team 4 2 1 1 7 14 2
Team 6 2 1 1 7 14 2
Team 5 2 1 1 10 11 2
Team 3 2 1 1 13 8 2
Team 2 2 1 1 11 10 2


Team 1 v Team 2 Team 1 Won 6-5
Team 3 v Team 4 Team 3 Won 8-1
Team 5 v Team 6 Team 5 Won 6-5
Team 1 v Team 5 Team 1 Won 7-4
Team 3 v Team 2 Team 2 Won 6-5
Team 4 v Team 6 Team 4 Won 7-2

Is there a way I could automatically add the results into the ladder
and then sort.
I don't get paid for doing the updates I just do it for the love of the
game. If anyone has any idea's that would be great.

Regards Zulu

 

[Richard Buttrey]

Hi
I do the ladder for the darts club I play darts for and have a
webpage that has all the fixtures and results of all the teams for
Monday and Wednesday night darts but I would like some help with the
ladder if anyone could help that would be great. The problem is I don't
use excel very well and I find it takes up a lot of my time with sorting
the ladder and manually inserting the results. Is their anyway I could
make things a little faster?

Here’s an e.g of the ladder.

P= Played W=Won L=Lost F=points for A= points against Pts= points

P W L F A Pts %
Team 1 2 2 0 13 9 4
Team 4 2 1 1 7 14 2
Team 6 2 1 1 7 14 2
Team 5 2 1 1 10 11 2
Team 3 2 1 1 13 8 2
Team 2 2 1 1 11 10 2


Team 1 v Team 2 Team 1 Won 6-5
Team 3 v Team 4 Team 3 Won 8-1
Team 5 v Team 6 Team 5 Won 6-5
Team 1 v Team 5 Team 1 Won 7-4
Team 3 v Team 2 Team 2 Won 6-5
Team 4 v Team 6 Team 4 Won 7-2

Is there a way I could automatically add the results into the ladder
and then sort.
I don't get paid for doing the updates I just do it for the love of the
game. If anyone has any idea's that would be great.

Regards Zulu

The first thing to recognise is that this is a fairly standard
database summary application, and you should arrange your workbook
accordingly. i.e. have one area where you collect your data, and
another where you summarise it.

Using your data, the following is one solution. (Note some of your
example results appeared incorrect. e.g. Team 6 doesn't appear to have
won, yet your summary table shows they have. Team 2 appears to have 11
points against rather than the 10 you suggest).


I have the following, being the start of a database to which you would
add results, in A1116

A B C D E
F
Home Team Away Team F A Won Won
Team1 Team2 6 5 1 0
Team3 Team4 8 1 1 0
Team5 Team6 6 5 1 0
Team1 Team5 7 4 1 0
Team3 Team2 5 6 0 1
Team4 Team6 7 2 1 0

Enter the Matches and results in columns A.
E11 contains the formula, =IF(C11>D11,1,0)
F11 contains the formula, =IF(C11<D11,1,0)

These should be copied down E:12:F16


The summary area is in A1:H16

A B C D E F G H
P W L F A Pts %
Team1 2 1 1 13 9 2 144.44%
Team2 2 1 1 11 11 2 100.00%
Team3 2 1 1 13 7 2 185.71%
Team4 2 1 1 8 10 2 80.00%
Team5 2 1 1 10 12 2 83.33%
Team6 2 0 2 7 13 0 53.85%


A2:A7 contains the names of the teams
The formula in the other cells are
B2 =COUNTIF($A$10:$B$16,A2)
C2 =SUMIF($A$11:$B$16,A2,$E$11:$F$16)
D2 =B2-C2
E2=SUMIF($A$11:$A$16,$A2,$C$11:$C$16)+SUMIF($B$11:$B$16,$A2,$D$11:$D$16)
F2=SUMIF($A$11:$A$16,$A2,$D$11:$D$16)+SUMIF($B$11:$B$16,$A2,$C$11:$C$16)
G2 =C2*2
H2 =E2/F2 and formatted as a percentage


Obviously in your real example you would modify the summary area so
that you cover all the rows your database area is ever likely to reach
It's probably best to change the references to row 16 to say row 1000
if you never expect to have more than 990 matches. Then as you add
matches to your database, the summary area automatically reflects the
results.

All you need to do then is either manually sort the summary range
H2:H7, or have a small bit of VBA code attached to a button to do the
same thing.

You could further improve on this by keeping a list of your teams
elsewhere on the database sheet, and applying a Data Validation List
to the cells in Columns A & B of the database, so that by clicking on
the cell drop down arrows you can pick the team from the drop down
list. This would avoid you having to type the Team Names and ensure
that you dob't have any mis-spellings which would compromise the
results table.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

Follow up.

You didn't mention drawn matches, and I hadn't thought about those
earlier.

If applicable you'd need to modify the data to include a "Drawn"
column, and change the various formula and "Pts" column accordingly.

Rgds

The first thing to recognise is that this is a fairly standard
database summary application, and you should arrange your workbook
accordingly. i.e. have one area where you collect your data, and
another where you summarise it.

Using your data, the following is one solution. (Note some of your
example results appeared incorrect. e.g. Team 6 doesn't appear to have
won, yet your summary table shows they have. Team 2 appears to have 11
points against rather than the 10 you suggest).


I have the following, being the start of a database to which you would
add results, in A1116

A B C D E
F
Home Team Away Team F A Won Won
Team1 Team2 6 5 1 0
Team3 Team4 8 1 1 0
Team5 Team6 6 5 1 0
Team1 Team5 7 4 1 0
Team3 Team2 5 6 0 1
Team4 Team6 7 2 1 0

Enter the Matches and results in columns A.
E11 contains the formula, =IF(C11>D11,1,0)
F11 contains the formula, =IF(C11<D11,1,0)

These should be copied down E:12:F16


The summary area is in A1:H16

A B C D E F G H
P W L F A Pts %
Team1 2 1 1 13 9 2 144.44%
Team2 2 1 1 11 11 2 100.00%
Team3 2 1 1 13 7 2 185.71%
Team4 2 1 1 8 10 2 80.00%
Team5 2 1 1 10 12 2 83.33%
Team6 2 0 2 7 13 0 53.85%


A2:A7 contains the names of the teams
The formula in the other cells are
B2 =COUNTIF($A$10:$B$16,A2)
C2 =SUMIF($A$11:$B$16,A2,$E$11:$F$16)
D2 =B2-C2
E2=SUMIF($A$11:$A$16,$A2,$C$11:$C$16)+SUMIF($B$11:$B$16,$A2,$D$11:$D$16)
F2=SUMIF($A$11:$A$16,$A2,$D$11:$D$16)+SUMIF($B$11:$B$16,$A2,$C$11:$C$16)
G2 =C2*2
H2 =E2/F2 and formatted as a percentage


Obviously in your real example you would modify the summary area so
that you cover all the rows your database area is ever likely to reach
It's probably best to change the references to row 16 to say row 1000
if you never expect to have more than 990 matches. Then as you add
matches to your database, the summary area automatically reflects the
results.

All you need to do then is either manually sort the summary range
H2:H7, or have a small bit of VBA code attached to a button to do the
same thing.

You could further improve on this by keeping a list of your teams
elsewhere on the database sheet, and applying a Data Validation List
to the cells in Columns A & B of the database, so that by clicking on
the cell drop down arrows you can pick the team from the drop down
list. This would avoid you having to type the Team Names and ensure
that you dob't have any mis-spellings which would compromise the
results table.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Zulu]

Hi
Yes their could be drawn matches, here is the site that would
explain things alittle more, thanks for your help.

Regards Zulu

http://members.optushome.com.au/msda_darts/msda.html

P.s. I copy the ladder into frontpage before I upload.

 

[Richard Buttrey]

Hi
Yes their could be drawn matches, here is the site that would
explain things alittle more, thanks for your help.

Regards Zulu

http://members.optushome.com.au/msda_darts/msda.html

P.s. I copy the ladder into frontpage before I upload.

Modify my original suggestion as follows

Add another column G to the database to record the draws.
G11 =IF(C11=D11,1,0) copy this down to row 16


In the summary section, move D1:H7 across one column.

D2
=SUMIF($A$11:$A$16,A2,$G$11:$G$16)+SUMIF($B$11:$B$16,A2,$G$11:$G$16)
copy down D27

L2 (the Lost column) now becomes =B2-C2-D2
copy down L2:L7

H2 (the Pts column) now becomes =C2*2+D2
copy down H2:H7

Rgds



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Zulu]

Hi
This is looking great but I have a small problem.

I get error in this part

E2=SUMIF($A$11:$A$16,$A2,$C$11:$C$16)+SUMIF($B$11
$B$16,$A2,$D$11:$D$16)

F2=SUMIF($A$11:$A$16,$A2,$D$11:$D$16)+SUMIF($B$11
$B$16,$A2,$C$11:$C$16)

message ... You've enterd too few arguments for this function.

Also where do I put the fixtures DOH! LOL.

Regards Zul

 

[Zulu]

Please ignore last post, error message is in:-

D2=SUMIF($A$11:$A$16,A2,$G$11:$G$16)+SUMIF($B$11:$B
16,A2,$G$11:$G$16)

F2=SUMIF($A$11:$A$16,$A2,$D$11:$D$16)+SUMIF($B$11
$B$16,$A2,$C$11:$C$16)


Regards Zul

 

[Zulu]

Hi
I sort out where to put the team results, and I've also changed

B2=COUNTIF($A$10:$B$100,A2) to allow for more games. I still don't kno
how to get around the error in column D (Draw) F (Points For)

message ... You've enterd too few arguments for this function.

I think Ive stuffed a few things up but its not to bad, is it possibl
for me to send you the file so you can have alook? or I can upload i
to my site and have a look there.

Regards Zul

 

[Richard]

Hi
I sort out where to put the team results, and I've also changed

B2=COUNTIF($A$10:$B$100,A2) to allow for more games. I still don't know
how to get around the error in column D (Draw) F (Points For)

message ... You've enterd too few arguments for this function.

I think Ive stuffed a few things up but its not to bad, is it possible
for me to send you the file so you can have alook? or I can upload it
to my site and have a look there.

Regards Zulu

Re the 'too few arguments' error message.

The two lines I posted started with D2= and F2= followed by the actual
formula. These were merely meant to say what should be in D2 and F2.

Make sure that the formula starts with "=SumIf" (- no quotes)

If there's still a problem and you'd like me to have a look at your
s/s, then email me with your site URL and I'll take a look.

Rgds

 

[Zulu]

Check this out Richard, I've added it to the web

http://members.optushome.com.au/msda_darts/tester.htm

Regards Zulu