Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 a 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Best way to tackle this problem...
"Lee Harris" wrote in message ... 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 a 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |