• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset, Product & Year

tanvirabid2

New Member
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
 

Attachments

  • Offset product chandoo.xlsx
    10.4 KB · Views: 6
Maybe

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

Attachments

  • Offset product chandoo.xlsx
    10.6 KB · Views: 2
tanvirabid2
Forum Rules are for Your too
  • 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
Forum Rules are for Your too
  • 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
 
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
 
@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.
Code:
= 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.
 
Back
Top