4

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.

First example /// Second example /// Third example

  • example 3 looks weird after your statement " ">" periods will always precede "$" periods." Can you clarify? – StampedeXV Feb 27 '14 at 9:46
  • 1
    It would also help if you could share the actual spreadsheet with us. – Vidar S. Ramdal Feb 27 '14 at 9:48
  • here's the sheet. docs.google.com/spreadsheets/d/… Thanks for your help! – gingmac Feb 27 '14 at 20:32
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.

0

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=">"),"","."))))
  • Thanks for taking a stab at it, Stampede. The problem is that sneaky 12-1 corner. I edited the original post to provide more info on what I mean. – gingmac Feb 27 '14 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.