Remove values from a range of cells

[finmanjim]

Let's say I have a range of numbers in cells A1:A15 and I
wish cell A16 to have the sum of A1:A15 excluding the
three lowest values in that range. Can this be done with
the MIN function? Any suggestions?

Thanks.

Jim

 

[Biff]

Hi Jim,

Here's a couple of examples.

This is probably easier to understand than the second
example and you only want to exclude the 3 lowest values:

=SUM(A1:A15)-SUM(SMALL(A1:A15,1),SMALL(A1:A15,2),SMALL
(A1:A15,3))


OR

Entered as an array: CTRL+SHIFT+ENTER

This example would be preferable if you had a large data
set and wanted to exclude many values.

=SUM(LARGE(A1:A15,ROW(INDIRECT("1:12"))))

Biff


This is probably easier to understand.

 

[RagDyer]

Another way using an array formula:

=SUM(A1:A15)-SUM(SMALL(A1:A15,{1,2,3}))

Must be entered using CSE(ctrl+shift+enter)
which will place curly brackets on the outside of the formula.
You should not enter them yourself.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi Jim,

Here's a couple of examples.

This is probably easier to understand than the second
example and you only want to exclude the 3 lowest values:

=SUM(A1:A15)-SUM(SMALL(A1:A15,1),SMALL(A1:A15,2),SMALL
(A1:A15,3))


OR

Entered as an array: CTRL+SHIFT+ENTER

This example would be preferable if you had a large data
set and wanted to exclude many values.

=SUM(LARGE(A1:A15,ROW(INDIRECT("1:12"))))

Biff


This is probably easier to understand.

 

[RagDyer]

Made a mistake !

Can be entered as a regular formula - <Enter>
--

Regards,

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

Another way using an array formula:

=SUM(A1:A15)-SUM(SMALL(A1:A15,{1,2,3}))

Must be entered using CSE(ctrl+shift+enter)
which will place curly brackets on the outside of the formula.
You should not enter them yourself.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi Jim,

Here's a couple of examples.

This is probably easier to understand than the second
example and you only want to exclude the 3 lowest values:

=SUM(A1:A15)-SUM(SMALL(A1:A15,1),SMALL(A1:A15,2),SMALL
(A1:A15,3))


OR

Entered as an array: CTRL+SHIFT+ENTER

This example would be preferable if you had a large data
set and wanted to exclude many values.

=SUM(LARGE(A1:A15,ROW(INDIRECT("1:12"))))

Biff


This is probably easier to understand.