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:H 65536))

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

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)

--
Biff
Microsoft Excel MVP


"mjones" wrote in message
...
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:H 65536))

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

On Nov 16, 12:09*am, "T. Valko" wrote:
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)

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

...

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:H 65536))

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

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

Ok, you just have to change the referenced range.

=LOOKUP(1E100,1/E8:P8,E8:P8)

--
Biff
Microsoft Excel MVP


"mjones" wrote in message
...
On Nov 16, 12:09 am, "T. Valko" wrote:
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)

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

...

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:H 65536))

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

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

On Nov 16, 1:25*pm, "T. Valko" wrote:
Ok, you just have to change the referenced range.

=LOOKUP(1E100,1/E8:P8,E8:P8)

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

...
On Nov 16, 12:09 am, "T. Valko" wrote:



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)

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

....

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:H 65536))

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

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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"mjones" wrote in message
...
On Nov 16, 1:25 pm, "T. Valko" wrote:
Ok, you just have to change the referenced range.

=LOOKUP(1E100,1/E8:P8,E8:P8)

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

...
On Nov 16, 12:09 am, "T. Valko" wrote:



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)

--
Biff
Microsoft Excel MVP

"mjones" wrote in message

...

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:H 65536))

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

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.