League table automatic sort/update

1. 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. Re: League table automatic sort/update

You can do this with a worksheet_change macro in the sheet module.
Right click on the sheet tab>view code>left window use worksheet>right
window select and write your code to sort.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"RedHook" <redhook@hotmail.co.uk> wrote in message
> 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. Re: 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, _
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" <redhook@hotmail.co.uk> wrote in message
> 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. Re: 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, _
End With
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Bob Phillips" <bob.NGs@googlemailxxx.com> wrote in message
news:ekxUZUCeGHA.380@TK2MSFTNGP04.phx.gbl...
> Private Sub Worksheet_Calculate()
> With Me
> .Columns("A:B").Sort Key1:=Range("B2"), _
> Order1:=xlDescending, _
> Key2:=Range("A2"), _
> Order2:=xlAscending, _
> 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" <redhook@hotmail.co.uk> wrote in message
> > 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. Re: 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\$11>C2)))

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. Re: 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. Re: 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, re:

> 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\$11>C2)))
>
> 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. Re: 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. Re: 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" <redhook@hotmail.co.uk> wrote in message
> 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. Re: League table automatic sort/update

No prob. On the vba approach, pl see Bob Phillips' response to your earlier
--
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. Re: 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
England 3 1 Paraguay FR1
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
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. Re: 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" <redhook@hotmail.co.uk> wrote in message
> 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
> 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. Re: 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
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

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. Re: 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(B2>C2,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
> 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
> 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
> 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. Re: 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" <demechanik@yahoo.com> wrote in message
news:69980559-A2D0-469F-A938-0D44E52B02F5@microsoft.com...
> 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
> 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
>
>
> 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. Re: 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" <demechanik@yahoo.com> wrote in message
news:69980559-A2D0-469F-A938-0D44E52B02F5@microsoft.com...
> 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
> 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
>
>
> 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. Re: 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. Re: 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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"RedHook" <redhook@hotmail.co.uk> wrote in message
> 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. Re: 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. Re: League table automatic sort/update

fwiw, a slight correction .. line:
> Put in G2: =1-F2

> Put in G2: =IF(F2="","",1-F2)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

21. Re: 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. Re: League table automatic sort/update

Here is one way around it, bit convoluted but it works.

First, hide zero values by going to Tools>options>View 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" <redhook@hotmail.co.uk> wrote in message
> 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. Re: 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. Re: League table automatic sort/update

Dazzling, maybe, but it does work <G>

Regards

Bob

"Max" <demechanik@yahoo.com> wrote in message
news:F0273EA6-1763-48C9-97A1-3765E75B69B3@microsoft.com...
> "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
> ---

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1