Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41)
Can someone please explain to me what this formula is doing?
=LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41)
It finds the maximum value in the range but it works with text
So b a; z9 z7; zzzz zz, etc It finds what would be at the bottom of the range it you were to sort the range in ascending order best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dave F" wrote in message ... Can someone please explain to me what this formula is doing? =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D4
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(D6:D41,""&D6:D41)=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(D6:D41,""&D6:D41)=0)*(D6:D41<"")),E6:E 41) 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/(D6:D10<0),D6:D10) which does exactly the same and also works with the offset =LOOKUP(2,1/(D6:D10<0),E6:E10) Mike "Dave F" wrote: Can someone please explain to me what this formula is doing? =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41)
A 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(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) 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 -- Biff Microsoft Excel MVP "Dave F" wrote in message ... Can someone please explain to me what this formula is doing? =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D4
I'd use the much simpler
=LOOKUP(2,1/(D6:D10<0),D6:D10) That will fail if the range isn't sorted in ascending order. -- Biff Microsoft Excel MVP "Mike H" wrote in message ... 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(D6:D41,""&D6:D41)=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(D6:D41,""&D6:D41)=0)*(D6:D41<"")),E6:E 41) 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/(D6:D10<0),D6:D10) which does exactly the same and also works with the offset =LOOKUP(2,1/(D6:D10<0),E6:E10) Mike "Dave F" wrote: Can someone please explain to me what this formula is doing? =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D4
If you're going to assume the range is sorted in ascending order, therefore
the *last* entry in the range would be the "largest" text entry then just use: =LOOKUP(REPT("z",255),D6:D10) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I'd use the much simpler =LOOKUP(2,1/(D6:D10<0),D6:D10) That will fail if the range isn't sorted in ascending order. -- Biff Microsoft Excel MVP "Mike H" wrote in message ... 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(D6:D41,""&D6:D41)=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(D6:D41,""&D6:D41)=0)*(D6:D41<"")),E6:E 41) 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/(D6:D10<0),D6:D10) which does exactly the same and also works with the offset =LOOKUP(2,1/(D6:D10<0),E6:E10) Mike "Dave F" wrote: Can someone please explain to me what this formula is doing? =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41)
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. Biff, I did an experiment with =LOOKUP(2,1/((COUNTIF(D6:D12,""&D6:D12)=0)*(D6:D12<"")),D6:D 12) and =LOOKUP(2,1/((COUNTIF(D6:D12,""&D6:D12)=0)),D6:D12) With test data: A, B, C, D, A, B, C both gave D as expected With just one blank the first still gave D but the second gave 0: but sometimes it did give the right result - very odd all the best -- Bernard "T. Valko" wrote in message ... A 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(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) 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 -- Biff Microsoft Excel MVP "Dave F" wrote in message ... Can someone please explain to me what this formula is doing? =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how does this formula work: =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41)
I see what you mean.
When I was testing this the first time around it didn't seem to make a difference with empty cells unless all the cells were empty. Obviously I didn't test it enough! Of course, if there will be no empty cells then we don't have to worry about that. Also note that the formula is written on the assumption that there is only *text* in the range. No formula blanks, either! And this all leads to the question: Just how robust does this need to be? Only the OP knows the answer to that question. -- Biff Microsoft Excel MVP "Bernard Liengme" wrote in message ... 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. Biff, I did an experiment with =LOOKUP(2,1/((COUNTIF(D6:D12,""&D6:D12)=0)*(D6:D12<"")),D6:D 12) and =LOOKUP(2,1/((COUNTIF(D6:D12,""&D6:D12)=0)),D6:D12) With test data: A, B, C, D, A, B, C both gave D as expected With just one blank the first still gave D but the second gave 0: but sometimes it did give the right result - very odd all the best -- Bernard "T. Valko" wrote in message ... A 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(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) 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 -- Biff Microsoft Excel MVP "Dave F" wrote in message ... Can someone please explain to me what this formula is doing? =LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41) Thanks. Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
can I nest COUNTIF functions(C5:C8,"apples") AND (D5:D8,"green") | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |