Make dynamic array blank based on adjacent values in Google Spreadsheets

Question

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.

/// ///

Answer 1

Direct answer

The following formula return the expected results for the three cases exposed in the question. It should be added to cell A2 and cells in A3:A13 should be empty.

=ArrayFormula(
  IF(
    NOT(
      (ROW(B2:B13)-1=1)
      *
      (B2:B13="$")
    )
    *
    (
      (B2:B13=">")
      +
      (B2:B13="$")
      +
      (ROW(B2:B13)-1>=Vlookup("$",{B2:B12,ROW(B2:B12)},2,0))
    )
    *
    NOT((B1:B12=">")
    *(B2:B13="$")),
    ".",
    " "
  )
)

Explanation

In this case the use of ArrayFormula function together with references to B2:B13 will return an array of 12 rows and 1 columns with the expected values.

The comparison of a range or array with a scalar value returns an array of the same size of the range or array. All the tests should return an array of the same size.

AND and OR functions could not work with arrays, but Google Sheets interpret TRUE as 1 and FALSE as 0, so, instead of using AND function use * and instead of OR use +.

As + and * take precedence over comparison operators each test should be enclosed in parentheses.

Answer 2

The formula for your Column A is:

=IF(B2=">",".",IF(B2="","",IF((And(B2="$", B1=">")),"",".")))

this is a copy from line 2, but then you can copy it to whole column and it will calculate correctly.

---

Ok, considering the clarification, I just took a brute force approach. Of course there might be a more elegant solution to this, but I thought why not explicitly considering that special case:

=IF(B3=">",".",IF(B3="","",IF((And(B3="$", B2=">")),"",IF(AND(C3=1,B3="$",B14=">"),"","."))))