Hello,

I'm trying to find a way to take the high four scores from a league
occurring over the spring/summer. Six total scores are possible, one
event is mandatory, so I need the high three from the remaining five
events. Clear as mud? ;)

If the scores were simply one numeric value, I think I could make it
work using 'sum' and 'large', judging by what I see in the help
files. Therein lies the problem. The scores are recorded in a format
similar to '595-30X', where the total points is '595', and '30X' is a
tie-breaker value (i.e. if two people have the same number of points,
but one has more 'X's, the higher X-count prevails). In order to make
the formulas a little simpler, I've broken the scores apart with '595'
in one column, and '30' in the one immediately adjacent to it. It
looks close enough that a non-computer savvy person looking at it will
recognize the scores for what they are, and makes adding up the
points, adding up the X-count, and then concatenating the two back
into a final cell in the format 'nnnn-xxx' relatively simple.
Except... it makes it so the row for a given competitor looks like
'585','20','592','20','595','30','599','38','0','0 ' (assuming
competitor makes 4 out of the 5 qualifiers but misses the last one)

How do I sum the high 3 values from '585','592','595','599','0' when
they aren't in adjacent cells which I can give as an array value? Do
I have to copy the values to some hidden cells or another sheet and do
the array calcs on those (now adjacent) values? Is there a way to do
this directly with out copying the values somewhere else?

TIA,

Monte