If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
League table automatic sort/update
Thanks for all your help so far, the problem with being new to this
stuff is figuring out how to use all the available information. I have one more query as described below, then I'll be ready to go and have a good play with all this stuff ! I'd like to implements a match results table as shown below TeamA ScA ScB TeamB MatchID Germany 2 1 Costa Rica FR1 Poland 2 2 Ecuador FR1 England 3 1 Paraguay FR1 Germany 4 2 Ecuador FR2 Costa Rica 1 1 Poland FR2 As the results table above is updated I'd like to summarize the results for each team in another worksheet/table. Basically for each match teams is assigned 1 point for a win, 0.5 points for a draw and 0 points if they lose a match. For the example results table the summary table would look like this: Team FR1 FR2 FR3 Costa Rica 0 0.5 Germany 1 1 Ecuador 0.5 0 England 1 Poland 0.5 0.5 Is this something that can be done with formulas or would it be easier to write some VBA code in response to changes in the original match results table. Thx RH |
#12
|
|||
|
|||
League table automatic sort/update
Assuming the data is in A1:E20
Put the teams in J2:Jn, FR1 in K1, Fr2 in K2, etc., then in K2 =SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20)) + SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+ SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/ 2 copy down and across -- HTH Bob Phillips (remove xxx from email address if mailing direct) "RedHook" wrote in message oups.com... Thanks for all your help so far, the problem with being new to this stuff is figuring out how to use all the available information. I have one more query as described below, then I'll be ready to go and have a good play with all this stuff ! I'd like to implements a match results table as shown below TeamA ScA ScB TeamB MatchID Germany 2 1 Costa Rica FR1 Poland 2 2 Ecuador FR1 England 3 1 Paraguay FR1 Germany 4 2 Ecuador FR2 Costa Rica 1 1 Poland FR2 As the results table above is updated I'd like to summarize the results for each team in another worksheet/table. Basically for each match teams is assigned 1 point for a win, 0.5 points for a draw and 0 points if they lose a match. For the example results table the summary table would look like this: Team FR1 FR2 FR3 Costa Rica 0 0.5 Germany 1 1 Ecuador 0.5 0 England 1 Poland 0.5 0.5 Is this something that can be done with formulas or would it be easier to write some VBA code in response to changes in the original match results table. Thx RH |
#13
|
|||
|
|||
League table automatic sort/update
Liked your much neater approach, Bob,
but I only managed to get the results in J1:L6 as Team FR1 FR2 Costa.. 0 1 Germany 1 1 Ecuador 0 0 England 1 0 Poland 1 0 I placed your suggested formula* in K2, and copied across/down to L6: =SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPRODUCT(--($D$1:$D$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6=$C$1:$C$6))/2 *slightly adapted the ranges Any tweak possible to your suggested formula which would drive out the OP's desired results ? Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote: Assuming the data is in A1:E20 Put the teams in J2:Jn, FR1 in K1, Fr2 in L1 [typo corrected], etc., then in K2 =SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20)) + SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+ SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/ 2 copy down and across |
#14
|
|||
|
|||
League table automatic sort/update
Just another formulas play to try ..
Assuming this table is in a sheet: X, in A1:E6 TeamA ScA ScB TeamB MatchID Germany 2 1 Costa Rica FR1 Poland 2 2 Ecuador FR1 England 3 1 Paraguay FR1 Germany 4 2 Ecuador FR2 Costa Rica 1 1 Poland FR2 Put in F2: =IF(OR(B2="",C2=""),"",IF(B2C2,1,IF(B2C2,0,0.5)) ) Put in G2: =1-F2 Select F2:G2, copy down to G6 Then in another sheet: Y (say) you have the summary table set up within A1:C6 Team FR1 FR2 Costa Rica Germany Ecuador England Poland Put in the formula bar for B2 and array-enter the formula, i.e. press CTRL+SHIFT+ENTER, instead of just pressing ENTER: =IF(ISNA(MATCH(1,(X!$A$2:$A$6=$A2)*(X!$E$2:$E$6=B$ 1),0)),IF(ISNA(MATCH(1,(X!$D$2:$D$6=$A2)*(X!$E$2:$ E$6=B$1),0)),"",INDEX(X!$G$2:$G$6,MATCH(1,(X!$D$2: $D$6=$A2)*(X!$E$2:$E$6=B$1),0))),INDEX(X!$F$2:$F$6 ,MATCH(1,(X!$A$2:$A$6=$A2)*(X!$E$2:$E$6=B$1),0))) Copy B2 across/down to C6 to populate Above will yield the desired results: Team FR1 FR2 Costa Rica 0 0.5 Germany 1 1 Ecuador 0.5 0 England 1 Poland 0.5 0.5 (Cell C5, ie England-FR2 will return as a "blank":"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RedHook" wrote: Thanks for all your help so far, the problem with being new to this stuff is figuring out how to use all the available information. I have one more query as described below, then I'll be ready to go and have a good play with all this stuff ! I'd like to implements a match results table as shown below TeamA ScA ScB TeamB MatchID Germany 2 1 Costa Rica FR1 Poland 2 2 Ecuador FR1 England 3 1 Paraguay FR1 Germany 4 2 Ecuador FR2 Costa Rica 1 1 Poland FR2 As the results table above is updated I'd like to summarize the results for each team in another worksheet/table. Basically for each match teams is assigned 1 point for a win, 0.5 points for a draw and 0 points if they lose a match. For the example results table the summary table would look like this: Team FR1 FR2 FR3 Costa Rica 0 0.5 Germany 1 1 Ecuador 0.5 0 England 1 Poland 0.5 0.5 Is this something that can be done with formulas or would it be easier to write some VBA code in response to changes in the original match results table. Thx RH |
#15
|
|||
|
|||
League table automatic sort/update
Thanks Max, I forgot the half-score for column D teams.
=SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20)) + SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+ SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/ 2+ SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/ 2 -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Max" wrote in message ... Liked your much neater approach, Bob, but I only managed to get the results in J1:L6 as Team FR1 FR2 Costa.. 0 1 Germany 1 1 Ecuador 0 0 England 1 0 Poland 1 0 I placed your suggested formula* in K2, and copied across/down to L6: =SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUM PRODUCT(--($D$1:$D$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPROD UCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6=$C$1:$C$6))/2 *slightly adapted the ranges Any tweak possible to your suggested formula which would drive out the OP's desired results ? Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote: Assuming the data is in A1:E20 Put the teams in J2:Jn, FR1 in K1, Fr2 in L1 [typo corrected], etc., then in K2 =SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20)) + SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+ SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/ 2 copy down and across |
#16
|
|||
|
|||
League table automatic sort/update
Can even simplify it G
=SUMPRODUCT(--((($A$1:$A$20=$J3)*($B$1:$B$20$C$1:$C$20))+(($D$1 :$D$20=$J3)* ($B$1:$B$20$C$1:$C$20))),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+ SUMPRODUCT(--(($A$1:$A$20=$J3)+($D$1:$D$20=$J3)),--($E$1:$E$20=K$1),--($B$1: $B$20=$C$1:$C$20))/2 -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Max" wrote in message ... Liked your much neater approach, Bob, but I only managed to get the results in J1:L6 as Team FR1 FR2 Costa.. 0 1 Germany 1 1 Ecuador 0 0 England 1 0 Poland 1 0 I placed your suggested formula* in K2, and copied across/down to L6: =SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUM PRODUCT(--($D$1:$D$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPROD UCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6=$C$1:$C$6))/2 *slightly adapted the ranges Any tweak possible to your suggested formula which would drive out the OP's desired results ? Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote: Assuming the data is in A1:E20 Put the teams in J2:Jn, FR1 in K1, Fr2 in L1 [typo corrected], etc., then in K2 =SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20)) + SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+ SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/ 2 copy down and across |
#17
|
|||
|
|||
League table automatic sort/update
Excellent - Thanks again for your help. Do you generate these complex
formulas 'by hand' or is there some tool/trick to help you generate them ? |
#18
|
|||
|
|||
League table automatic sort/update
No, it's all by hand mate. I did create a tool once, and whilst it is useful
to help get the syntax correct, you still need to know the basics so as to know what to ask for. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "RedHook" wrote in message oups.com... Excellent - Thanks again for your help. Do you generate these complex formulas 'by hand' or is there some tool/trick to help you generate them ? |
#19
|
|||
|
|||
League table automatic sort/update
Bob, thanks .. albeit I had to drop that into K3 though
before propagating it across/down and up! g Just a lingering point though: England - FR2 will return a zero, instead of a "blank" (part of the OP's desired result?) I'm not sure how important the above is to the OP (or to the underlying beautiful game) to have the result returned as a zero when presumably England has yet to play the game under MatchID FR2 ?? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote: Can even simplify it G =SUMPRODUCT(--((($A$1:$A$20=$J3)*($B$1:$B$20$C$1:$C$20))+(($D$1 :$D$20=$J3)* ($B$1:$B$20$C$1:$C$20))),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+ SUMPRODUCT(--(($A$1:$A$20=$J3)+($D$1:$D$20=$J3)),--($E$1:$E$20=K$1),--($B$1: $B$20=$C$1:$C$20))/2 |
#20
|
|||
|
|||
League table automatic sort/update
fwiw, a slight correction .. line:
Put in G2: =1-F2 should read: Put in G2: =IF(F2="","",1-F2) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table problem | Redwood | Database Design | 29 | April 3rd, 2006 04:58 PM |
Need to Improve Code Copying/Pasting Between Workbooks | David | General Discussion | 1 | January 6th, 2006 03:56 AM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |