CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Seamus O'Hara
Seamus O'Hara

Reputation: 513

Else statement inside Google Sheets IFS statement

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

Upvotes: 1

Views: 1906

Answers (3)

gjlmotea
gjlmotea

Reputation: 91

Yes, we can put TRUE statement at the end like:

=IFS(condition1, value1, [condition2, value2...], TRUE, 0)

The last condition always be true, so the else statement (or default value) is 0.

Upvotes: 0

Rodrigo Nóbrega
Rodrigo Nóbrega

Reputation: 86

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
  )
)

Upvotes: 4

player0
player0

Reputation: 1

use in row 1:

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

Upvotes: 1

Related Questions