1

I am trying dynamically to calculate Week over Week for a spreadsheet, as new data is added to the columns every week on the date in the header. My data looks like so:

Name   WoW   9/27/2018   10/4/2018   10/11/2018   10/18/2018   10/25/2018
A      ?     100         120              
B      ?     120         140

WoW for A should be 120/100-1 = 20%. WoW for B should be 140/120-1 = 16.67%.

I was trying to use INDIRECT() in cell B2 to calculate WoW like so:

=INDIRECT(IF(MATCH(TODAY(),C$1:G$1,0),TODAY(),""))/INDIRECT(IF(MATCH(TODAY()-7,C1:G1,0),TODAY()-7,""))-1

however it just returns #REF!.

Any idea what I am doing wrong?

P.S. On second thoughts, it looks like this will fail on today()+1 through today()+6 so if there is a way to perhaps update the match() that would be highly appreciated.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user8834780
  • 1,620
  • 3
  • 21
  • 48
  • 1
    Why not just have `= D2/C2-1` in cell `B2` and drag down? – ImaginaryHuman072889 Oct 04 '18 at 17:44
  • Because that is only true for this week. As mentioned, I want to set the formula so that it dynamically pulls the value for the most recent week with data, and calculate WoW based on the week before that. ie. Come October 11, formula should dynamically pull WoW for Oct 11 and Oct 4 – user8834780 Oct 04 '18 at 17:50

2 Answers2

2

I suspect you want something more like:

=INDEX(2:2,MATCH(TODAY()+WEEKDAY(TODAY())-5,$1:$1,0))/INDEX(2:2,MATCH(TODAY()+WEEKDAY(TODAY())-5,$1:$1,0)-1)-1

than just D2/C2-1. INDIRECT is volatile so best avoided (though so is TODAY).

pnuts
  • 58,317
  • 11
  • 87
  • 139
2

An alternative would be this:

=INDEX(2:2,MATCH(999999,2:2,1)) / INDEX(2:2,MATCH(999999,2:2,1)-1) -1

Put in cell B2 and copy down. It just uses the last two cells in each row that are not empty, to do the calculation.

enter image description here

Marco Vos
  • 2,888
  • 1
  • 9
  • 10