Function to return the latest non-zero value in a range of cells .
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? |
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 -----Original Message----- 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? . |
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 "hungryman" wrote in message ... 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? |
All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com