Last Number of Array

  • Thread starter Thread starter GBL
  • Start date Start date
G

GBL

Hi:
I'm a novice at this using Excel 2000.
I have an array of values (numbers). They are scattered within in an
array of ascending years along the "y" axis and months along the "x" axis.
I need a method of picking the *latest* number out of an array (B32:M37)
that are greater than zero - proceeding from left to right and downward.
Trouble is, some cells are empty (zeroes). All cells are formatted as
numbers.
Hope I described the problem well enough.

Thanks in Advance!!
 
Hi
one way: use the following array formula (entered with
CTRL+SHIFT+ENTER):

=INDEX(B32:M37,MAX(IF(B32:M37>0,ROW(B32:M37)))-31,MAX(IF(OFFSET(B32:M37
,MAX(IF(B32:M37>0,ROW(B32:M37)))-32,0)>0,COLUMN(OFFSET(B32:M37,MAX(IF(B
32:M37>0,ROW(B32:M37)))-32,0)))))
 
Back
Top