If you have a bunch of letters in the cells A1 to A10, this formula will give you the "highest" letter. So if you have A,C,K,B,L,D,F, if will give the answer "L"
But I can't understand how it works:
=LOOKUP(2,1/(COUNTIF(A1:A10,">"&A1:A10&"~")=0),A1:A10)
Can anyone explain? Please
I see yahoo just removes half of my question. So it's pretty much pointless
expletive_xom
Favorite Answer
if this is your formula
=LOOKUP(2,1/( COUNTIF(A1:A10,">"& A1:A10&"~")=0),A1:A10)
you have to put spaces in the formula for Y!A to show it all.
Ron Coderre gave a detailed explanation in 2008
http://www.excelforum.com/excel-general/650324-max-for-text.html
- the ~ evens all the cells out and makes sure blanks dont throw your formula off...so 5 cells like A,C,K,,B would
end up as A~,C~,K~,~,B~
- the (COUNTIF(A1:A10,">"&A1:A10&"~")=0) creates 2 columns that compare the letters to the letters with the ~... A,C,K,,B to >A~,>C~,>K~,>~,>B~
the countif will tell you theres
3 > than the A~
1> than the C~
0> than the K~
4> than the ~
2> than the B~
- the =0 in the formula shows that the K~ count has 0 greater (so if theres 2 k's it doesnt matter, it just looks at the 1st one)...so the K~ cells convert to a TRUE and everything else gets a FALSE
- the 1/ part creates errors TRUE =1 and FALSE=0 so 1/0 will throw an error and 1/1 is always 1
- the (2 in the formula is 2 will always be higher than the 1 you got in the last step
- the LOOKUP(2 will lookup all 10 values but ignore the errors and will try to find the 2...if it doesnt find a 2 (and it wont, i am not sure why noone uses LOOKUP(1 instead of LOOKUP(2 on all these formulas) then it just looks for the 1st 1....in this case the 3rd item on the list because all the 1/0 threw an error.