Else statement inside Google Sheets IFS statement-CodePudding

I've got an IFS statement in a column header to calculate some numbers based on a Google Forms input. I would like to modify one of these column headers to include a check against a boolean and output the results based on this, I'm not sure how to include an ELSE statement inside the IFS statement.

Here is the current code:

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "NET",
    LEN(A:A)=0, IFERROR(1/0),
    LEN(A:A)>0, F:F-E:E
  )
)

I would like to modify this to include a boolean check before LEN(A:A)>0, F:F-E:E

so: (D:D)=TRUE, B:B ELSE LEN(A:A)>0, F:F-E:E

Hope someone can help! Thanks in advance

CodePudding user response：

In IFS, you can simulate an ELSE using the TRUE condition as the last condition, because the first TRUE result returns the given value - something like: IFS(<condition 1>, <answer 1>, <condition 2>, <answer 2>, TRUE, <default answer>) But to have an "else if", it's just one more condition of the IFS. I suppose you mean: IF ROW(A:A)=1 ... ELSE IF LEN(A:A)=0 ... ELSE IF (D:D)=TRUE ... ELSE IF LEN(A:A)>0 ... Hence, try this:

=ArrayFormula(
  IFS(
    ROW(A:A)=1, "NET",
    LEN(A:A)=0, IFERROR(1/0),
    (D:D)=TRUE, B:B,
    LEN(A:A)>0, F:F-E:E
  )
)

CodePudding user response：

use in row 1:

={"NET"; INDEX(IF(A2:A="",,IF(D2:D=TRUE, B2:B, F2:F-E2:E)))}