Last non-zero

[mjones]

Hi All,

Someone gave me this formula years ago and it works great. It gets
the value of the last non-zero in a column.

Now, I need to turn it around and get the last non-zero value in a
row, but I can't figure it out. Here's the old formula:

=LOOKUP(9.99E+307,IF(Bank!H9:H65536=0,"",Bank!H9:H65536))

It looked from row H down. Now I need to look from E668 right to get
the first non-zero value.

Any help would be appreciated.

Thanks

Michele

 

[T. Valko]

Now I need to look from E668 right to

get the first non-zero value.

You subject line says last non-zero but your description says first
non-zero. So, which is it?

Assuming the range contains only numbers or possibly empty cells.

For the first non-zero (array entered**):

=INDEX(A1:E1,MATCH(TRUE,A1:E1<>0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last non-zero:

=LOOKUP(1E100,1/A1:E1,A1:E1)

 

[mjones]

You subject line says last non-zero but your description says first
non-zero. So, which is it?

Assuming the range contains only numbers or possibly empty cells.

For the first non-zero (array entered**):

=INDEX(A1:E1,MATCH(TRUE,A1:E1<>0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For the last non-zero:

=LOOKUP(1E100,1/A1:E1,A1:E1)

Hi Biff,

Thanks for your help. I see what you mean about first and last -
going left/right can be confusing. I'm getting errors on both
formulas you suggested.

I put an example file at www.quality-computing.com/test.xls.

I'm trying to get D9 to be $2,128.58 (the last (right-most) value
that's not zero in row 8) so I can use it in formulas.

I hope this makes sense.

Thanks again!

Michele

 

[T. Valko]

Ok, you just have to change the referenced range.

=LOOKUP(1E100,1/E88,E88)

--
Biff
Microsoft Excel MVP

You subject line says last non-zero but your description says first
non-zero. So, which is it?

Assuming the range contains only numbers or possibly empty cells.

For the first non-zero (array entered**):

=INDEX(A1:E1,MATCH(TRUE,A1:E1<>0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

For the last non-zero:

=LOOKUP(1E100,1/A1:E1,A1:E1)

Hi Biff,

Thanks for your help. I see what you mean about first and last -
going left/right can be confusing. I'm getting errors on both
formulas you suggested.

I put an example file at www.quality-computing.com/test.xls.

I'm trying to get D9 to be $2,128.58 (the last (right-most) value
that's not zero in row 8) so I can use it in formulas.

I hope this makes sense.

Thanks again!

Michele

 

[mjones]

Ok, you just have to change the referenced range.

=LOOKUP(1E100,1/E88,E88)

--
Biff
Microsoft Excel MVP









Hi Biff,

Thanks for your help.  I see what you mean about first and last -
going left/right can be confusing.  I'm getting errors on both
formulas you suggested.

I put an example file atwww.quality-computing.com/test.xls.

I'm trying to get D9 to be $2,128.58 (the last (right-most) value
that's not zero in row 8) so I can use it in formulas.

I hope this makes sense.

Thanks again!

Michele

Perfect! Thanks.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Ok, you just have to change the referenced range.

=LOOKUP(1E100,1/E88,E88)

--
Biff
Microsoft Excel MVP









Hi Biff,

Thanks for your help. I see what you mean about first and last -
going left/right can be confusing. I'm getting errors on both
formulas you suggested.

I put an example file atwww.quality-computing.com/test.xls.

I'm trying to get D9 to be $2,128.58 (the last (right-most) value
that's not zero in row 8) so I can use it in formulas.

I hope this makes sense.

Thanks again!

Michele

Perfect! Thanks.