Get Last NonBlank Value

S

Scott

In the range (B3:E3) below, I'm using FORMULA 1 and FORMULA 2 below to
retrieve the last value that is "NonBlank and Not Equal To Zero".

Both FORMULA 1 and FORMULA 2 will both retrieve the last value within the
range that is "NonBlank", but they both return a zero if the last value is a
zero.

I need help to come up with a formula that will return the value "2000" in
the below example, instead of zero. Any ideas?


Example Cells
*************************************************

A B C D
E
1
2 4000 2000 0
3
4


FORMULA 1:
***************

=LOOKUP(9^9, B3:E3)



FORMULA 2:
***************

=LOOKUP(2,1/ISNUMBER(B3:E3),B3:E3)
 
S

Scott

I found another way, but I need some help understanding it and your example.
Please example what the "1/(B3:E3)>0) part is. It looks like the value found
is being divided into 1.

What is the logic there?

=LOOKUP(2,1/(B3:E3>0),B3:E3)
 
T

T. Valko

Assuming the range contains numbers only (no formulas that return formula
blanks):

=LOOKUP(1E100,1/B3:E3,B3:E3)
 
D

Dave Peterson

(b3:e3>0)
returns an array of true's and false's
1/(b3:e3>0)
returns an array of 1's and errors (divided by 0 errors)

the =lookup() is looking for a match for the number 2 in that array of 1's and
errors. Since there are no 2's in that array and since it ignores the errors,
it'll use the last 1 in that array.

Then it uses that "index" into the last range (b3:E3).

But if your data can contain strings (even =""), you may want to do some more
testing--if you really want the last number returned.
 

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.

Ask a Question

Top