YoY function with automatic weekly comparison

Question

Hello,

I have a table with the following format. For each calendar week I have a column for this year (e.g. 2017) and last year (e.g. 2016) next to each other.

What I would like to do is calculate automatically the Y-1 (YoY) change based on the current week. I already set up the formula to define the current week (=WEEKNUM((Today());1)) but how can I define in the pink column that I would like to calculate the %-change for that week compared to the same week last year? Together with the Weeknum-Formula it should update the value each week automatically.

Thanks a lot for your help!

Janine

Answer 1

How can I set up a formula where it adjusts the referred column each week automatically?

Your question can be interpreted in different ways...

I would use OFFSET:

The formula for the validation check:

B7:  =B2/A2-1

Copy this formula into B8, D7:D8, F7:F8

The formula in F7 becomes

=F2/E2-1

So the question is how can we refer to F2 and E2 using OFFSET

G2:  =OFFSET(G2,0,$G$1*2+1)/OFFSET(G2,0,$G$1*2)-1

Where the value in G1 means "how many weeks back?"

Andreas.

Answer 2

Hey Andreas,

thanks for your help. 

However, the column where the change is calculated remains all the time the same but the referred columns (e.g. CW52) changes every week. How can I set up a formula where it adjusts the referred column each week automatically?

Best,

Janine

Answer 3

I have a column for this year (e.g. 2017) and last year (e.g. 2016) next to each other.

I would like to calculate the %-change for that week compared to the same week last year?

---

A simple example:

Let us assume we have 2 visitors in 2016 and 4 visitors in 2017, it is obvious that we have a plus of 100%.

And if the exchange the numbers, 4 visitors in 2016 and 2 visitors in 2017, we have a minus of 50%.

C1:  =B1/A1-1

and drag down.

Andreas.