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!

This gives you the row number for December

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

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)

"MTro" wrote in message
...
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!

suppose the follwoing entries are there in A1 to A20
11-Mar-05
08-Jun-05
21-Nov-05
20-Apr-05
25-Jun-05
13-Mar-05
12-Mar-05
05-Jun-05
12-Jun-05
02-Oct-05
29-Jul-05
17-Aug-05
16-Dec-05
25-May-05
27-Apr-05
30-Apr-05
21-May-05
16-Nov-05
31-Dec-05
13-Sep-05

B1 to B12 type 1 to 12
the formula in c1 waill be
=MAX(IF(MONTH($A$1:$A$20)=B1,($A$1:$A$20)))
this is array function so hit control+shift+enter
and copy down to C12.
in D1 type
=MATCH(C1,$A$1:$A$20,0)
copy down to d12
column c gives the latest date in that month (i.e. max date in that month)
column D will give row number of the max value in column A

now you willl get hang of it and you can modify.
if #N/A occurs in column D that means no dates in that month are available.


perhaps more elegant solutions are available.
"MTro" wrote in message
...
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!