LOOKUP ( )
Don't forget the old standbys:
The value of the last numeric cell in Col_A:
=LOOKUP(10^99,A:A)
The value of the last text cell in Col_A
=LOOKUP(REPT("z",255),A:A)
***********
Regards,
Ron
XL2002, WinXP
"Epinn" wrote:
Ron,
Guess what, *before* I read your post I tested "0.5" some more. I did pick up 0.5 even though it is *not* the last value. The difference between this time and last time is I sorted the array in ascending order last time but no order this time. Looks like this is temperamental.
Anyway, I agree with you that it may not be "safe" to use
=LOOKUP(2,1/A1:A100,A1:A100)
=LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100)
always returns the last non-zero, non-blank, non-error value (assuming you
don't want a final error value returned). <<
None of the formulae that we have discussed will return error values (e.g. #DIV/0!); so I won't worry about it. JMB is right about checking for zero will take care of ignoring blanks as well. Thanks, JMB.
I have done some more testing and have come to the following conclusion. Please correct me if I am wrong.
=LOOKUP(2,1/(A1:A10<""),A1:A10)
ignore blank but not 0, not text
=LOOKUP(2,1/(A1:A10<0),A1:A10)
ignore blank and zero but not text
=LOOKUP(2,1/(ISNUMBER(A1:A10)),A1:A10)
ignore blank and text but not 0
=LOOKUP(2,1/(ISTEXT(A1:A10)),A1:A10)
ignore blank, zero, numbers but not text
I am quite satisfied now unless someone tells me I am wrong on the above. I can include more conditions like ignoring blank, zero and text, but I want to give my brain a rest.
Thank you very much for your guidance, Ron. I am glad that I posted.
Epinn
"Ron Coderre" wrote in message ...
My interpretation is that for a match of "2" to happen, "2" itself has to
be in the array. The result of a calculation being "2" won't cause a match
to happen. But I am not comfortable with this and I haven't totally
convinced myself.<<
Try using these values
A1: 9
A2: 8
A3: 7
A4: 6
A5: 5
A6: 0.5
A7: 3
This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5
Consequently, I'm not so comfortable with "may be safe".
=LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100)
always returns the last non-zero, non-blank, non-error value (assuming you
don't want a final error value returned).
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Epinn" wrote:
Previously I wrote:
Afterall, the following formula may be "safe" if we want to ignore zero.
=LOOKUP(2,1/A1:A100,A1:A100)
I want to add ".... and text."
This formula is only good when the array has *numbers only*.
I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know.
(A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100)
(B) =LOOKUP(2,1/(A1:A100<""),A1:A100)
Epinn
"Epinn" wrote in message ...
Ron,
Thank you for your response.
I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later.
I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used.
Would you prefer this formula if "0" is to be ignored?
=LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100)
a last value of zero in the lookup range would be ignored because the
resulting fraction would be an error ......
"Evaluate formula" shows #DIV/0! and therefore ignored.
If the lookup range is in the right order and happens to contain 0.5 in the
right place, 1/0.5 would calculate to 2, resulting in a match.
I included 0.5 in the array and I sorted it as well.
However, the following formula did *NOT* find a match and still returned the last value.
=LOOKUP(2,1/A1:A100,A1:A100)
My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.
Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here?
Afterall, the following formula may be "safe" if we want to ignore zero.
=LOOKUP(2,1/A1:A100,A1:A100)
Epinn
"Ron Coderre" wrote in message ...
Did you mean to use this version?:
=LOOKUP(2,1/(A1:A100<""),A1:A100)
In that formula, this section:(A1:A100<"")
returns a series of 1's(for non-blanks) and 0's(for blanks)...or error
values (for cells that contain errors)
Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and
errors for blanks or errors.
Since there will be no 2's for the LOOKUP to find, it will match on the last
non-error value (LOOKUP ignores errors in the lookup range) and return the
corresponding value.
That version has advantages over the formula you posted:
=LOOKUP(2,1/A1:A100,A1:A100)
Among them are these:
If the lookup range is in the right order and happens to contain 0.5 in the
right place, 1/0.5 would calculate to 2, resulting in a match.
OR a last value of zero in the lookup range would be ignored because the
resulting fraction would be an error.
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Epinn" wrote:
Hi,
=LOOKUP(2,1/A1:A100,A1:A100)
The following is my interpretation of this formula. If I am wrong, please correct me.
I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5.
Thanks for your help.
Epinn
|