D
Dave F
Can someone please explain to me what this formula is doing?
=LOOKUP(2,1/((COUNTIF(D641,">"&D641)=0)*(D641<>"")),D641)
Thanks.
Dave
=LOOKUP(2,1/((COUNTIF(D641,">"&D641)=0)*(D641<>"")),D641)
Thanks.
Dave
=LOOKUP(2,1/(D610<>0),D610)
need that for your application but I put it in there out of habit! There's
really no difference with it or without it except when every cell in the
range is blank.
Hi,
It's making very hard work of looking up a value (2) it wont find in the
range so it will return the last instance of the maximum value.
This bit
=LOOKUP(2,1/((COUNTIF(D641,">"&D641)=0
returns either a 1 for the max value or div/0 error for anything else but
because it's looking up 2 it returns the last instance.
But, that's irrelevent because why does it matter if the first ot last
instance of max is returned. It would matter if the formula was like this
=LOOKUP(2,1/((COUNTIF(D641,">"&D641)=0)*(D641<>"")),E6:E41)
In which case it finds the last max in D and returns form column E which is
why I said it's making hard work of it.
I'd use the much simpler
=LOOKUP(2,1/(D610<>0),D610)
which does exactly the same and also works with the offset
=LOOKUP(2,1/(D610<>0),E6:E10)
Mike
This explanation is brilliantA little known fact about COUNTIF is that it evaluates TEXT for equality the
same way it evaluates numbers.
A
B
D
C
=COUNTIF(A1:A4,">A") = 3
=COUNTIF(A1:A4,">D") = 0
You wanted to return the "largest" text entry in the range.
=LOOKUP(2,1/((COUNTIF(D641,">"&D641)=0)*(D641<>"")),D641)
Here's how the formula works...
In the above formula LOOKUP uses 3 arguments:
lookup_value = 2
lookup_vector = 1/((COUNTIF(A1:A4,">"&A1:A4)=0)*(A1:A4<>""))
result_vector = A1:A4
Let's dissect the lookup_vector...
Since we want the largest text entry we "know" that the largest text entry
will have a COUNTIF result of 0 meanng there are no entries greater than the
largest text entry. See the COUNTIF formula above.
The COUNTIF will return an array of results:
COUNTIF(A1:A4,">"&A1) = COUNTIF(A1:A4,">A") = 3
COUNTIF(A1:A4,">"&B1) = COUNTIF(A1:A4,">B") = 2
COUNTIF(A1:A4,">"&C1) = COUNTIF(A1:A4,">D") = 0
COUNTIF(A1:A4,">"&D1) = COUNTIF(A1:A4,">C") = 1
We then test these results for equality to 0:
COUNTIF(A1:A4,">A") = 3 = 0 = FALSE
COUNTIF(A1:A4,">B") = 2 = 0 = FALSE
COUNTIF(A1:A4,">D") = 0 = 0 = TRUE
COUNTIF(A1:A4,">C") = 1 = 0 = FALSE
This array is then multiplied together with this array: (A1:A4<>"")
This is simply testing to make sure the cells are not blank. You may not
need that for your application but I put it in there out of habit! There's
really no difference with it or without it except when every cell in the
range is blank.
(A1<>"") = TRUE
(B1<>"") = TRUE
(C1<>"") = TRUE
(D1<>"") = TRUE
{F;F;T;F}*{T;T;T;T} ={0;0;1;0}
Ok, now comes the confusing part!!!
The way that LOOKUP works is:
If the lookup_value is greater than any value in lookup_vector, it will
match the last value in the lookup_vector that is less than the
lookup_value. The lookup_value is 2 and the lookup_vector is 0;0;1;0. The
lookup_value 2 IS greater than any value in the lookup_vector so it will
match the last value in the lookup_vector that is less than 2 and that
value is the last 0.
Now comes the really confusing part!!!
The last value that is less than the lookup_value 2 is the last 0.
However, this can't be our match since 0 means this entry failed to meet the
conditions of the COUNTIF=0 and/or the test for blank. So, we need to do
something about that.
LOOKUP ignores error values so we are going to use that to our advantage. We
divide 1 by the the array {0;0;1;0} knowing that any number divided by 0
returns the #DIV/0! error. So:
1/0 = #DIV/0!
1/0 = #DIV/0!
1/1 = 1
1/0 = #DIV/0!
So that becomes the array for the lookup_vector (D=#DIV/0!):
{D;D;1;D}
Now, the last value in the lookup_vector that is less than the
lookup_value 2 is 1.
So the final result of the formula is the entry in the result_vector which
is the range A1:A4 that corresponds to 1.
=LOOKUP(2,{D;D;1;D},{"A";"B";"D";"C"})
=D
D is the largest TEXT entry in the range.
So there you have it!
exp101