Help: I want to identify the latest date of my daily series

[fitfat]

Dear all:


I want to identify the latest date of my daily attached series.


If I use this formula = OFFSET(A1, COUNT(A2:A65536), 0) or OFFSET(A1, COUNT(B2:A65536), 0), I do not get the answer that I want


So can any one help me with a formula that give me the 15-Jan-10 as the answer of my question?


Date Agricuture

28-Dec-09 1,751

29-Dec-09 1,758

30-Dec-09 1,753

31-Dec-09 #N/A

1-Jan-10 #N/A

2-Jan-10 #N/A

3-Jan-10 #N/A

4-Jan-10 1,805

5-Jan-10 1,835

6-Jan-10 1,849

7-Jan-10 1,924

8-Jan-10 1,952

9-Jan-10 #N/A

10-Jan-10 #N/A

11-Jan-10 1,960

12-Jan-10 1,966

13-Jan-10 1,932

14-Jan-10 1,946

15-Jan-10 1,936

16-Jan-10 #N/A

17-Jan-10 #N/A

18-Jan-10 #N/A

19-Jan-10 #N/A

20-Jan-10 #N/A

21-Jan-10 #N/A


I put the additional dates after 15-Jan-10 in purpose because I want to have my series automatically updated every time I open this file, which is actually linked to other file.


Many thanks in advance.

 

[Hui]

Fitfat


I hate doing this but it can be done using an Array Formula


=+MAX(IF(ISERROR(B2:B26),"",A2:A26))


and press Ctrl Shift Enter to enter the formula as an array formula

 

[fitfat]

Yes, this works!! Thanks a lot Hui