Dropping the lowest value

[bernhard_nickel]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi All,

I'd like to generate code to "drop the lowest grade." Given a range of values, I'd like excel to perform some operations on all but the n lowest ones, for n=1 (for example). I've tried solving the problem with a conditional command that tests for the rank of an entry, but that doesn't give the proper result for ranges in which more than one value is tied for lowest.

(Spelled out: suppose I have five values, and I tell excel to operate on a value if its rank is greater than 5; if two of the values are tied for last, then both receive the rank 4, and both are operated on, contrary to intent).

Any help would be much appreciated.
B.

 

[Mike Middleton]

My approach doesn't use ranks, but when I want to "drop the lowest grade," I
usually do the following:

For data in A1:A5, in some other cell I enter =SUM(A1:A5)-MIN(A1:A5).

To drop the two lowest, I use =SUM(A1:A5)-SMALL(A1:A5,1)-SMALL(A1:A5,2)

- Mike
http://www.MikeMiddleton.com


Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi
All,

I'd like to generate code to "drop the lowest grade." Given a range of
values, I'd like excel to perform some operations on all but the n lowest
ones, for n=1 (for example). I've tried solving the problem with a
conditional command that tests for the rank of an entry, but that doesn't
give the proper result for ranges in which more than one value is tied for
lowest.

(Spelled out: suppose I have five values, and I tell excel to operate on a
value if its rank is greater than 5; if two of the values are tied for last,
then both receive the rank 4, and both are operated on, contrary to intent).

Any help would be much appreciated.
B.