non-blank entry in a range?

[Harvey Waxman]

Is there an easy way to reference the last non-blank entry in a range without
using some sort of macro?

Thanks

 

[JE McGimpsey]

Is there an easy way to reference the last non-blank entry in a range without
using some sort of macro?

One way:

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

 

[Harvey Waxman]

One way:

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

Excellent. Can you "englishify" it? I checked the help in Excel but it isn't
enough for these gray cells.

Thanks

 

[JE McGimpsey]

Excellent. Can you "englishify" it? I checked the help in Excel but it
isn't
enough for these gray cells.

By "englishify" it, I assume you mean explain what the parts do:

1) (A1:A65535<>"") creates an array of FALSE/TRUE depending on whether
the cells are blank or not, respectively.

2) 1/{TRUE,TRUE,FALSE,TRUE,...} coerces the array to 1/0 (for
TRUE/FALSE) and divides 1 by the coerced array to produce

{1,1,#DIV/0,1...}

3) LOOKUP(2,{1,1,#DIV/0,1...},A1:A65535) looks through the array for the
value 2, which obviously doesn't exist, so it finds the last value *less
than or equal to* 2 (i.e., the last 1), and returns the value in
A1:A65535 corresponding to the index of that last 1.

 

[Harvey Waxman]

By "englishify" it, I assume you mean explain what the parts do:

1) (A1:A65535<>"") creates an array of FALSE/TRUE depending on whether
the cells are blank or not, respectively.

2) 1/{TRUE,TRUE,FALSE,TRUE,...} coerces the array to 1/0 (for
TRUE/FALSE) and divides 1 by the coerced array to produce

{1,1,#DIV/0,1...}

3) LOOKUP(2,{1,1,#DIV/0,1...},A1:A65535) looks through the array for the
value 2, which obviously doesn't exist, so it finds the last value *less
than or equal to* 2 (i.e., the last 1), and returns the value in
A1:A65535 corresponding to the index of that last 1.

Thanks again.