find first non-zero value in a vertical list of numbers
 

Looking for a formaula that finds the first non-zero value in a
vertical list of numbers. The zero values will always precede a number
greater than zero.

January 0 0
February 0 0
March 0 0
April 580 0
May 1000 1050
June 235 850
July 1032 1000
August 654 2635
September 6854 2654
October 987 9852
November 3214 1568
December 605 6387


the answer to above should be 580 for the first column and 1050 for the
second column.

THANK YOU FOR ANY HELP!

find first non-zero value in a vertical list of numbers
 

On 25 Nov 2006 17:21:15 -0800, "Dorff78" wrote:

Looking for a formaula that finds the first non-zero value in a
vertical list of numbers. The zero values will always precede a number
greater than zero.

January 0 0
February 0 0
March 0 0
April 580 0
May 1000 1050
June 235 850
July 1032 1000
August 654 2635
September 6854 2654
October 987 9852
November 3214 1568
December 605 6387


the answer to above should be 580 for the first column and 1050 for the
second column.

THANK YOU FOR ANY HELP!

=INDEX(rng,MATCH(TRUE,rng<0,0))

entered as an *array* formula (i.e. enter the formula by holding down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula).

rng is a vertical range that does not include the entire column.


--ron

find first non-zero value in a vertical list of numbers
 

AWESOME THANKS...

Why the Array formula entry though, what does this do to the formula?
Never used it before.

find first non-zero value in a vertical list of numbers
 

On 25 Nov 2006 18:07:06 -0800, "Dorff78" wrote:

AWESOME THANKS...

Why the Array formula entry though, what does this do to the formula?
Never used it before.

If you look in HELP "about array formulas and constants" it gives a pretty
good explanation.

Glad to help.


--ron