Function to return the latest non-zero value in a range of cells .

G

Guest

I have a list of dates witten sequentially in a row on the sheet. Under some
of these I have some values. I need to find the non-blank value associated
with the latest date. Is there an easy way of doing this?
 
J

Jason Morin

Try:

=INDEX(B1:B21,MAX(IF(ISNUMBER(B1:B21)*(B1:B21<>0),ROW
(B1:B21))))

Array-entered (meaning press ctrl/shift/enter). Change
the ranges to suit.

This formula will pull the last value, skipping over
blanks, text values, and zeroes.

HTH
Jason
Atlanta, GA
 
J

JulieD

Hi

to find the last non-blank value
=INDEX(A2:G2,MATCH(9.99999999999999E+307,A2:G2))

to find the last non-zero value

=LOOKUP(2,1/(-A2:G2<>0),A2:G2)

Hope this helps

Cheers

JulieD
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NumberFormat for small non-zero numbers? 2
NON SEQUENTIAL DATE RANGES 1
Divide by the minimum non-zero number in a range of cells 21
Counting Zeros in a row until encounter first non-zero value 9
row number of last non-blank cell 14
find last populated cell in range of cells 6
Finding NEXT to last, non zero numeric value in a row 3
return first value greater than zero 3

Top