Function or Formula to Add lowest X number of values in a range

[Guest]

I'm looking for a way to total (SUM) the lowest X number of values in a given
range of values. For example, I have a row of 15 values. I'd like to do
some analysis and determine what the total and average is for the lowest 5
values, lowest 10 values, or whatever number of values I desire. Is there a
function or formula that will help me accomplish this?

 

[Bob Umlas]

Ctrl/Shift/Enter:
=SUM(SMALL(A1:A15,ROW(1:5)))
for the smallest 5. If you want the smallest X, where X is in cell D7, for
example, ctrl/shift/enter:
=SUM(SMALL(A1:A15,ROW(INDIRECT("1:"&D7))))

Bob Umlas
Excel MVP

 

[Guest]

I'm looking for a way to total (SUM) the lowest X number of values in a given
range of values. For example, I have a row of 15 values. I'd like to do
some analysis and determine what the total and average is for the lowest 5
values, lowest 10 values, or whatever number of values I desire. Is there a
function or formula that will help me accomplish this?

 

[Guest]

Thanks Bob for the quick response. That gets me real close to what I'm
looking for. It gets me the single lowest value in the range of values.
What I need to get is the total of let's say, the lowest 5 values or the
lowest 10 values in that same range. Any ideas?

 

[Bob Phillips]

It will get you the sum if you array enter it as suggested, commit with
Ctrl-Shift-Enter, not just enter

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

That did it! I was missing the Ctrl/Shift/Enter:

Thanks Bob!