Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with a Ladder
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 -- Zulu ------------------------------------------------------------------------ Zulu's Profile: http://www.excelforum.com/member.php...o&userid=26972 View this thread: http://www.excelforum.com/showthread...hreadid=401887 |
#2
|
|||
|
|||
On Mon, 5 Sep 2005 05:10:28 -0500, Zulu
wrote: 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 A11:D16 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:D. E11 contains the formula, =IF(C11D11,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 __________________________ |
#3
|
|||
|
|||
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 On Mon, 05 Sep 2005 14:06:04 +0100, Richard Buttrey wrote: On Mon, 5 Sep 2005 05:10:28 -0500, Zulu wrote: 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 A11:D16 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:D. E11 contains the formula, =IF(C11D11,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 __________________________ |
#4
|
|||
|
|||
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. -- Zulu ------------------------------------------------------------------------ Zulu's Profile: http://www.excelforum.com/member.php...o&userid=26972 View this thread: http://www.excelforum.com/showthread...hreadid=401887 |
#5
|
|||
|
|||
On Mon, 5 Sep 2005 15:54:44 -0500, Zulu
wrote: 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 D2:D7 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 __________________________ |
#6
|
|||
|
|||
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 Zulu -- Zulu ------------------------------------------------------------------------ Zulu's Profile: http://www.excelforum.com/member.php...o&userid=26972 View this thread: http://www.excelforum.com/showthread...hreadid=401887 |
#7
|
|||
|
|||
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 Zulu -- Zulu ------------------------------------------------------------------------ Zulu's Profile: http://www.excelforum.com/member.php...o&userid=26972 View this thread: http://www.excelforum.com/showthread...hreadid=401887 |
#8
|
|||
|
|||
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 -- Zulu ------------------------------------------------------------------------ Zulu's Profile: http://www.excelforum.com/member.php...o&userid=26972 View this thread: http://www.excelforum.com/showthread...hreadid=401887 |
#9
|
|||
|
|||
Zulu wrote: 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 |
#10
|
|||
|
|||
Check this out Richard, I've added it to the web http://members.optushome.com.au/msda_darts/tester.htm Regards Zulu -- Zulu ------------------------------------------------------------------------ Zulu's Profile: http://www.excelforum.com/member.php...o&userid=26972 View this thread: http://www.excelforum.com/showthread...hreadid=401887 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|