+ Reply to Thread
Results 1 to 24 of 24

League table automatic sort/update

  1. #1
    RedHook
    Guest

    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. #2
    Don Guillett
    Guest

    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
    [email protected]
    "RedHook" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Bob Phillips
    Guest

    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, _
    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    Bob Phillips
    Guest

    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, _
    Header:=xlYes
    End With
    Application.EnableEvents = True
    End Sub

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #5
    Max
    Guest

    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. #6
    RedHook
    Guest

    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. #7
    Max
    Guest

    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. #8
    RedHook
    Guest

    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. #9
    Bob Phillips
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #10
    Max
    Guest

    Re: 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. #11
    RedHook
    Guest

    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
    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. #12
    Bob Phillips
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #13
    Max
    Guest

    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
    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. #14
    Max
    Guest

    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
    > 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. #15
    Bob Phillips
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #16
    Bob Phillips
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #17
    RedHook
    Guest

    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. #18
    Bob Phillips
    Guest

    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
    know what to ask for.

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "RedHook" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #19
    Max
    Guest

    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. #20
    Max
    Guest

    Re: 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. #21
    RedHook
    Guest

    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. #22
    Bob Phillips
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #23
    Max
    Guest

    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. #24
    Bob Phillips
    Guest

    Re: League table automatic sort/update

    Dazzling, maybe, but it does work <G>

    Regards

    Bob

    "Max" <[email protected]> wrote in message
    news:[email protected]...
    > "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 to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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