A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Get Last NonBlank Value



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2008, 04:50 PM posted to microsoft.public.excel.worksheet.functions
Scott[_17_]
external usenet poster
 
Posts: 2
Default 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)

  #2  
Old September 23rd, 2008, 05:23 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Get Last NonBlank Value

One way:

=LOOKUP(2,1/((B3:E30)*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
  #3  
Old September 23rd, 2008, 05:33 PM posted to microsoft.public.excel.worksheet.functions
muddan madhu
external usenet poster
 
Posts: 695
Default 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)


  #4  
Old September 23rd, 2008, 05:42 PM posted to microsoft.public.excel.worksheet.functions
Scott[_17_]
external usenet poster
 
Posts: 2
Default 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:E30)*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


  #5  
Old September 23rd, 2008, 05:44 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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)



  #6  
Old September 23rd, 2008, 07:49 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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:E30)*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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.