return the row that matches today's week number
Our take
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Average cells in a column and ignore those with 0 and #VALUE, but only if I different column contains a certain year.Back again because everyone was so helpful! I'm trying to Average cells in row AW, while ignoring Os and errors, while also only doing that row if column AL contains a certain year. I think that makes sense? I know it needs to be an AVERAGEIF formula, but when I do "*2026*" as the Criteria, before even attempting the >0, the formula breaks. submitted by /u/Wicked-Storm [link] [comments]
- Get rid of rounding errorHello, got a problem with a spreadsheet at work. For an calculation I need to calculate the average Full time equivalent of staff. Sheet is set up like this this: Column A: Person ID Column B: duration of period in days Column C: full-time equivalent Column D: weighted full-time equivalent for period Column E: average Full time equivalent over year Column A to C are preset base data. Formulas are in column D and E Calculation is basically done, broken down the formula is: In column D: =ROUND([duration days]/365×[Full time eqivalent for period];4) In column E it is: =SUMIF([Person ID]; [Person ID]; [Weighted for period]) But because if the rounding it doesn't add up to 1, it gies to 0,9999, even if the full year average is 1. Any ideas, how to fix that? (Other than IF(0,9999; 1; do the math) 😂) Was thinking about something like " if all Full time equivalent for period (column C) for one person ID (column A) are 1,0000, then make the average over year 1,0000, if not make the calculation based on my current formula for column E" but I can't get the operators right for that. Thanks in advance fir any help😂 Edit: moving rounding to column E instead of D did the trick. Sometimes its the obvious stuff xD Thanks to all submitted by /u/AttemptSlow612 [link] [comments]