Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? . |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula is used for subtracting a range of different cells f. | Excel Worksheet Functions | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
index to a range of cells | Excel Worksheet Functions |