Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Hi All
I'm just getting up to speed with Excel and as an exercise I'm trying to implement a simple league table. What I'd like to know is how to create 'dynamically' a ranking table like the one shown below. Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 The table is sorted on the points column, the values for which are referenced from another sheet. Ideally I'd Like the table to be sorted automatically as players points total change(based on calculations In another sheet). Can I do this with formulas/macros or will it require some VBA code ? Thx RH |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
You can do this with a worksheet_change macro in the sheet module.
Right click on the sheet tabview codeleft window use worksheetright window select and write your code to sort. -- Don Guillett SalesAid Software "RedHook" wrote in message oups.com... Hi All I'm just getting up to speed with Excel and as an exercise I'm trying to implement a simple league table. What I'd like to know is how to create 'dynamically' a ranking table like the one shown below. Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 The table is sorted on the points column, the values for which are referenced from another sheet. Ideally I'd Like the table to be sorted automatically as players points total change(based on calculations In another sheet). Can I do this with formulas/macros or will it require some VBA code ? Thx RH |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Private Sub Worksheet_Calculate()
With Me .Columns("A:B").Sort Key1:=Range("B2"), _ Order1:=xlDescending, _ Key2:=Range("A2"), _ Order2:=xlAscending, _ Header:=xlYes End With End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "RedHook" wrote in message oups.com... Hi All I'm just getting up to speed with Excel and as an exercise I'm trying to implement a simple league table. What I'd like to know is how to create 'dynamically' a ranking table like the one shown below. Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 The table is sorted on the points column, the values for which are referenced from another sheet. Ideally I'd Like the table to be sorted automatically as players points total change(based on calculations In another sheet). Can I do this with formulas/macros or will it require some VBA code ? Thx RH |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Maybe best to stop the cascade of events
Private Sub Worksheet_Calculate() Application.EnableEvents = False With Me .Columns("A:B").Sort Key1:=Range("B2"), _ Order1:=xlDescending, _ Key2:=Range("A2"), _ Order2:=xlAscending, _ Header:=xlYes End With Application.EnableEvents = True End Sub -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Bob Phillips" wrote in message ... Private Sub Worksheet_Calculate() With Me .Columns("A:B").Sort Key1:=Range("B2"), _ Order1:=xlDescending, _ Key2:=Range("A2"), _ Order2:=xlAscending, _ Header:=xlYes End With End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "RedHook" wrote in message oups.com... Hi All I'm just getting up to speed with Excel and as an exercise I'm trying to implement a simple league table. What I'd like to know is how to create 'dynamically' a ranking table like the one shown below. Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 The table is sorted on the points column, the values for which are referenced from another sheet. Ideally I'd Like the table to be sorted automatically as players points total change(based on calculations In another sheet). Can I do this with formulas/macros or will it require some VBA code ? Thx RH |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Here's an option using formulas to create 'dynamically' the desired ranking
table A sample construct is available at: http://www.savefile.com/files/4859486 Auto extract full descending sort n rank w_wo skips.xls Assuming source data is housed in sheet: X, within A1:C11, headers in A1:C1 , data from row2 to row11, viz.: Sn Player Points 1 John 80 2 Steve 89 3 Angie 77 etc In another sheet: Y (say) With the same col headers in A1:C1 Put in A2: =RANK(C2,$C$2:$C$11) Put in B2: =INDEX(X!B:B,MATCH(LARGE($D:$D,ROW(A1)),$D:$D,0)) Copy B2 to C2 Put in D2: =IF(X!C2="","",X!C2-ROW()/10^10) (Leave D1 empty) Select A2:D2, copy down to D11 A1:C11 auto-returns a full descending sort of the source table in X, sorted by the points col. Players with tied points, if any, will appear in the same relative order that they appear within the source table. (Col D is a helper col with an arb tie-breaker for a full descending sort. If desired, just hide it away) The ranking within col A uses a simple RANK formula which gives duplicate numbers the same rank, and will then skip accordingly subsequent ranks. This simple rendition should suffice ? But if you really insist on having a non-skip ranking (as indicated in your original post), we could replace the formula in A2 with this complex array adapted from a past post by Daniel M: =RANK(C2,$C$2:$C$11)-(COUNTIF($C$2:$C$11,""&C2)-SUM((1/COUNTIF( $C$2:$C$11,$C$2:$C$11))*($C$2:$C$11C2))) Then array-enter the formula in A2, i.e. press CTRL+SHIFT+ENTER (instead of just pressing ENTER), then copy A2 down to A11 The above non-skip ranking is implemented in sheet: Y (2) in the sample book -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RedHook" wrote: Hi All I'm just getting up to speed with Excel and as an exercise I'm trying to implement a simple league table. What I'd like to know is how to create 'dynamically' a ranking table like the one shown below. Pos Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 The table is sorted on the points column, the values for which are referenced from another sheet. Ideally I'd Like the table to be sorted automatically as players points total change(based on calculations In another sheet). Can I do this with formulas/macros or will it require some VBA code ? Thx RH |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Thanks all for your input so far - I have the table sort working now.
The final thing I'd like to do is to update the player ranking column dynamically once the table is sorted: Rank Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 I guess the best approach is to name the two ranges of cells that comprise the Points and Rank columns. Then for each cell in the Points range compare the value with the previous one(unless it's the first), if the vaue is less then the corresponding cell in the Rank range gets set accordingly. If two players have the same points they are allocated the same Rank as for Jayne and Bob in the example above. What I'm not clear about is how to reference the individual cells in the named Points and Ranks ranges. Thx RH |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
"RedHook" wrote:
Thanks all for your input so far - I have the table sort working now. The final thing I'd like to do is to update the player ranking column dynamically once the table is sorted: But wasn't the part above covered in my earlier response, But if you really insist on having a non-skip ranking (as indicated in your original post), we could replace the formula in A2 with this complex array adapted from a past post by Daniel M: =RANK(C2,$C$2:$C$11)-(COUNTIF($C$2:$C$11,""&C2)-SUM((1/COUNTIF( $C$2:$C$11,$C$2:$C$11))*($C$2:$C$11C2))) Then array-enter the formula in A2, i.e. press CTRL+SHIFT+ENTER (instead of just pressing ENTER), then copy A2 down to A11 The above non-skip ranking is implemented in sheet: Y (2) in the sample book -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Hi Max
Yes, you did cover that in your earlier post and thanks for your help. I'd just be interested to know how it would be done using the VBA approach as well. Regards RH |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Just add a ws function of
=RANK(C2,scores) where scores is the named range of scores. Don't do that bit in VBA. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "RedHook" wrote in message ups.com... Thanks all for your input so far - I have the table sort working now. The final thing I'd like to do is to update the player ranking column dynamically once the table is sorted: Rank Player Points 1 Tom 124 2 Linda 122 3 Harry 107 4 Jayne 100 4 Bob 100 5 Steve 89 6 Mark 88 7 John 80 8 Angie 77 9 Andrew 71 I guess the best approach is to name the two ranges of cells that comprise the Points and Rank columns. Then for each cell in the Points range compare the value with the previous one(unless it's the first), if the vaue is less then the corresponding cell in the Rank range gets set accordingly. If two players have the same points they are allocated the same Rank as for Jayne and Bob in the example above. What I'm not clear about is how to reference the individual cells in the named Points and Ranks ranges. Thx RH |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
No prob. On the vba approach, pl see Bob Phillips' response to your earlier
post in this thread. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RedHook" wrote: Hi Max Yes, you did cover that in your earlier post and thanks for your help. I'd just be interested to know how it would be done using the VBA approach as well. Regards RH |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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(B2<C2,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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
#21
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Hi Bob, Max
Returning zero for a match that has still to be played is the desired result. Thanks to both of you for your help on this it's much appreciated. Just getting a feeling for how powerful some of these Excel features really are. Regards RH |
#22
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Here is one way around it, bit convoluted but it works.
First, hide zero values by going to ToolsoptionsView and unchecking the Zero Values checkbox. Then format the cells in results table like so [<0.5]0;General And finally, use this version of the formula =SUMPRODUCT(--((($A$1:$A$20=$J2)*($B$1:$B$20$C$1:$C$20))+(($D$1 :$D$20=$J2)* ($B$1:$B$20<$C$1:$C$20))),--($E$1:$E$20=K$1))+ SUMPRODUCT(--((($A$1:$A$20=$J2)*($B$1:$B$20<$C$1:$C$20))+(($D$1 :$D$20=$J2)*( $B$1:$B$20$C$1:$C$20))),--($E$1:$E$20=K$1))*0.0001+ SUMPRODUCT(--(($A$1:$A$20=$J2)+($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) "RedHook" wrote in message oups.com... Hi Bob, Max Returning zero for a match that has still to be played is the desired result. Thanks to both of you for your help on this it's much appreciated. Just getting a feeling for how powerful some of these Excel features really are. Regards RH |
#23
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
"Bob Phillips" wrote:
Here is one way around it, bit convoluted but it works. .... Thought it was simply dazzling, Bob ! Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#24
Posted to microsoft.public.excel.newusers
|
|||
|
|||
League table automatic sort/update
Dazzling, maybe, but it does work <G
Regards Bob "Max" wrote in message ... "Bob Phillips" wrote: Here is one way around it, bit convoluted but it works. .... Thought it was simply dazzling, Bob ! Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linking table in Excel to word | Links and Linking in Excel | |||
pivot table sort entries that don't yet appear in table | Excel Discussion (Misc queries) | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |