Offset, Product & Year

[tanvirabid2]

Hi people,
I was hoping to get help on Offset, Product and Year combined. I have monthly returns. To annualize for 12 months, I can use =((PRODUCT(1+B4:B15)-1)) to get the result. I want the result to be based on the Years i.e. 2020, 2019, 2018. Since I have many years of monthly data and a new monthly row is added every month. So I wanted to build a dynamic formula using Offset and Year to get the Product result. The purpose of using offset is that new months will be added below row 3.
I am using this formula:
=PRODUCT(IF(YEAR(OFFSET($A$3,1,0,20,1))=$D5,(1+OFFSET(B$3,1,0,20,1))-1))
The idea is that Product result should be based on months that belong to the Year 2020, 2019 and so on. However, the formula is not working even with array. Attached is the sample file. Would be grateful if someone can assist.
Thanks

 

[Fluff13]

X posted https://www.mrexcel.com/board/threads/offset-product-year.1168608/
and marked as solved

 

[Excel Wizard]

Maybe

=PRODUCT(IF(YEAR($A$4:$A$22)=D5,1+$B$4:$B$22))
confirm with Ctrl+Shift+Enter

 

[vletm]

tanvirabid2
Forum Rules are for Your too

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

tanvirabid2
Seems that You don't care ...
You missed even the third step:
have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[tanvirabid2]

tanvirabid2
Forum Rules are for Your too

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

My apologies, thank you

 

[tanvirabid2]

I received this solution from a user in Mr. Excel. This works:
=PRODUCT(IF(YEAR(OFFSET($A$3,1,0,20,1))=$D5,(1+OFFSET(B$3,1,0,20,1))))-1

Offset, Product & Year

Hi people, I was hoping to get help on Offset, Product and Year combined. I have monthly returns. To annualize for 12 months, I can use =((PRODUCT(1+B4:B15)-1)) to get the result. Column B has the results. I want the result to be based on the Years i.e. 2020, 2019, 2018. Since I have many years...

www.mrexcel.com

 

[Peter Bartholomew]

@tanvirabid2
You asked for INDIRECT and that is what you have. The function used to be a key element of creating dynamic ranges pre-Office 2007 when Excel Tables appeared. The formula as it stands isn't even dynamic because the 20 rows is hard-wired into the formula.

If you convert the table with 'months' and 'returns' to a Table by selecting a cell and typing Ctrl/T, you would be able to use Structured References rather than the INDIRECT function.

= PRODUCT(IF(YEAR(Table1[Month])=@Year, (1+Table1[Return]))) - 1

Office 365 could make the whole process fully dynamic, with even the list of years growing dynamically as you change the data table. That is a far less trivial change, however, and requires helper ranges and LN function to make SUMIFS work.

 

[bernardand]

My apologies, thank you

 

[Peter Bartholomew]

My apologies, thank you

Have you done something you feel you need to apologise for or is it something you are about to do?