Search code examples
google-sheets-formulaarray-formulas

Arrayformula with IFS function didn't expand the output result in a column


I used this formula to assign a number of each week in a month using the function (arrayfomula). But the function didn't expand the result for the whole rows. Any suggestion

https://docs.google.com/spreadsheets/d/1JOg8IWgs07OihXpmQbVVUl37T7b6R6xreKTicWfFk6E/edit?usp=sharing

the formula is in E5

=   arrayformula(IFS(
  OR(
     (DAY(C5:C35) >= 1) * (DAY(C5:C35) <= 7),
     (DAY(C5:C35) >= 15) * (DAY(C5:C35) <= 21),
     (DAY(C5:C35) >= 29) * (DAY(C5:C35) <= 31)
  ), 1,
  OR(
     (DAY(C5:C35) >= 8) * (DAY(C5:C35) <= 14),
     (DAY(C5:C35) >= 22) * (DAY(C5:C35) <= 28)
  ), 2,
  TRUE, "" ))

Solution

  • You may try:

    =   arrayformula(IFS(
             (DAY(C5:C35) >= 1) * (DAY(C5:C35) <= 7)+
             (DAY(C5:C35) >= 15) * (DAY(C5:C35) <= 21)+
             (DAY(C5:C35) >= 29) * (DAY(C5:C35) <= 31)
          , 1,
             (DAY(C5:C35) >= 8) * (DAY(C5:C35) <= 14)+
             (DAY(C5:C35) >= 22) * (DAY(C5:C35) <= 28)
          , 2,
          TRUE, "" ))
    

    enter image description here