is there a function that will return the first non-zero cell in a given range?

For example, I have a range of 20 cells in a row (say column A through
column T), and I want the function (or seried of functions) to look within
the range and return the value furthest to the right. Like, in row 1, the
first value is in column C. In row 2, the first value may be in column F. In
row 3, the first value may be in column S.

Thanks for the help.

Try this:

I'm assuming that "value" means number.

=LOOKUP(10^10,A1:T1)

Biff

"mlh97" wrote in message
...
is there a function that will return the first non-zero cell in a given
range?

For example, I have a range of 20 cells in a row (say column A through
column T), and I want the function (or seried of functions) to look within
the range and return the value furthest to the right. Like, in row 1, the
first value is in column C. In row 2, the first value may be in column F.
In
row 3, the first value may be in column S.

Thanks for the help.

Hi,

Your question is unclear - in one place you ask for the "first non zero
cell" in another place you ask for the "value farthest to the right". These
two requests are exactly opposite.

1. The following array formula will deal with the first one:
=INDEX(B2:L2,1,MATCH(TRUE,B2:L2<0,0))
This assumes that you mean the first non blank/non zero cell. If you want to
find cells that are empty then I will need to modify the formula.

2. If the numbers in the cells are small, <387,420,489 then you can use the
formula:
=LOOKUP(9^9,B1:L1) to find the last entry to the right

--
Cheers,
Shane Devenshire


"mlh97" wrote:

is there a function that will return the first non-zero cell in a given range?

For example, I have a range of 20 cells in a row (say column A through
column T), and I want the function (or seried of functions) to look within
the range and return the value furthest to the right. Like, in row 1, the
first value is in column C. In row 2, the first value may be in column F. In
row 3, the first value may be in column S.

Thanks for the help.

I think you want a dynamic name range? E.g., calculating the average of
a row for which you need to ignore the zero's? If so, look into the
OFFSET function then.

ShaneDevenshire;2206443 Wrote:
Hi,

Your question is unclear - in one place you ask for the "first non zero

cell" in another place you ask for the "value farthest to the right".
These
two requests are exactly opposite.

1. The following array formula will deal with the first one:
=INDEX(B2:L2,1,MATCH(TRUE,B2:L20,0))
This assumes that you mean the first non blank/non zero cell. If you
want to
find cells that are empty then I will need to modify the formula.

2. If the numbers in the cells are small, 387,420,489 then you can use
the
formula:
=LOOKUP(9^9,B1:L1) to find the last entry to the right

--
Cheers,
Shane Devenshire


"mlh97" wrote:
-
is there a function that will return the first non-zero cell in a
given range?

For example, I have a range of 20 cells in a row (say column A through

column T), and I want the function (or seried of functions) to look
within
the range and return the value furthest to the right. Like, in row 1,
the
first value is in column C. In row 2, the first value may be in column
F. In
row 3, the first value may be in column S.

Thanks for the help.-




--
Henk57