How to create a formula where high and low numbers are excluded
 

How do I create a formula in Excel 2003 where if I have six colums of
numbers, and I would like to get the sum of these six numbers, excluding the
highest and lowest number? Is this possible?

One way

=SUM(A1:F1)-MAX(A1:F1)-MIN(A1:F1)

Also check out the LARGE and SMALL functions.


On Sun, 6 Mar 2005 15:23:07 -0800, "
osoft.com wrote:

How do I create a formula in Excel 2003 where if I have six colums of
numbers, and I would like to get the sum of these six numbers, excluding the
highest and lowest number? Is this possible?

On Sun, 6 Mar 2005 15:23:07 -0800, "
osoft.com wrote:

How do I create a formula in Excel 2003 where if I have six colums of
numbers, and I would like to get the sum of these six numbers, excluding the
highest and lowest number? Is this possible?

If you have six and only six numbers, then:

=SUM(LARGE(rng,{2,3,4,5}))

where rng is the cell reference for the six columns, e.g. A2:F2.

A more general formula, where there can be a variable amount of numbers:

=SUM(LARGE(rng,ROW(INDIRECT("2:"&COUNT(rng)-1))))

This latter is an "array" formula. After typing it in, hold down <ctrl<shift
while hitting <enter. XL will place braces {...} around the formula.


--ron