Return last non-zero value in a row?

[Riverglen]

I need a formula or function that will return the value of the last non-zero value in a row. So, if a row contains 2,6,3.....4,7,<b>,<b> the function would return 7. I can't just use a cell reference since values are periodically added to the row.

This seems like such a simple thing that it's hard to believe that there isn't an existing function for it, but I can't identify one. I'm using Excel 2000.

[jason.b75]

Are the 0's empty cells or cells containing a 0 value?

=LOOKUP(1e+100,1:1)

[Riverglen]

I can live with either option. I took a look at the LOOKUP family of functions, and didn't see a way to get what I want.

I don't understand the suggestion you made. To clarify what I'm trying to do, I want to return the found value (ie. last non-zero or non-blank) in a specified row to another cell elsewhere on the sheet.

[daddylonglegs]

Jason's suggestion returns the last number in row 1 (although that might be a zero). To return the last non-zero number try

=LOOKUP(2,1/(1:1<>0),1:1)

replace the 1:1s in that formula with whatever row or range you need, e.g. if your data is in A3:Z3 change to

=LOOKUP(2,1/(A3:Z3<>0),A3:Z3)

[jason.b75]

The formula I suggested would find the last numeric value in the row, empty cells / text will be ignored, cells with 0 will not.

1e+100 is a very large number in scientific notation, (1 followed by 100 0's), the lookup function will look for and return the last value in the specified row that is less that or equal to 1e+100.

1:1 refers to row 1.

Hope that makes sense.

[Riverglen]

Well, with the syntax clarification daddylonglegs provided, I got jason's suggested solution to work for ranges that do not end with one or more zeros. But I now realize that I really need to be able to test explicitly for the last non-zero value in rows that typically will end with one or more zero valued cells. There will never be any embedded zero valued cells anywhere else in the row however.

I tried the more elaborate version that daddylonglegs suggested and I wound up displaying the reciprocal of the last non-zero value in the row. His approach did sucessfully ignore a couple of zero valued cells at the end of the row.

I do understand the scientific notation in Jason's version, but since I am guaranteed that the largest value in the row will be less than 100, I just used 100 as the limiting value in my formula. But I still don't understand how the daddylonglegs version approach is intended to work.

[JBeaucaire]

Just expanding on the syntax given:

=LOOKUP(2, 1/((A6:Z6<>0)*ISNUMBER(A6:Z6)), A6:Z6)


http://screencast.com/t/ZlVQ22ii

[daddylonglegs]

I tried the more elaborate version that daddylonglegs suggested and I wound up displaying the reciprocal of the last non-zero value in the row.

I don't believe that should happen if you use the correct version of the suggested formula. In attached example I put this formula in AA3 and copied down to AA6

=LOOKUP(2,1/(A3:Z3<>0),A3:Z3)

In row 3 there are zero values at the end but the formula ignores these and returns the last non-zero value, i.e. 3. If there are no values in the row (or no non-zero values) you get #N/A......and if the last non-zero value is text then the formula will also return that value.

If you want to ignore text too then try Jerry's version.......or this version will also do that

=1/LOOKUP(9.99E+307,1/(A3:Z3))

In this version:

=LOOKUP(2,1/(A3:Z3<>0),A3:Z3)

A3:Z3<>0 returns an array of TRUE/FALSE values, in my example for row 3 that looks like this

{TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

[FALSE if the cell is blank or zero, TRUE otherwise]

Then you divide 1 by that array to get this array as the "lookup vector"

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

[because 1/TRUE =1 and 1/FALSE = #DIV/0!]

Now because the largest value in that array can never be > 1 we can use 2 as the "lookup value". Because 2 is never found the match is with the last number in the lookup vector (i.e. the last 1 - which corresponds to the last non-zero, non-blank value) and the corresponding value is returned from the "result vector", which is the part in red, hence retrieving the required value....

[Riverglen]

Many thanx Daddy

I tried your version again and it works fine. Must have made some sort of typo the first time I tried it. I appreciate the explanation of how it works. It's not something I would have come up with without help. I'm still kind of surprised that there isn't a built in function for doing this kind of thing.[COLOR="Silver"]