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

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

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?
 
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
 
MJAMES said:
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?
 
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?
 
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)
 
Back
Top