G
Guest
What function or macro would I use to return the last list information in a
given range of row even when some cells are left blank?
given range of row even when some cells are left blank?
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
One way courtesy of Harlan Grove
=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)
if you are looking horizontal just change to
=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)
=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)
if you are looking horizontal just change to
=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)
R
Ragdyer
Using Row1,
For *numeric* data *only*:
=LOOKUP(9.99999999999999E+307,1:1)
For *text* data *only*:
=LOOKUP(REPT("z",255),1:1)
For text or numbers:
=INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1:1)))
For *numeric* data *only*:
=LOOKUP(9.99999999999999E+307,1:1)
For *text* data *only*:
=LOOKUP(REPT("z",255),1:1)
For text or numbers:
=INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1:1)))
R
Ragdyer
Leave it to Harlan to trump all the old standards.<g>
And since ISBLANK accepts total references,
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
And since ISBLANK accepts total references,
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
G
Guest
Raddyer,
This works perfect.....Thanks a bunch!
This works perfect.....Thanks a bunch!
R
RagDyeR
Appreciate the feed-back ... BUT ... don't you think Peo's suggestion is
more concise?
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
Raddyer,
This works perfect.....Thanks a bunch!
more concise?
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
Raddyer,
This works perfect.....Thanks a bunch!
B
Biff
Hi RD
It appears that this formula will work on an entire row but not an entire
column.
I put a number in IV1 and used:
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
Which worked.
I put a number in A65536 and used:
=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)
This did not work and returned 0.
If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.
Biff
It appears that this formula will work on an entire row but not an entire
column.
I put a number in IV1 and used:
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
Which worked.
I put a number in A65536 and used:
=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)
This did not work and returned 0.
If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.
Biff
R
RagDyeR
I noticed that also.
Maybe Harlan can explain?
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
Hi RD
It appears that this formula will work on an entire row but not an entire
column.
I put a number in IV1 and used:
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
Which worked.
I put a number in A65536 and used:
=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)
This did not work and returned 0.
If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.
Biff
Maybe Harlan can explain?
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
Hi RD
It appears that this formula will work on an entire row but not an entire
column.
I put a number in IV1 and used:
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
Which worked.
I put a number in A65536 and used:
=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)
This did not work and returned 0.
If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.
Biff
H
Harlan Grove
Ragdyer wrote...
....
Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.
....
....And since ISBLANK accepts total references,
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.
R
RagDyeR
It has to be more complex then just a "number of cells (entries)" issue
Harlan, since A1:A65536 DOES work!
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
Ragdyer wrote...
....
Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.
Harlan, since A1:A65536 DOES work!
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
Ragdyer wrote...
....
....And since ISBLANK accepts total references,
=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.
H
Harlan Grove
RagDyeR wrote...
Not on my system. First, Excel *ALWAYS* replaces A1:A65536 with A:A.
Second, in a new worksheet, the following formula entered in B1
B1:
=SUMPRODUCT(--ISBLANK(A:A))
returns 0 rather than 65536. When I change the formula to
B1:
=SUMPRODUCT(--ISBLANK(A1:A65535))
it returns 65535. What results do you get on your system?
As for the formula in this thread, . . .
with A:A still all blank, I enter the following formula in B2.
B2:
=LOOKUP(2,1/(1-ISBLANK(A:A)),ROW(A:A))
It returns 1 to start with, not #N/A and not 0. If I change it to
B2:
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))
it returns #N/A as expected (at least as I expect).
Reverting to the A:A formula, I then enter 9 in cell A11. The formula
*STILL* returns 1, not 11. What results do you get on your system?
....It has to be more complex then just a "number of cells (entries)" issue
Harlan, since A1:A65536 DOES work!
Not on my system. First, Excel *ALWAYS* replaces A1:A65536 with A:A.
Second, in a new worksheet, the following formula entered in B1
B1:
=SUMPRODUCT(--ISBLANK(A:A))
returns 0 rather than 65536. When I change the formula to
B1:
=SUMPRODUCT(--ISBLANK(A1:A65535))
it returns 65535. What results do you get on your system?
As for the formula in this thread, . . .
....Ragdyer wrote...
...
with A:A still all blank, I enter the following formula in B2.
B2:
=LOOKUP(2,1/(1-ISBLANK(A:A)),ROW(A:A))
It returns 1 to start with, not #N/A and not 0. If I change it to
B2:
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))
it returns #N/A as expected (at least as I expect).
Reverting to the A:A formula, I then enter 9 in cell A11. The formula
*STILL* returns 1, not 11. What results do you get on your system?
R
Ragdyer
You're skirting the issue Harlan!
I misspoke.
A1:A65536 *Doesn't* work.
A2:A65536 *OR* A1:A65535 *Both* work! ! !
You mentioned number of entries as the fubar of an A:A range.
All I said was it has to be more complex then that.
Come on ... 256 compared to 65,535!
Maybe Redmond has put all "entire column" references on the restricted list
because they're planning to spring that long awaited "Million" row XL on us
in the near future.<vbg>
I misspoke.
A1:A65536 *Doesn't* work.
A2:A65536 *OR* A1:A65535 *Both* work! ! !
You mentioned number of entries as the fubar of an A:A range.
All I said was it has to be more complex then that.
Come on ... 256 compared to 65,535!
Maybe Redmond has put all "entire column" references on the restricted list
because they're planning to spring that long awaited "Million" row XL on us
in the near future.<vbg>
H
Harlan Grove
....Ragdyer said:You're skirting the issue Harlan!
I misspoke.
I wasn't skirting the issue. You were being sloppy. I pointed that out.
You got defensive, and you're still in CYA mode.
You mentioned number of entries as the fubar of an A:A range.
All I said was it has to be more complex then that.
Come on ... 256 compared to 65,535!
It's not complex, it's so simple it's almost laughable. 0-65535 is the
range of unsigned 16-bit (short) integers. (Speculation, but supported
by comments made by Gnumeric developers) Excel uses short integers
internally when iterating through arrays. Excel has no problem
iterating through millions of entries in individual arrays as long as
both dimensions are less then 65536. Just try
=SUMPRODUCT(ROW(1:65535)+65535*(COLUMN(A:BH)-1))
and compare the result to Gauss's formula for the sum of 65535*60
sequential integers starting from 1
=(65535*60)*(65535*60+1)/2
The dependence on short integers and 16-bit manuipulations using
bitwise operators is the main reason Excel will remain stuck with 65536
rows. Why it's still stuck with 256 columns is a better question since
there's nothing special and certainly no built-in support for 24-bit
(256 = 2^8 by 65536 = 2^16 = 2^24) integers.
It'd REQUIRE a new file format AND a fairly thorough rewrite of most of
Excel's source code to break through the 65536 row limit. Unless
Microsoft Office's market share drops under 60%, I don't see Microsoft
expending the resources to do it. The hopeful news is that as soon as
OpenOffice 2.0 hits production, some of the Calc developers may begin
to explore expanding Calc's worksheet cell count. If so, and if that
makes it into the OOo 3.0 design specs, we may be only a few years away
from Microsoft being forced (kicking & screaming) to devote serious
developer resources to a true major upgrade for Excel. It'd be the
first true major upgrade since Excel 2000 (OLAP, VBA6, major pivot
table improvements - vs 2002 with colored worksheet tabs and PITA
'Smart'Tags, or 2003 with fixes to problems with some stats functions
that were brought to Microsoft's attention over a decade ago).
R
RagDyeR
Once again ... aaaas always ... the last word is yours.<g>
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
I wasn't skirting the issue. You were being sloppy. I pointed that out.
You got defensive, and you're still in CYA mode.
It's not complex, it's so simple it's almost laughable. 0-65535 is the
range of unsigned 16-bit (short) integers. (Speculation, but supported
by comments made by Gnumeric developers) Excel uses short integers
internally when iterating through arrays. Excel has no problem
iterating through millions of entries in individual arrays as long as
both dimensions are less then 65536. Just try
=SUMPRODUCT(ROW(1:65535)+65535*(COLUMN(A:BH)-1))
and compare the result to Gauss's formula for the sum of 65535*60
sequential integers starting from 1
=(65535*60)*(65535*60+1)/2
The dependence on short integers and 16-bit manuipulations using
bitwise operators is the main reason Excel will remain stuck with 65536
rows. Why it's still stuck with 256 columns is a better question since
there's nothing special and certainly no built-in support for 24-bit
(256 = 2^8 by 65536 = 2^16 = 2^24) integers.
It'd REQUIRE a new file format AND a fairly thorough rewrite of most of
Excel's source code to break through the 65536 row limit. Unless
Microsoft Office's market share drops under 60%, I don't see Microsoft
expending the resources to do it. The hopeful news is that as soon as
OpenOffice 2.0 hits production, some of the Calc developers may begin
to explore expanding Calc's worksheet cell count. If so, and if that
makes it into the OOo 3.0 design specs, we may be only a few years away
from Microsoft being forced (kicking & screaming) to devote serious
developer resources to a true major upgrade for Excel. It'd be the
first true major upgrade since Excel 2000 (OLAP, VBA6, major pivot
table improvements - vs 2002 with colored worksheet tabs and PITA
'Smart'Tags, or 2003 with fixes to problems with some stats functions
that were brought to Microsoft's attention over a decade ago).
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
....Ragdyer said:You're skirting the issue Harlan!
I misspoke.
I wasn't skirting the issue. You were being sloppy. I pointed that out.
You got defensive, and you're still in CYA mode.
You mentioned number of entries as the fubar of an A:A range.
All I said was it has to be more complex then that.
Come on ... 256 compared to 65,535!
It's not complex, it's so simple it's almost laughable. 0-65535 is the
range of unsigned 16-bit (short) integers. (Speculation, but supported
by comments made by Gnumeric developers) Excel uses short integers
internally when iterating through arrays. Excel has no problem
iterating through millions of entries in individual arrays as long as
both dimensions are less then 65536. Just try
=SUMPRODUCT(ROW(1:65535)+65535*(COLUMN(A:BH)-1))
and compare the result to Gauss's formula for the sum of 65535*60
sequential integers starting from 1
=(65535*60)*(65535*60+1)/2
The dependence on short integers and 16-bit manuipulations using
bitwise operators is the main reason Excel will remain stuck with 65536
rows. Why it's still stuck with 256 columns is a better question since
there's nothing special and certainly no built-in support for 24-bit
(256 = 2^8 by 65536 = 2^16 = 2^24) integers.
It'd REQUIRE a new file format AND a fairly thorough rewrite of most of
Excel's source code to break through the 65536 row limit. Unless
Microsoft Office's market share drops under 60%, I don't see Microsoft
expending the resources to do it. The hopeful news is that as soon as
OpenOffice 2.0 hits production, some of the Calc developers may begin
to explore expanding Calc's worksheet cell count. If so, and if that
makes it into the OOo 3.0 design specs, we may be only a few years away
from Microsoft being forced (kicking & screaming) to devote serious
developer resources to a true major upgrade for Excel. It'd be the
first true major upgrade since Excel 2000 (OLAP, VBA6, major pivot
table improvements - vs 2002 with colored worksheet tabs and PITA
'Smart'Tags, or 2003 with fixes to problems with some stats functions
that were brought to Microsoft's attention over a decade ago).
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.