I'm attempting to indicate certain months that fall between the end of one period and the beginning of another. I have the months of the year in column C
: values 1
through 12
. It's a running snapshot of the last 12 months, so when NOW()=February 2014, the months will show data (in other columns) from February 2013 – January 2014. When we roll into March 2014 (and therefore have a complete data set for February 2014), new data is imported and the same 1-12 now shows a snapshot of March 2013 – February 2014.
In Column B
, I indicate the two periods mentioned above with $
and >
. Either period may be multiple months; they may have a gap between them or they may run back to back; >
periods will always precede $
periods, so the third snapshot is a period of December 2013 for >
and January 2014 for $
.
What I want is for the period of time between the end of >
and the beginning of $
to show (empty string) in Column
A
and all other months to show .
in Column A
. If >
is adjacent to $
, the first $
month should be (empty string) and all following months should be
.
I'm looking for the formula or script for column A
to return the values .
or (empty string).
Below is the formula in cell A2 that was used to fill down to the cell A13.
=IF(OR(AND(MAX(FILTER(D:D,B:B=">"))+1=MIN(FILTER(D:D,B:B="$")),B2="$",D2=MIN(FILTER(D:D,B:B="$"))),AND(MAX(FILTER(D:D,B:B=">"))>MIN(FILTER(D:D,B:B="$")),OR(D2>MAX(FILTER(D:D,B:B=">")),D2<MIN(FILTER(D:D,B:B="$")))),AND(MAX(FILTER(D:D,B:B=">"))=12,MIN(FILTER(D:D,B:B="$"))=1,B2="$"),AND(D2>MAX(FILTER(D:D,B:B=">")),D2<MIN(FILTER(D:D,B:B="$")))),"",".")
I've attached a few shots of what I'm describing. You can see and comment on the sheet here.
///
///