don't know how to ask the Q

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlh97 - 15 Jun 2007 22:05 GMT

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.

Reply to this Message

T. Valko - 15 Jun 2007 22:17 GMT

Try this:

I'm assuming that "value" means number.

=LOOKUP(10^10,A1:T1)

Biff

> is there a function that will return the first non-zero cell in a given
> range?
[quoted text clipped - 7 lines]
>
> Thanks for the help.

Reply to this Message

ShaneDevenshire - 16 Jun 2007 06:46 GMT

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

Signature

Cheers,
Shane Devenshire

> is there a function that will return the first non-zero cell in a given range?
>
[quoted text clipped - 5 lines]
>
> Thanks for the help.

Reply to this Message

Henk57 - 16 Jun 2007 16:23 GMT

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.

ShaneDevenshire;2206443 Wrote:

> Hi,
>
[quoted text clipped - 30 lines]
>
> Thanks for the help.

--
Henk57

Reply to this Message