Calculate annual returns for changing data

**imasood** · 04-01-2013, 07:50 AM

How do I have formulas for yearly returns in a table that automatically pick only the underlying daily/weekly returns (in a given range) without having to do it manually? it ideally would be a single formula from cell b2 to b9 giving the annual return for each corresponding year. thx

**Richard Buttrey** · 04-01-2013, 10:02 AM

Hi,

One way as an array formula in B2 copied across and down

Formula:

Please Login or Register  to view this content.

**imasood** · 04-01-2013, 10:23 AM

spot on-thanks much

**sans** · 04-01-2013, 03:57 PM

Sorry, wrong info

**imasood** · 04-02-2013, 02:27 AM

Richard could you do me another big favor annd briefly walk me through how the formula works? thx

**Richard Buttrey** · 04-02-2013, 05:05 AM

Hi,

The two MATCH() function elements in the overall formulae identify the row number of the first and last row for the year in question. Note that one has the FALSE element and the other doesn't. This means that the one without the FALSE will find the last row for the year's data and the other will find the first.

The OFFSET() function defines a range of cells. The two MATCH functions identify the range top and bottom. The rest of the formula is your original PRODUCT() formula.