how to sum 'n' largest values *not* in an array?

[memilanuk]

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

 

[T. Valko]

'585','20','592','20','595','30','599','38','0','0'

What's the "rule" that differentiates the "X" values from the scores?

Just looking at the values, the scores are significantly higher than the "X"
values. If you can define a "rule" to separate the 2 then this can be done
with a relatively simple formula, otherwise, it'll be slightly more
complicated.

For example, sum the highest 3 values that are greater than 50 (highest
possible "X" value ???).

 

[memilanuk]

What's the "rule" that differentiates the "X" values from the scores?

Just looking at the values, the scores are significantly higher than the "X"
values. If you can define a "rule" to separate the 2 then this can be done
with a relatively simple formula, otherwise, it'll be slightly more
complicated.

For example, sum the highest 3 values that are greater than 50 (highest
possible "X" value ???).

The scores are from a bullseye type target with a decimal
(10,9,8,7,6,5) scoring system. The highest possible value for any
individual shot is '10'. Inside the 10-ring is another scoring ring,
the 'X' ring. Any hit in the X ring still counts as 10 points, but
also counts as a tie-breaker. So ten shots all in the 10 ring but
with only seven inside the X ring would be noted as '100-7X'. A
100-1X beats a 99-9X, because the highest point value wins first, with
the X-count only coming into play in the event of a tie such as a
100-7X vs a 100-5X. Several individual stages add up to a daily
aggregate like the '595-30X' mentioned earlier. The high three daily
aggs out of five possible events (competitors may attend any or all of
these qualifiers) are added up, plus the daily score from the one
required event, and forms a grand aggregate for league champion, etc.

Don't know if that makes it any clearer or not Its something that
is relatively easy, but time consuming, to do by way of Mk1 Mod0
eyeball (the sorting/filtering) but a bit more challenging to make
happen in Excel (so far).

Thanks,

Monte

 

[Ragdyer]

If I understand you completely, I don't see your problem!

Since your X scores will *never* be even close to being the largest value,
just include those cells in the Large() function ... they'll *never* enter
into the calculation.

Say your example data was in D2 to M2.
Try this:

=SUM(LARGE(D2:M2,{1,2,3}))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

What's the "rule" that differentiates the "X" values from the scores?

Just looking at the values, the scores are significantly higher than the "X"
values. If you can define a "rule" to separate the 2 then this can be done
with a relatively simple formula, otherwise, it'll be slightly more
complicated.

For example, sum the highest 3 values that are greater than 50 (highest
possible "X" value ???).

The scores are from a bullseye type target with a decimal
(10,9,8,7,6,5) scoring system. The highest possible value for any
individual shot is '10'. Inside the 10-ring is another scoring ring,
the 'X' ring. Any hit in the X ring still counts as 10 points, but
also counts as a tie-breaker. So ten shots all in the 10 ring but
with only seven inside the X ring would be noted as '100-7X'. A
100-1X beats a 99-9X, because the highest point value wins first, with
the X-count only coming into play in the event of a tie such as a
100-7X vs a 100-5X. Several individual stages add up to a daily
aggregate like the '595-30X' mentioned earlier. The high three daily
aggs out of five possible events (competitors may attend any or all of
these qualifiers) are added up, plus the daily score from the one
required event, and forms a grand aggregate for league champion, etc.

Don't know if that makes it any clearer or not Its something that
is relatively easy, but time consuming, to do by way of Mk1 Mod0
eyeball (the sorting/filtering) but a bit more challenging to make
happen in Excel (so far).

Thanks,

Monte

 

[Domenic]

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?

Assuming that B2, D2, F2, H2, and J2 contain the five values, try...

=SUM(LARGE((B2,D2,F2,H2,J2),{1,2,3}))

 

[memilanuk]

Assuming that B2, D2, F2, H2, and J2 contain the five values, try...

=SUM(LARGE((B2,D2,F2,H2,J2),{1,2,3}))

Domenic,

That worked pretty well; I had thought I had to enter the values for
an array by highlighting and doing the Ctrl+Shift+Enter thing to get
the curly braces ({ }) but not so this time. Slick!

Now... for an another sticking point: now that I have the sum of the
high 3 out of 5 scores in terms of points... what about the X-counts?
I mean, I know now the aggregate point value, but how do I add the X-
counts that go with those particular scores - which may or may not be
the highest ones (its entirely possible to win by points but have a
low X-count). An example might be something like 585-20X, 592-20X,
595-30X, 599-27X, 591-21X. Putting it in terms of what I'd do looking
at it by 'eye', I'd find the top three scores (which we did using large
() ), and then bring the corresponding X-count values along with the
point values, add up all the points and all the Xs for each competitor
and then rank each person by points, then by X count. After that, the
tie-breaking rules get kind of obscure - goes back to who dropped a
point last, which involves going through the score cards that were
turned in, shot by shot. If it comes down to that (fairly rarely), I
think we're going to be doing that by hand anyways.

I'm thinking perhaps setting up some hidden cells - 6 columns, two
(points & Xs) for each of their 'high 3', and fill them with the
appropriate values using '=large((b4,d4,e4,f4,h4,i4)),1)' for the
first point value, and then find someway to reference the cell
immediately to the right of the cell that had the large value to copy
the X count over. large() seems to pull the cell value, but not the
cell address, so I'm not sure that will work. Any other ideas?

TIA,

Monte

 

[T. Valko]

That's the same thought process I had which is why I asked about the
difference between a score and a "X" value.

 

[Domenic]

For the X-counts...

Assumptions:

B2, D2, F2, H2, and J2 contain the point values

C2, E2, G2, I2, and K2 contain the corresponding X-counts

The sum of the X-counts does not exceed three digits in length

Formula:

=RIGHT(SUM(LARGE(IF(MOD(COLUMN(B2:J2)-COLUMN(B2),2)=0,B2:J2+C2:K2/1000),{
1,2,3})),3)+0

....confirmed with CONTROL+SHIFT+ENTER.