How to identify the lastest date of the filled cell?

[fitfat]

I have this series:

date cpi

Jan-09 102

Feb-09 90.5

Mar-09 92.4

Apr-09 95.5

May-09 96.8

Jun-09 97.8

Jul-09 95.2

Aug-09 95.1

Sep-09 94.8

Oct-09 93.2

Nov-09 93.8

Dec-09 #N/A

Jan-10 #N/A

Feb-10 #N/A

Mar-10 #N/A

Apr-10 #N/A

May-10 #N/A

Jun-10 #N/A


and this


date Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 Nov-09 Dec-09 Jan-10 Feb-10 Mar-10

cpi 97.8 95.2 95.1 94.8 93.2 93.8 #N/A #N/A #N/A #N/A


how to identify get last date of the filled cpi? (in this case is Nov 2009)

 

[Hui]

Fitfat

Assuming that the data including titles was in Column A1..B19

try the following


=+OFFSET(A1,COUNT(B2:B19),0)


Make sure the cell where you put this formula has a Date Format

 

[fitfat]

Hi Hui. Your formula works for my first series

but do you have any solution for the second type?


date Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 Nov-09 Dec-09 Jan-10 Feb-10 Mar-10

cpi 97.8 95.2 95.1 94.8 93.2 93.8 #N/A #N/A #N/A #N/A


thank you so much

 

[Hui]

Try

=+OFFSET(A1,0,COUNT(B2:S2))

 

[fitfat]

Thanks a lot!!!