Last NonBlank cell in a range

Koffiepit

unread,

Apr 20, 2008, 12:12:55 AM4/20/08

to

Does anybody know how to construct a worksheet function that displays the
location, or better still, the value, of the last non-blank cell in a large
range like the second row in a spreadsheet for instance?

O yes, and if you do know, could you explain how it works?

Much appreciated in advance.

Koffiepit

yshridhar

unread,

Apr 20, 2008, 12:26:00 AM4/20/08

to

=LOOKUP(255,A2:Z2) - for numeric value
=LOOKUP(REPT("z",255),A2:Z2) - for text value
best wishes
Sreedhar

Lars-Åke Aspelin

unread,

Apr 20, 2008, 2:27:11 AM4/20/08

to

Assuming "the last non-blank cell" is defined as the rightmost
non-blank cell in the last row of the range that has any non-blank
cell you can try this to get the value og the last non-blank cell:

=OFFSET(A1,INT(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(myrange)+COLUMN(myrange)))/COLUMNS(1:1))-1,
MOD(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(myrange)+COLUMN(myrange)))/COLUMNS(1:1),1)*COLUMNS(1:1)-1)

myrange is the range in question.
This should be entered, everything on one line, as an array formula,
i.e. by pressing CTRL+SHIFT+ENTER rather than just ENTER

This is how it works:

The ISBLANK(myrange) part gives an array with TRUE for all blank cells
and FALSE for all non-blank cell

The COLUMNS(1:1)*ROW(myrange)+COLUMN(myrange) part gives an array
with a "cell number" for each cell in the range. The number
COLUMNS(1:1) part is there to make the number unique for all cells.

When these two arrays are multiplied you get an array with zeroes for
the blank cells and the "cell number" for the non-blank cells.

MAX then finds the maximum "cell number" for a non-blank cell.

The INT and MOD parts are there to transform the cell number to row
and column for the cell.

INT(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(myrange)+COLUMN(myrange)))/COLUMNS(1:1))
gives the row number (r) of the cell

MOD(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(myrange)+COLUMN(myrange)))/COLUMNS(1:1),1)*COLUMNS(1:1)
gives the column number (c) of the cell

and OFFSET(A1,r-1,c-1) finally gives the value of the cell with row
number r and column number c

Hope this helps / Lars-Åke

Domenic

unread,

Apr 20, 2008, 9:34:17 AM4/20/08

to

For the last numerical value in a range, try...

=LOOKUP(9.99999999999999E+307,Range)

Hope this helps!

In article <C3B094A9-336A-4CC0...@microsoft.com>,

Ragdyer

unread,

Apr 20, 2008, 1:32:21 PM4/20/08

to

This will display the value in the last non-blank cell in Row2, *either*
Text or Number:

=LOOKUP(2,1/(2:2<>""),2:2)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Koffiepit" <koff...@hotmail.com> wrote in message
news:C3B094A9-336A-4CC0...@microsoft.com...

Ragdyer

unread,

Apr 20, 2008, 2:18:43 PM4/20/08

to

If you would like to see how it works, change the formula to a smaller range
so that the formula evaluation will display - without a "Too Large" error.

Say 10 cells:
=LOOKUP(2,1/(A2:J2<>""),A2:J2)

Enter this formula in A1, and put a value in any 2 cells within the range.

Now, in the formula bar, select *only*:
(A2:J2<>"")
And hit <F9>

You see an array of True and False, where the populated cells return True.

Hit <Esc> to revert back to the formula without destroying it.

Trues evaluate to 1's and Falses to 0's.

So, now select in the formula bar *only*:
1/(A2:J2<>"")
And hit <F9>

You now see an array of 1's and #DIV/0! errors.
This comes from dividing 1 by 1 and 1 by 0.
Hit <Esc>

In this form of the Lookup function, the one dimension lookup vector is
between the first and second commas.
This means the lookup vector is *NOT* A2:J2,
But *IS* the array of 1's and #DIV/0! errors.

The lookup value in this formula is 2, but there is *no* 2 in the lookup
vector.

NOW, follow this anomaly:
The Lookup() function pre-supposes that the lookup vector is sorted,
ascending.
If it can't find the lookup value, it's programmed to find the largest value
in the lookup vector that is *less* than or equal to the lookup value.
It by-passes the errors, and since it believes that the lookup vector is
sorted ascending, it returns the *last* 1, which, if sorted, *should* be the
largest value that's less then the lookup value.

So here, the lookup value of 2 cannot exist and is *never* found.

This anomaly can also work if you're just looking for the last numerical
value in a range, or just the last text value in a range.

For numbers, make the lookup value larger then any number that may possibly
exist in the lookup vector:
=LOOKUP(99^99,2:2)

And the same concept for text:
=LOOKUP(REPT("z",255),2:2)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ragdyer" <Rag...@cutoutmsn.com> wrote in message
news:%23E8lKzw...@TK2MSFTNGP06.phx.gbl...

Dave

unread,

Apr 21, 2008, 10:04:01 AM4/21/08

to

Hi Raqdyer,
Just jumping in, thanks for the function, and the explanation. Understanding
is always better than just using.
Regards - Dave.

RagDyer

unread,

Apr 21, 2008, 7:10:16 PM4/21/08

to

You're quite welcome!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dave" <Da...@discussions.microsoft.com> wrote in message
news:1E23A2D3-B972-4E41...@microsoft.com...