How do I choose first value greater than 0?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jagruc - 26 Aug 2006 03:11 GMT

I have a worksheet with data by month in columns. I want to look
through the data consecutively and find the first value greater than 0
in each row.

I can do this with an imbedded if statement, but I did not know until
recently that you can only have 8 IF's. So it takes two if formulas,
the first with 8 IFs the second with five, one to compare to the result
of the first IF formula and the rest to check the final 4 months of
data.

I tried to figure out a way to use the CHOOSE option to make this work
but had no success there.

Is there a better way to do this?

Signature

jagruc

Reply to this Message

Carim - 26 Aug 2006 09:10 GMT

Hi,

Take a look at Tom's solution in following post,
http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/
thread/5ffa10f74b324e0d/2991947f4f49f236?lnk=gst&q=first+positive+value&rnum=1#2
991947f4f49f236

and adjust to your needs ...

HTH
Cheers
Carim

Reply to this Message

Biff - 26 Aug 2006 22:18 GMT

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDEX(B1:M1,MATCH(TRUE,B1:M1>0,0))

Biff

> I have a worksheet with data by month in columns. I want to look
> through the data consecutively and find the first value greater than 0
[quoted text clipped - 10 lines]
>
> Is there a better way to do this?

Reply to this Message