Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
Hi,
I have a list of names and I need to get the name with the highest in the alphabetical order. I dont want to sort. Thanks, |
#2
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
highest?? -- Don Guillett SalesAid Software "Calaw" wrote in message ... Hi, I have a list of names and I need to get the name with the highest in the alphabetical order. I dont want to sort. Thanks, |
#3
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
On Thu, 10 Nov 2005 07:20:15 -0800, "Calaw"
wrote: Hi, I have a list of names and I need to get the name with the highest in the alphabetical order. I dont want to sort. Thanks, Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ (Version 4.01 or later). If by "Highest" you mean that A is higher than B and so forth, use the formula: =VSORT(Names,,1) If you mean Z is higher than Y and so forth, then: =VSORT(Names) will do. --ron |
#4
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
Here's another way...
If Z is higher than Y, try... =INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A1 0),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:A1 0),1),COUNTIF(A1:A10,"< "&A1:A10),0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "Calaw" wrote: Hi, I have a list of names and I need to get the name with the highest in the alphabetical order. I dont want to sort. Thanks, |
#5
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
On Thu, 10 Nov 2005 11:49:07 -0500, Domenic wrote:
Here's another way... If Z is higher than Y, try... =INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A 10),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:A 10),1),COUNTIF(A1:A10,"< "&A1:A10),0)) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "Calaw" wrote: Hi, I have a list of names and I need to get the name with the highest in the alphabetical order. I dont want to sort. Thanks, Nice --ron |
#6
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
Domenic wrote...
Here's another way... If Z is higher than Y, try... =INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A 10),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:A 10),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. |
#7
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
Nice Harlan! Actually you pointed it out to me on another occasion but
I completely forgot about it. Thanks for reminding me. It's definitely much more preferable than the one I offered. In article .com, "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:A 10),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:A 10),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. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
Is there a function to give us the highest value in a list of tex
Agreed! Definitely nicer! :)
In article , Ron Rosenfeld wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) | |||
Find in list function | New Users to Excel | |||
How do I filter a list using a greater than todays date function? | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
i need a function to find the highest value in a list | Excel Worksheet Functions |