• 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.

YoY calculations for 3 periods

M

Maverick

New Member
Hi All,

Happy New Year, hope its an EXCELent one for all!
Last year, this forum kindly helped with a formula but 12 months later, I need help in adapting the formulas.
Currently, the user can select two periods (2014 & 2013) from a slicer and a formula calculates the YoY %. However, If I add 2015 data to the mix, how can I adapt this for the user to select either 2014/2013, 2015/2014 or 2015/2013?
Please see attached a small version on the spreadsheet with current formulas.
As ever, many thanks in advance!
 

Attachments

Hi again,
The supplied formula worked very well but now looking to adapt it. If I make it dynamic and add months, the grand totals for the year(s) move and need some help in adapting the formula accordingly...
Thank you
 

Attachments

Hi:
The key here is to make the
=INDEX(INDEX($C$19:$U$896,0,MATCH($B$11,$C$18:$S$18,0)), MATCH(10^308,INDEX($C$19:$U$896,0,MATCH($B$11,$C$18:$M$18,0)),1),1)
the one colored dynamic, you can either do it by referencing it with the pivot table or by simply copying and pasting "2013 Total" in the cell B11 do the same for 2014 as well. I have made the necessary changes in the file attached. Please go through the same and let me know with questions if any...
 

Attachments

Hi Nebu,
Thanks for this and it makes sense. However, how would you adapt the formula if you wanted to do 2014/2013 or 2015/2014 or even 2014/2013 when the total for the year moves given the number of months selected. Please see 'dynamic total' tab (if you don't mind!)

Thanks
 
Hi:

Please use slicers to select the respective years or months , the YOY % is now dynamic to any combination of selection you make , Let me know with questions if nay.

Thanks
 

Attachments

Hi Nebu et Al,
The formula provided has been extremely helpful. Due to the success of it, I would like to add another dimension.
Currently I have YoY information based on totals but would like to have year on year by client based on months or year selected thereby a dynamic field.

I have this for one year (2014/2013) but with the option of 3 years, I cant work out a simple way of showing this detail.

This is what i currently have:

IFERROR(GETPIVOTDATA("Revenue ",$F$16,"Year","2014","advertiser",F20)/GETPIVOTDATA("Revenue ",$F$16,"Year","2013","advertiser",F20)-1,"")

Any help in expanding this to show any number of months or year selected to give the clients activity for that selected period would be very helpful indeed.

Thank you
 

Attachments

Hi:

Let us clarify somethings first , how many years or how many months you want to compare at one instance. I would assume it will be 2 years at a time or 2 months at a time, if you have something else in mind let me know a sample output would be great...
 
Hi,
I've attached a sample file of what currently happens. The user can select 2 years and as many months as required. eg 2014/2013 and as many clients as required for Jan - Apr will return an overall YOY figure and also a figure by client (column A).
2 years works fine as shown but I just cant add the third year dimension (2015) dimension. eg if user selects 2015/2013 for Q1..

Thanks Nebu...I have tried my level best before troubling you!
 

Attachments

As simple as that!? I was so engrossed in the formula that I didn't see it.
As ever, many thanks Nebu-you've saved me a lot of time!
 
You must log in or register to reply here.
Back
Top