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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MJAMES - 31 Aug 2006 15:42 GMT

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?

Reply to this Message

Bob Umlas - 31 Aug 2006 15:51 GMT

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

> 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?

Reply to this Message

MJAMES - 31 Aug 2006 16:21 GMT

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?

> Ctrl/Shift/Enter:
> =SUM(SMALL(A1:A15,ROW(1:5)))
[quoted text clipped - 12 lines]
> there a
> > function or formula that will help me accomplish this?

Reply to this Message

Bob Phillips - 31 Aug 2006 16:32 GMT

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

Signature

HTH

Bob Phillips

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

> 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.
[quoted text clipped - 17 lines]
> > there a
> > > function or formula that will help me accomplish this?

Reply to this Message

MJAMES - 31 Aug 2006 16:40 GMT

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

Thanks Bob!

> It will get you the sum if you array enter it as suggested, commit with
> Ctrl-Shift-Enter, not just enter
[quoted text clipped - 24 lines]
> > > there a
> > > > function or formula that will help me accomplish this?

Reply to this Message

MJAMES - 31 Aug 2006 16:19 GMT

Reply to this Message