How do I extract the month end value from a daily time series?

[MTro]

Hi. I have a financial time series (a two column array consisting of the
columns 'date' and 'price') containing weekday values of a stock price.
Weekends are missing, non trading weekdays are shown as n/a. I want to
extract the calendar month end value only. I don't want to take monthly
averages, or assume 30-day months the whole year round, or any other
shortcut. I just want the spreadsheet to recognise the row in the spreadsheet
with a price that is the last day of the month. For example, for this year,
I would want to extract 12 rows (from an array of about 260) with the last
two rows being extracted being Wednesday 30th November 2005 and Friday 30th
December 2005. Hope you can help, thanks very much!

 

[Bob Phillips]

This gives you the row number for December

=MAX(IF((MONTH(A1:A20)=12)*(B1:B20<>"na"),ROW(A1:A20)))

as an array formula, so commit with Ctrl-Shift-Enter

Just adjust the range to suit.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)