don't know how to ask the Q

[Guest]

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.

 

[T. Valko]

Try this:

I'm assuming that "value" means number.

=LOOKUP(10^10,A1:T1)

Biff

 

[Guest]

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

 

[Henk57]

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