Find last nonblank in a column

See my contrib in:

http://tinyurl.com/7ysq5

davidm wrote:
> Can someone please explain how this formula works
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> in the context LOOKUP vector having the following syntax
> LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_VECTOR)
>
> If Column L contains data, ISNUMBER(L1:L1000) evaluates to TRUE (or 1)
> with the reciprocal also being 1. Parsed to the LOOKUP function, the
> result yields
> =LOOKUP(2,1,L1:L1000) - which is nothing like the conventional syntax
> quoted above. I am stumped.
>
> TIA
>
>

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>
> LOOKUP() effects a binary search (see:
> http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.
>

Thank you Aladin.

I don't profess to understand all the information in link that you posted
but I took from it that in the worst case scenario it would only take 17
comparisons to produce a result for 65536 rows which, as you said, is very
fast

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Sandy Mann wrote:
> Aladin,
>
> I accept that your use of LOOKUP is more efficient than mine, (well my
> plagiarised version really, probably the only original thought I have had
> was, "I wonder if there is anything on the net about Excel?"), for one thing
> 'mine' used two function calls. So that I can expand my understanding of
> how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
> range? If it does check all rows, is it still faster than a limited range -
> say 1,000 rows. I other words is it 65 times faster?
>

LOOKUP() effects a binary search (see:
http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.

In

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

it's not LOOKUP itself that requires time. Rather:

(a) the evaluation of ISNUMBER(L1:L1000), and

(b) 1/Expression

Hence, the formula

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

is significantly less efficient compared to

=LOOKUP(9.99999999999999E+307,L:L)

When the interest is in retrieving the last numeric value, one would
should invoke the latter, not the former.

Aladin,

I accept that your use of LOOKUP is more efficient than mine, (well my
plagiarised version really, probably the only original thought I have had
was, "I wonder if there is anything on the net about Excel?"), for one thing
'mine' used two function calls. So that I can expand my understanding of
how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
range? If it does check all rows, is it still faster than a limited range -
say 1,000 rows. I other words is it 65 times faster?

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> That puts efficiency in back seat. <g>
>
> George W. Barrowcliff wrote:
>> Thanks, for the quick responses. Does exactly what I needed to do.
>>
>>
>> "Sandy Mann" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>>George,
>>>
>>>Provided that you do not mean WHOLE column when you say column of numbers
>>>try:
>>>
>>>=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>>>
>>>--
>>>HTH
>>>
>>>Sandy
>>>[email protected]
>>>Replace@mailinator with @tiscali.co.uk
>>>
>>>
>>>"George W. Barrowcliff" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>>How can I find the last nonblank value in a column of numbers?
>>>>
>>>>TIA
>>>>
>>>>GWB
>>>>
>>>>
>>>
>>>
>>
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

See my contrib in:

http://tinyurl.com/7ysq5

davidm wrote:
> Can someone please explain how this formula works
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> in the context LOOKUP vector having the following syntax
> LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_VECTOR)
>
> If Column L contains data, ISNUMBER(L1:L1000) evaluates to TRUE (or 1)
> with the reciprocal also being 1. Parsed to the LOOKUP function, the
> result yields
> =LOOKUP(2,1,L1:L1000) - which is nothing like the conventional syntax
> quoted above. I am stumped.
>
> TIA
>
>

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>
> LOOKUP() effects a binary search (see:
> http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.
>

Thank you Aladin.

I don't profess to understand all the information in link that you posted
but I took from it that in the worst case scenario it would only take 17
comparisons to produce a result for 65536 rows which, as you said, is very
fast

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Aladin,

I accept that your use of LOOKUP is more efficient than mine, (well my
plagiarised version really, probably the only original thought I have had
was, "I wonder if there is anything on the net about Excel?"), for one thing
'mine' used two function calls. So that I can expand my understanding of
how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
range? If it does check all rows, is it still faster than a limited range -
say 1,000 rows. I other words is it 65 times faster?

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> That puts efficiency in back seat. <g>
>
> George W. Barrowcliff wrote:
>> Thanks, for the quick responses. Does exactly what I needed to do.
>>
>>
>> "Sandy Mann" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>>George,
>>>
>>>Provided that you do not mean WHOLE column when you say column of numbers
>>>try:
>>>
>>>=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>>>
>>>--
>>>HTH
>>>
>>>Sandy
>>>[email protected]
>>>Replace@mailinator with @tiscali.co.uk
>>>
>>>
>>>"George W. Barrowcliff" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>>How can I find the last nonblank value in a column of numbers?
>>>>
>>>>TIA
>>>>
>>>>GWB
>>>>
>>>>
>>>
>>>
>>
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

Sandy Mann wrote:
> Aladin,
>
> I accept that your use of LOOKUP is more efficient than mine, (well my
> plagiarised version really, probably the only original thought I have had
> was, "I wonder if there is anything on the net about Excel?"), for one thing
> 'mine' used two function calls. So that I can expand my understanding of
> how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
> range? If it does check all rows, is it still faster than a limited range -
> say 1,000 rows. I other words is it 65 times faster?
>

LOOKUP() effects a binary search (see:
http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.

In

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

it's not LOOKUP itself that requires time. Rather:

(a) the evaluation of ISNUMBER(L1:L1000), and

(b) 1/Expression

Hence, the formula

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

is significantly less efficient compared to

=LOOKUP(9.99999999999999E+307,L:L)

When the interest is in retrieving the last numeric value, one would
should invoke the latter, not the former.

See my contrib in:

http://tinyurl.com/7ysq5

davidm wrote:
> Can someone please explain how this formula works
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> in the context LOOKUP vector having the following syntax
> LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_VECTOR)
>
> If Column L contains data, ISNUMBER(L1:L1000) evaluates to TRUE (or 1)
> with the reciprocal also being 1. Parsed to the LOOKUP function, the
> result yields
> =LOOKUP(2,1,L1:L1000) - which is nothing like the conventional syntax
> quoted above. I am stumped.
>
> TIA
>
>

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>
> LOOKUP() effects a binary search (see:
> http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.
>

Thank you Aladin.

I don't profess to understand all the information in link that you posted
but I took from it that in the worst case scenario it would only take 17
comparisons to produce a result for 65536 rows which, as you said, is very
fast

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Sandy Mann wrote:
> Aladin,
>
> I accept that your use of LOOKUP is more efficient than mine, (well my
> plagiarised version really, probably the only original thought I have had
> was, "I wonder if there is anything on the net about Excel?"), for one thing
> 'mine' used two function calls. So that I can expand my understanding of
> how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
> range? If it does check all rows, is it still faster than a limited range -
> say 1,000 rows. I other words is it 65 times faster?
>

LOOKUP() effects a binary search (see:
http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.

In

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

it's not LOOKUP itself that requires time. Rather:

(a) the evaluation of ISNUMBER(L1:L1000), and

(b) 1/Expression

Hence, the formula

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

is significantly less efficient compared to

=LOOKUP(9.99999999999999E+307,L:L)

When the interest is in retrieving the last numeric value, one would
should invoke the latter, not the former.

Aladin,

I accept that your use of LOOKUP is more efficient than mine, (well my
plagiarised version really, probably the only original thought I have had
was, "I wonder if there is anything on the net about Excel?"), for one thing
'mine' used two function calls. So that I can expand my understanding of
how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
range? If it does check all rows, is it still faster than a limited range -
say 1,000 rows. I other words is it 65 times faster?

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> That puts efficiency in back seat. <g>
>
> George W. Barrowcliff wrote:
>> Thanks, for the quick responses. Does exactly what I needed to do.
>>
>>
>> "Sandy Mann" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>>George,
>>>
>>>Provided that you do not mean WHOLE column when you say column of numbers
>>>try:
>>>
>>>=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>>>
>>>--
>>>HTH
>>>
>>>Sandy
>>>[email protected]
>>>Replace@mailinator with @tiscali.co.uk
>>>
>>>
>>>"George W. Barrowcliff" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>>How can I find the last nonblank value in a column of numbers?
>>>>
>>>>TIA
>>>>
>>>>GWB
>>>>
>>>>
>>>
>>>
>>
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

Aladin,

I accept that your use of LOOKUP is more efficient than mine, (well my
plagiarised version really, probably the only original thought I have had
was, "I wonder if there is anything on the net about Excel?"), for one thing
'mine' used two function calls. So that I can expand my understanding of
how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
range? If it does check all rows, is it still faster than a limited range -
say 1,000 rows. I other words is it 65 times faster?

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> That puts efficiency in back seat. <g>
>
> George W. Barrowcliff wrote:
>> Thanks, for the quick responses. Does exactly what I needed to do.
>>
>>
>> "Sandy Mann" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>>George,
>>>
>>>Provided that you do not mean WHOLE column when you say column of numbers
>>>try:
>>>
>>>=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>>>
>>>--
>>>HTH
>>>
>>>Sandy
>>>[email protected]
>>>Replace@mailinator with @tiscali.co.uk
>>>
>>>
>>>"George W. Barrowcliff" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>>How can I find the last nonblank value in a column of numbers?
>>>>
>>>>TIA
>>>>
>>>>GWB
>>>>
>>>>
>>>
>>>
>>
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

Sandy Mann wrote:
> Aladin,
>
> I accept that your use of LOOKUP is more efficient than mine, (well my
> plagiarised version really, probably the only original thought I have had
> was, "I wonder if there is anything on the net about Excel?"), for one thing
> 'mine' used two function calls. So that I can expand my understanding of
> how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
> range? If it does check all rows, is it still faster than a limited range -
> say 1,000 rows. I other words is it 65 times faster?
>

LOOKUP() effects a binary search (see:
http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.

In

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

it's not LOOKUP itself that requires time. Rather:

(a) the evaluation of ISNUMBER(L1:L1000), and

(b) 1/Expression

Hence, the formula

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

is significantly less efficient compared to

=LOOKUP(9.99999999999999E+307,L:L)

When the interest is in retrieving the last numeric value, one would
should invoke the latter, not the former.

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>
> LOOKUP() effects a binary search (see:
> http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.
>

Thank you Aladin.

I don't profess to understand all the information in link that you posted
but I took from it that in the worst case scenario it would only take 17
comparisons to produce a result for 65536 rows which, as you said, is very
fast

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

See my contrib in:

http://tinyurl.com/7ysq5

davidm wrote:
> Can someone please explain how this formula works
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> in the context LOOKUP vector having the following syntax
> LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_VECTOR)
>
> If Column L contains data, ISNUMBER(L1:L1000) evaluates to TRUE (or 1)
> with the reciprocal also being 1. Parsed to the LOOKUP function, the
> result yields
> =LOOKUP(2,1,L1:L1000) - which is nothing like the conventional syntax
> quoted above. I am stumped.
>
> TIA
>
>

Sandy Mann wrote:
> Aladin,
>
> I accept that your use of LOOKUP is more efficient than mine, (well my
> plagiarised version really, probably the only original thought I have had
> was, "I wonder if there is anything on the net about Excel?"), for one thing
> 'mine' used two function calls. So that I can expand my understanding of
> how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
> range? If it does check all rows, is it still faster than a limited range -
> say 1,000 rows. I other words is it 65 times faster?
>

LOOKUP() effects a binary search (see:
http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.

In

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

it's not LOOKUP itself that requires time. Rather:

(a) the evaluation of ISNUMBER(L1:L1000), and

(b) 1/Expression

Hence, the formula

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

is significantly less efficient compared to

=LOOKUP(9.99999999999999E+307,L:L)

When the interest is in retrieving the last numeric value, one would
should invoke the latter, not the former.

Aladin,

I accept that your use of LOOKUP is more efficient than mine, (well my
plagiarised version really, probably the only original thought I have had
was, "I wonder if there is anything on the net about Excel?"), for one thing
'mine' used two function calls. So that I can expand my understanding of
how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
range? If it does check all rows, is it still faster than a limited range -
say 1,000 rows. I other words is it 65 times faster?

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> That puts efficiency in back seat. <g>
>
> George W. Barrowcliff wrote:
>> Thanks, for the quick responses. Does exactly what I needed to do.
>>
>>
>> "Sandy Mann" <[email protected]> wrote in message
>> news:%[email protected]...
>>
>>>George,
>>>
>>>Provided that you do not mean WHOLE column when you say column of numbers
>>>try:
>>>
>>>=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>>>
>>>--
>>>HTH
>>>
>>>Sandy
>>>[email protected]
>>>Replace@mailinator with @tiscali.co.uk
>>>
>>>
>>>"George W. Barrowcliff" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>>How can I find the last nonblank value in a column of numbers?
>>>>
>>>>TIA
>>>>
>>>>GWB
>>>>
>>>>
>>>
>>>
>>
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.

That puts efficiency in back seat. <g>

George W. Barrowcliff wrote:
> Thanks, for the quick responses. Does exactly what I needed to do.
>
>
> "Sandy Mann" <[email protected]> wrote in message
> news:%[email protected]...
>
>>George,
>>
>>Provided that you do not mean WHOLE column when you say column of numbers
>>try:
>>
>>=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>>
>>--
>>HTH
>>
>>Sandy
>>[email protected]
>>Replace@mailinator with @tiscali.co.uk
>>
>>
>>"George W. Barrowcliff" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>How can I find the last nonblank value in a column of numbers?
>>>
>>>TIA
>>>
>>>GWB
>>>
>>>
>>
>>
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>
> LOOKUP() effects a binary search (see:
> http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.
>

Thank you Aladin.

I don't profess to understand all the information in link that you posted
but I took from it that in the worst case scenario it would only take 17
comparisons to produce a result for 65536 rows which, as you said, is very
fast

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Thanks, for the quick responses. Does exactly what I needed to do.


"Sandy Mann" <[email protected]> wrote in message
news:%[email protected]...
> George,
>
> Provided that you do not mean WHOLE column when you say column of numbers
> try:
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> --
> HTH
>
> Sandy
> [email protected]
> Replace@mailinator with @tiscali.co.uk
>
>
> "George W. Barrowcliff" <[email protected]> wrote in message
> news:[email protected]...
> > How can I find the last nonblank value in a column of numbers?
> >
> > TIA
> >
> > GWB
> >
> >
>
>

=LOOKUP(9.99999999999999E+307,A:A)

which will return the last numeric value from column A if any.

George W. Barrowcliff wrote:
> How can I find the last nonblank value in a column of numbers?
>
> TIA
>
> GWB
>
>

See my contrib in:

http://tinyurl.com/7ysq5

davidm wrote:
> Can someone please explain how this formula works
>
> =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)
>
> in the context LOOKUP vector having the following syntax
> LOOKUP(LOOKUP_VALUE,LOOKUP_VECTOR,RESULT_VECTOR)
>
> If Column L contains data, ISNUMBER(L1:L1000) evaluates to TRUE (or 1)
> with the reciprocal also being 1. Parsed to the LOOKUP function, the
> result yields
> =LOOKUP(2,1,L1:L1000) - which is nothing like the conventional syntax
> quoted above. I am stumped.
>
> TIA
>
>

George,

Provided that you do not mean WHOLE column when you say column of numbers
try:

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"George W. Barrowcliff" <[email protected]> wrote in message
news:[email protected]...
> How can I find the last nonblank value in a column of numbers?
>
> TIA
>
> GWB
>
>

Try this:

=LOOKUP(2,1/(1-ISBLANK(F1:F1000)),F1:F1000)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George W. Barrowcliff" <[email protected]> wrote in message
news:[email protected]...
How can I find the last nonblank value in a column of numbers?

TIA

GWB

How can I find the last nonblank value in a column of numbers?

TIA

GWB