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

[Dorff78]

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!

 

[Ron Rosenfeld]

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

 

[Dorff78]

AWESOME THANKS...

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

 

[Ron Rosenfeld]

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