Sum of the Largest Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I have a gradebook. I have 16 assignments, and thus have
16 columns for scores. However, I would like to include
ONLY the highest 10 scores in the final grade. In other
words, how can I find the sum of the largest 10 values in
a 16-cell range?

Thanks!
 
=SUM(LARGE($A$1:$P$1,ROW(INDIRECT("1:10"))))

arary entered using CTRL+SHIFT+ENTER
 
Hi Chimi!

One way:

=SUMPRODUCT(LARGE(C6:P6,ROW(INDIRECT("1:10"))))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
For the sequence
1,2,3,4,5,6,7,7,9,10,11,12,13,14,15,16
your formula will return 107 instead of the
correct result 114.
 
Hi Ken

Your formula will fail with the sequence:

1,2,3,4,5,7,7,8,9,10,11,12,13,14,15,16
 
You're right. The formula should be dismissed.

Leo Heuser said:
For the sequence
1,2,3,4,5,6,7,7,9,10,11,12,13,14,15,16
your formula will return 107 instead of the
correct result 114.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.
Click to expand...
 
Hi Ken,

It appears we missed an important sign post. The interaction between two
things made me to post a wrong formula:

[1] The Top N issues;
[2] Eagerness to avoid the ROW(INDIRECT(...) idiom because the volatility
this causes.

Sum (or average, etc.) N largest/smallest values are similar to the Top N
questions. When it's not expicitly stated that the values to be processed
must be *exactly N* then I feel we should at least provide solutions for a
self-adjusting N for Nth value might have multiple instances. An OP at
MrExcel explicitly asked me for the values associated with all
instances/occurrences of the Nth value. A few weeks later I saw Harlan also
explicitly mention this to an OP at worksheet.functions. Anyway, I no
longer take such questions implicitly as 'exactly N'. The same with
questions of "how can I retrieve the associated with the Min/Max value?".
I feel that "Sum largest/Smallest N" should be based on a self-adjusting
N...

=SUMIF(Range,">"&LARGE(Range,N))+COUNTIF(Range,LARGE(Range,N))*LARGE(Range,N
)

However, Chimi's question (the OP in this thread) involves a question of
summing "exactly N" values. My failed attempt to effect the required sum
without the ROW(INDIRECT(...) idiom stranded (as Leo's sample shows), how
ironic -- considering [1], on the multiple occurrences of the Nth value [
the sign post we missed :( ]. The lesson is that we can't avoid the
ROW(INDIRECT(...) idiom for computations involving questions of exactly N
(where N is variablized).

The incident enabled me to correct an earlier mistake of mine though
(http://makeashorterlink.com/?K1C5222F6)

Aladin
 
Back
Top