Get Last NonBlank Value
 

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)

Get Last NonBlank Value
 

One way:

=LOOKUP(2,1/((B3:E3<0)*ISNUMBER(B3:E3)),B3:E3)

Scott wrote:

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)

--

Dave Peterson

Get Last NonBlank Value
 

Try this

=LOOKUP(2,1/(B3:E30),B3:E3)

On Sep 23, 11:50*am, "Scott" wrote:
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)

Get Last NonBlank Value
 

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:E30),B3:E3)


"Dave Peterson" wrote in message
...
One way:

=LOOKUP(2,1/((B3:E3<0)*ISNUMBER(B3:E3)),B3:E3)

Scott wrote:

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)

--

Dave Peterson

Get Last NonBlank Value
 

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

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


--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
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)

Get Last NonBlank Value
 

(b3:e30)
returns an array of true's and false's
1/(b3:e30)
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.

Scott wrote:

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:E30),B3:E3)

"Dave Peterson" wrote in message
...
One way:

=LOOKUP(2,1/((B3:E3<0)*ISNUMBER(B3:E3)),B3:E3)

Scott wrote:

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)

--

Dave Peterson

--

Dave Peterson