I'm searching for an Excel formula that can flag specif weekly ranges, I have the following sample data with the desired output on columns 'Flag 1' and 'Flag 2':
[
So for 'Flag 1' column, I need to put '1' after 8 weeks of every new month based on 'Week' or 'Month' columns. For 'Flag 2', I need to put '1' after every two weeks from flags with values '1' in 'Flag 1' column.
This is related to a much complex task, in case you wonder what is the purpose of this :)
CodePudding user response:
There might be a fancier way, but this should do the job, you just need to create a 'helper' table. I've only included the necessary columns.
The grey section is the helper table and could be on the same or a different sheet.
Column 'E' is just the 1st of each month. You can either just create a series or put 1st Jan in E2, then
=EOMONTH(E2,0) 1
in E3 and drag down.Column 'F' has the formula
=(E2 6-WEEKDAY(E2)) (7*7)
. It takes the value in E2, works out the 1st Friday, then adds 7 weeks (you said 8, but you're including the 1st week of the month).Column 'G' is just a dummy value for what comes next
In 'B2' put the formula
=IFERROR(VLOOKUP(A2,$F$2:$G$13,2,0),0)
. This is looking up the helper table to see if the week can be found. If it finds, it, it adds 1, otherwise (ie IFERROR true), it adds 0.'C2' just has
=IF(B1=1,1,0)
so is just adding a 1 if the row above is 1.
Additional question - Flag 2
To make Flag 2 = 1 5 weeks post Flag 1, use an extra helper column.
- Column 'G' formula is just
=F2 (7*5)
- Flag 2 formula is now
=IFERROR(VLOOKUP(A2,$G$2:$H$13,2,0),0)
- Flag 1 may also need updating (should do it automatically if you insert column 'G', but if not, it should now be
=IFERROR(VLOOKUP(A2,$F$2:$H$13,3,0),0)