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

[Guest]

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?

 

[Myrna Larson]

One way

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

Also check out the LARGE and SMALL functions.

 

[Ron Rosenfeld]

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