+ Reply to Thread
Results 1 to 2 of 2

Best way to tackle this problem...

  1. #1
    Lee Harris
    Guest

    Best way to tackle this problem...

    OK, it's no sweat to do it manually (which I do now), but curiosity is
    making me wonder if it could be done automatically without being too complex


    What I have now is a list of players with golf scores, a summation of the
    four rounds of scores, and a rank function which ranks only players in the
    "Professional" class (have a "P" in a certain column)


    I then pop that onto a tab when the event is done, sort by rank, and paste
    in a set of $ winnings in the next column along, going down the list
    manually and "merging" any tied values, that is to say if 2nd place gets 100
    dollars, 3rd place 50 dollars, but two players tie for 2nd, they would both
    get 75 dollars.




    The two things which would be most useful to me are:


    1) A way to work out the rank "within class" without having separate columns
    for each - currently I just have a A7=IF(A1="P", sum(A3:A6),"") type
    arrangement, with the rank working on column A7 for example.

    Clearly I could add extra columns for each type, then aggregate them into
    ANOTHER column, but wondered if there was a more elegant solution

    actually, it's probably as easy to do it the long winded way is it?


    2) The most helpful part ....

    Being able to do some kind of lookup into a winnings table, but with the
    facility to work out how many duplicates of that rank there are, and
    amalgamating the winnings for position N to N+ties and using the average
    amount

    I can already see a potential problem with circular references if say you're
    trying to lookup a RANK into an earnings table, but also trying to COUNT the
    number of times that rank is in the column

    I suppose I could add a 2nd column counting how many times that rank
    appears, then do some kind of indexing to find out which row that rank
    appears on the lookup table, using the count as the number of cells to sum
    on the earnings column starting at that row


    hmm, the beginnings of a self made solution appear... perhaps I'll give it a
    go, unless someone can see ahead to potential problems and has an easier
    method



  2. #2
    Lee Harris
    Guest

    Re: Best way to tackle this problem...


    "Lee Harris" <[email protected]> wrote in message
    news:[email protected]...
    > OK, it's no sweat to do it manually (which I do now), but curiosity is
    > making me wonder if it could be done automatically without being too
    > complex
    >
    >
    > What I have now is a list of players with golf scores, a summation of the
    > four rounds of scores, and a rank function which ranks only players in the
    > "Professional" class (have a "P" in a certain column)
    >
    >
    > I then pop that onto a tab when the event is done, sort by rank, and paste
    > in a set of $ winnings in the next column along, going down the list
    > manually and "merging" any tied values, that is to say if 2nd place gets
    > 100 dollars, 3rd place 50 dollars, but two players tie for 2nd, they would
    > both get 75 dollars.
    >
    >
    >
    >
    > The two things which would be most useful to me are:
    >
    >
    > 1) A way to work out the rank "within class" without having separate
    > columns for each - currently I just have a A7=IF(A1="P", sum(A3:A6),"")
    > type arrangement, with the rank working on column A7 for example.
    >
    > Clearly I could add extra columns for each type, then aggregate them into
    > ANOTHER column, but wondered if there was a more elegant solution
    >
    > actually, it's probably as easy to do it the long winded way is it?
    >
    >
    > 2) The most helpful part ....
    >
    > Being able to do some kind of lookup into a winnings table, but with the
    > facility to work out how many duplicates of that rank there are, and
    > amalgamating the winnings for position N to N+ties and using the average
    > amount
    >
    > I can already see a potential problem with circular references if say
    > you're trying to lookup a RANK into an earnings table, but also trying to
    > COUNT the number of times that rank is in the column
    >
    > I suppose I could add a 2nd column counting how many times that rank
    > appears, then do some kind of indexing to find out which row that rank
    > appears on the lookup table, using the count as the number of cells to sum
    > on the earnings column starting at that row
    >
    >
    > hmm, the beginnings of a self made solution appear... perhaps I'll give it
    > a go, unless someone can see ahead to potential problems and has an easier
    > method
    >



    I did manage to fix the 2nd part quite easily with a lookup, a match and an
    offset, and I just used the current method for the 1st bit, so no worries



+ 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