Can anybody explain how this Excel formula works?

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...

- 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.