Mark said:Subject: = LOOKUP(9.99999999999999E+307,A1:H1)
I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left
Joe User said:1. In this form ("array" form), LOOKUP finds the largest
value less than or equal to the lookup value in the first
row of the lookup array. Assuming a sorted range,
presumably LOOKUP starts at the end of the range and
searches linearly.
Joe User said:Mark said:Subject: = LOOKUP(9.99999999999999E+307,A1:H1)
I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left
First, please repeat the details of the Subject line in the message body.
Not all NG readers show the entire Subject line in all contexts.
Second, typing 9.99999999999999E+307 is silly, IMHO. Not your fault; you're
just following someone else's instructions. True, that is the largest
constant that we can enter into a cell. But since it is not the largest cell
value, there is no point to entering a constant with such precision. 9E307
is probably sufficient, if you want a constant; 10^308 is probably
sufficiently better [1].
Third, apparently you do not understand what the function does, after all.
It finds the last value in the range, not the first available cell (i.e. the
cell after the last value). It does that even if the cell range is not
sorted in ascending order, which is normally a requirement for LOOKUP.
This use of LOOKUP is based on the assumption that all cell values are less
than the lookup value (e.g. 10^308). Reading the LOOKUP help page should
then give you the explanation you require. Namely:
1. In this form ("array" form), LOOKUP finds the largest value less than or
equal to the lookup value in the first row of the lookup array. Assuming a
sorted range, presumably LOOKUP starts at the end of the range and searches
linearly.
2. In this form of LOOKUP ("array" form), LOOKUP returns the value in the
same position of the last row of the lookup array that corresponds to the
value found in #1. In this usage, since there is only one row, the "last"
row is the same as the "first" row.
-----
Endnotes
[1] The largest cell value is about 1.79769313486231*10^308. If you want to
enter a similar number, but with fewer digits, be sure to truncate, not
round. Rounding will result in a #NUM error because the resulting value
would be too large. The actual largest cell value is 2*(2^1023 - 2^970). In
VBA, but not in Excel, you would write 2*2^1023 - 2^971.
----- original message -----
Mark said:Hello
I understand what the above function does, however I would like to know how
it works, in obtaining the first available cell with data in the range from
right to left....Thanks Mark
Not your fault; you're just following someone
else's instructions.
Joe User said:Mark said:Subject: = LOOKUP(9.99999999999999E+307,A1:H1)
I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left
First, please repeat the details of the Subject line in the message body.
Not all NG readers show the entire Subject line in all contexts.
Second, typing 9.99999999999999E+307 is silly, IMHO. Not your fault;
you're
just following someone else's instructions. True, that is the largest
constant that we can enter into a cell. But since it is not the largest
cell
value, there is no point to entering a constant with such precision.
9E307
is probably sufficient, if you want a constant; 10^308 is probably
sufficiently better [1].
Third, apparently you do not understand what the function does, after all.
It finds the last value in the range, not the first available cell (i.e.
the
cell after the last value). It does that even if the cell range is not
sorted in ascending order, which is normally a requirement for LOOKUP.
This use of LOOKUP is based on the assumption that all cell values are
less
than the lookup value (e.g. 10^308). Reading the LOOKUP help page should
then give you the explanation you require. Namely:
1. In this form ("array" form), LOOKUP finds the largest value less than
or
equal to the lookup value in the first row of the lookup array. Assuming
a
sorted range, presumably LOOKUP starts at the end of the range and
searches
linearly.
2. In this form of LOOKUP ("array" form), LOOKUP returns the value in the
same position of the last row of the lookup array that corresponds to the
value found in #1. In this usage, since there is only one row, the "last"
row is the same as the "first" row.
-----
Endnotes
[1] The largest cell value is about 1.79769313486231*10^308. If you want
to
enter a similar number, but with fewer digits, be sure to truncate, not
round. Rounding will result in a #NUM error because the resulting value
would be too large. The actual largest cell value is 2*(2^1023 - 2^970).
In
VBA, but not in Excel, you would write 2*2^1023 - 2^971.
----- original message -----
Mark said:Hello
I understand what the above function does, however I would like to know
how
it works, in obtaining the first available cell with data in the range
from
right to left....Thanks Mark
1. In this form ("array" form), LOOKUP finds the largest value less than or
equal to the lookup value in the first row of the lookup array. Assuming a
sorted range, presumably LOOKUP starts at the end of the range and searches
linearly.
Charles Williams said:LOOKUP and the sorted data options of VLOOKUP, HLOOKUP
and MATCH use a Binary Search algorithm
L. Howard Kittle said:Hi mark,
Put 1 through 8 in A1:H1.
In A4 enter =LOOKUP(9.99999999999999E+307,A1:H1)
This returns 8.
In A5 enter =LOOKUP(9,A1:H1)
This also returns 8.
As long as the Look_Up value is larger than any value in the Look_Up Vector,
it will return the last number in the vector.
Now change D1 to 500. A4 returns 8 and A5 returns 3. The A4 lookup value
is larger than 500 so it returns 8. The A5 lookup value is less than 500 so
it returns the 3.
Beam up help on the worksheet and check out LOOKUP and parse the examples
and the lookup rules.
The huge lookup value in the A4 formula is, I believe, the largest number
Excel will deal with, so it would, of course, return the last value in the
vector no matter how large it was. But the lookup value really only needs
to be larger than any value anticipated within the lookup vector to return
the last value in the vector.
HTH
Regards,
Howard
.
Joe User said:Mark said:Subject: = LOOKUP(9.99999999999999E+307,A1:H1)
I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left
First, please repeat the details of the Subject line in the message body.
Not all NG readers show the entire Subject line in all contexts.
Second, typing 9.99999999999999E+307 is silly, IMHO. Not your fault; you're
just following someone else's instructions. True, that is the largest
constant that we can enter into a cell. But since it is not the largest cell
value, there is no point to entering a constant with such precision. 9E307
is probably sufficient, if you want a constant; 10^308 is probably
sufficiently better [1].
Third, apparently you do not understand what the function does, after all.
It finds the last value in the range, not the first available cell (i.e. the
cell after the last value). It does that even if the cell range is not
sorted in ascending order, which is normally a requirement for LOOKUP.
This use of LOOKUP is based on the assumption that all cell values are less
than the lookup value (e.g. 10^308). Reading the LOOKUP help page should
then give you the explanation you require. Namely:
1. In this form ("array" form), LOOKUP finds the largest value less than or
equal to the lookup value in the first row of the lookup array. Assuming a
sorted range, presumably LOOKUP starts at the end of the range and searches
linearly.
2. In this form of LOOKUP ("array" form), LOOKUP returns the value in the
same position of the last row of the lookup array that corresponds to the
value found in #1. In this usage, since there is only one row, the "last"
row is the same as the "first" row.
-----
Endnotes
[1] The largest cell value is about 1.79769313486231*10^308. If you want to
enter a similar number, but with fewer digits, be sure to truncate, not
round. Rounding will result in a #NUM error because the resulting value
would be too large. The actual largest cell value is 2*(2^1023 - 2^970). In
VBA, but not in Excel, you would write 2*2^1023 - 2^971.
----- original message -----
Mark said:Hello
I understand what the above function does, however I would like to know how
it works, in obtaining the first available cell with data in the range from
right to left....Thanks Mark