Home > Software design >  Flagging based specific ranges
Flagging based specific ranges

Time:09-29

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':

[Sample data

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.

example table

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)

Example

  • Related