Return value from a range within a range.

[jconway]

I have end of month stock prices from 12/31/2009 through 12/31/2013 in the second column of a sheet and the corresponding date in the first column. In a separate sheet I have the years 2010, 2011, 2012, and 2013 in a column. I need to build a formula on the separate sheet that will calculate the yearly return for the corresponding year on that sepeate sheet. This has to be a single formula or else I would do a new formula for each year. I beleive I need something that will look at the year on the sheet where I am calculating the yearly return, look in the total range of price data, find the range of the data only for the correspinding year within the total data range, and then return the price on 12/31/XX by using a max function against the data and then divide that by the same thing looking for the max date (12/31) of the prior year. I don't know how to have excel look at the all the dates and then find the dates for the specific year and return the value I am looking for. Thank you.

It kind of looks like this; data:
12/31/2009 $XXX
1/31/2010 $XXX
2/28/2010 $XXX
.
.
.
.
.
12/31/2013 $XXX

And the table I am trying to insert this into is just:
Yearly Return
2010
2011
2012
2013

[canapone]

Hi,

a first example: data in sheet1, in sheet2 in A2 2010

=SUMIF(Sheet1!A:A,DATE(A2,12,31),Sheet1!B:B)/SUMIF(Sheet1!A:A,DATE(A2-1,12,31),Sheet1!B:B)


the segment


SUMIF(Sheet1!A:A,DATE(A2,12,31),Sheet1!B:B)

returns value related to Dec 31,2010

the segment

SUMIF(Sheet1!A:A,DATE(A2-1,12,31),Sheet1!B:B)

returns value related to Dec 31,2009


A sample file would be helpful

[jconway]

Hi,

a first example: data in sheet1, in sheet2 in A2 2010

=SUMIF(Sheet1!A:A,DATE(A2,12,31),Sheet1!B:B)/SUMIF(Sheet1!A:A,DATE(A2-1,12,31),Sheet1!B:B)


the segment


SUMIF(Sheet1!A:A,DATE(A2,12,31),Sheet1!B:B)

returns value related to Dec 31,2010

the segment

SUMIF(Sheet1!A:A,DATE(A2-1,12,31),Sheet1!B:B)

returns value related to Dec 31,2009


A sample file would be helpful

Canapone, thank you for your help! I spent some more on it and came up with this:

=((MAX((YEAR('Monthly Returns'!$A$4:$A$52)=Summary!$A9)*'Monthly Returns'!B$4:B$52))/(MAX((YEAR('Monthly Returns'!$A$4:$A$52)=(Summary!$A9-1))*'Monthly Returns'!B$4:B$52)))-1

and then entered it an array formula and it all seems to working out when I drag the formula down for the different years and double check it against just looking at the data and finding the matching numbers and do it out algebraically! Thank you again for the help.

[canapone]

Hi again

in attachment some other formulas, a small example.

AGGREGATE(14,...1) is available only from Excel 2010


Regards