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