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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|