Is there a function to give us the highest value in a list of tex
On 10 Nov 2005 11:51:54 -0800, "Harlan Grove" wrote:
Domenic wrote...
Here's another way...
If Z is higher than Y, try...
=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1: A10),1),
COUNTIF(A1:A10,"<"&A1:A10),0))
...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
If A is higher than B, try...
=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1: A10),1),
COUNTIF(A1:A10,"<"&A1:A10),0))
...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
...
Since any instance of the 'highest' value would be as good as any
other, both formulas could be reduced to
=LOOKUP(2,1/(COUNTIF(A1:A30,""&A1:A30)=0),A1:A30)
and
=LOOKUP(2,1/(COUNTIF(A1:A30,"<"&A1:A30)=0),A1:A30)
respectively, neither of which require array entry.
Nicer.
--ron
|