"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