return the row that matches today's week number
This shouldn't be difficult for me, but I keep getting an error.
I have a list of dates in column A, each date corresponds to Friday - "Week ending", and a corresponding amount in column B for that week.
I want to average the four previous weeks, meaning this week and the preceding three weeks. So I naturally want to find the row in my list of dates that has the same week number as today, and average that row's B value with the previous three rows.
For some reason I'm having trouble finding the match for today's week number and keep getting #VALUE! back. I know its the INDEX causing the error, but not sure why.
I'm using Office 365, and my formula (currently) is :
=MATCH(TRUE,INDEX(WEEKNUM(A2:A13)=WEEKNUM(TODAY()),0),0)
I know I could add the helper column to pull the week number for the dates in A, but that feels like cheating when I know this should work. For some reason, I'm having trouble generating the array for the INDEX, or so it would appear, and I'd like to know why. This is a current version of Office 365 so I should not need to use Ctl+Sft+Ent, but it yields the same error regardless.
I'm sure I'm missing something simple, so feel free to point it out. TIA
[link] [comments]
Want to read more?
Check out the full article on the original site