How to use ArrayFormula in Google Sheets, with multiple IF conditions?

So I just found out about ArrayFormula and am trying to convert my spreadsheet to utilize it, to be more efficient. Its worked wonderfully on all my columns except one, which is giving me trouble.

Currently, I have this formula in it (and copied to each cell):

=IF(C2="Immediate",
  D2+1,
  IF(C2="3 Day",
    WORKDAY(D2,3,Holidays!$B$2:$B$11),
    IF(C2="5 Day",
      WORKDAY(D2,5,Holidays!$B$2:$B$11),
      IF(ISBLANK(C2),
        IFERROR(1/0)
      )
    )
  )
)

It works wonderfully to calculate a date, based on a dropdown menu I have in another column. I cant figure out how to convert this to utilize ArrayFormula, as I get an error as soon as I add a second IF statement.

I can get the first statement to work with this formula:

=ARRAYFORMULA(IF(C2:C="Immediate",D2:D+1,))

But as soon as I try to add a second IF statement(such as with the formula below) I get an error.

How do I do this? I figure its probably simple but I cant figure it out!

Im trying to use a simpler formula to figure out where I am going wrong, and same thing will happen with the following formula so Im clearly doing something wrong!

=ARRAYFORMULA(IF(A1:A=1,"Hello",),IF(A1:A=2,"Goodbye",))

With only the first IF statement, it works. As soon as I add the second, I get #N/A

Answers


Try this formula somewhere on row 1 of your sheet:

=ARRAYFORMULA(IF(A:A="",,IF(A:A=1,"Hello",IF(A:A=2,"Goodbye","Other Result"))))


I know this is old but maybe try this formula:

={"Header_Cell_Name_Here";
  ArrayFormula(
    IFS(
      $C$2:$C="","",
      $C$2:$C="Immediate", $D$2:$D + 1,
      $C$2:$C="3 Day", WORKDAY($D$2:$D,3,Holidays!$B$2:$B$11),
      $C$2:$C="5 Day", WORKDAY($D$2:$D,5,Holidays!$B$2:$B$11)
    )
  )
}

IFS is a great alternative to IF. Rather than taking three arguments like you do with an IF statement:

IF(logical_expression, value_if_true, value_if_false)

an IFS statement can handle any number of conditions:

IFS(condition1, value1, [condition2, ...], [value2, ...])

you can use that formula

=ArrayFormula(IF(C2:C="",IFERROR(1/0),IF(C2:C="Immediate",D2:D+1,IF(C2:C="3 Day",WORKDAY(D2:D,3,Holidays!$B$2:$B$11),IF(C2:C="5 Day",WORKDAY(D2:D,5,Holidays!$B$2:$B$11),IF(ISBLANK(C2:C),IFERROR(1/0)))))))

here is an example


Need Your Help

Query syntax in error for SQLite Database Query

android sqlite android-intent arraylist android-sqlite

I'm having trouble with the query syntax. I have a listview in the MainActivity and pass the position to a second activity to then run the query. I also have a data model class, Promise, and an a...