Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
RedHook
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
RedHook
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
RedHook
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
RedHook
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
RedHook
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
RedHook
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Linking table in Excel to word travis Links and Linking in Excel 1 November 19th 05 03:30 PM
pivot table sort entries that don't yet appear in table rachael Excel Discussion (Misc queries) 11 September 19th 05 11:29 PM
Pivot Tables, Help? Adam Excel Discussion (Misc queries) 6 March 24th 05 03:35 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM


All times are GMT +1. The time now is 09:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"