That looks like a simple formula, but it really is an advanced formula. Explaining it will be a challenge. Let's see how I do.
With
A1:A10 containing text (and possibly some blanks)
This formula returns the Maximum text value.
That maximum is the item that would appear at the bottom of the list
if you were to sort the list in ascending order.
The COUNTIF function can determine if one text value is greater than
another (using the ascending order logic I mentioned). One possible sticking
point would be blank cells. The formula would indicate that blanks are
larger than any other cells...and return a zero for each blank....the same
value that the "largest" text value will return. So...we append a tilde (~) to
every cell we test. The tilde is one of the "smallest" values you can type
with one key on the keyboard. Now,
This part of the formula: COUNTIF(A1:A10,">"&A1:A10&"~")
returns an array of 10 values (one for each cell we test). Each value
represents the number of cells in the range on the left of the formula
that are "larger" than the value in the test cell in the right of the formula.
Example:
A1: a
A2: b
A3: c
A4:A10 are blank
2 values are greater than "a"
1 value is greater than "b"
0 values are greater than "c"
For each blank cell, 3 values are greater than "~".
In that example, this formula: COUNTIF(A1:A10,">"&A1:A10&"~")
would return this array: {2;1;0;3;3;3;3;3;3;3}
The 0 in that array represents the cell that has no other cells
larger than it (A3 with "c").
This formula section: (COUNTIF(A1:A10,">"&A1:A10&"~")=0)
test if each value in that array equals 0 (zero).
The result is a series of TRUE/FALSE values which, when divided into 1,
are converted into 1's and 0's, respectively.
Still using our sample data, (COUNTIF(A1:A10,">"&A1:A10&"~")=0)
returns
and this: 1/(COUNTIF(A1:A10,">"&A1:A10&"~")=0)
is the equivalent of:
which calcs to:
Note: 1/0 returns an error.
As if that isn't confusing enough....here comes the tricky part!
1) The LOOKUP formula ignores error values.
2) If the LOOKUP formula is trying to find a value that is larger than
any other value in the list....it simply returns the LAST VALID ITEM in the list.
In our case, we will have a 1 in the midst of a buch of error values.
Consequently, that 1 is the only valid item in the lookup list!
So....
Tries to find the number 2 in the lookup list. Whatever value the
LOOKUP function determines is a match, that value's corresponding item
in the value list will be returned.
Since 2 is larger than any value in:
The LOOKUP function matches on 1 (the 3rd item in the list)
and returns the 3rd item in A1:A10....which is "c".
I hope that helps.
Bookmarks