IFS, AND, OR nested expression help

I’m trying to create a nested IF/AND/OR expression. Here is my attempt which does not work and gives me an error of

could not be parsed due to exception: Number of opened and closed parentheses does not match.

IFS([Length], “6”, AND([Diameter], “10”, “13”, OR([Length], “6”, AND([Diameter], “18”, “73”, OR([Length], “6”, AND([Diameter], “22”, “121”, POWER(([Diameter] - 4), 2)*[Length]/16))))))))

I have tried several combinations of opened and closed parentheses, but none seem to work and I’m not sure if my syntax is even close since I’m pretty new to appsheet and expressions. If my expression is way off and makes no sense basically I am trying to achieve this:

If [Length] equals “6” and [Diameter] equals “10” then “13” is the answer. OR If [Length] equals “6” and [Diameter] column equals “18” then “73” is the answer. OR If [Length] equals “6” and [Diameter] equals “22” then “121” is the answer. If none of these things are true than use this formula “POWER(([Diameter] - 4), 2)*[Length]/16”

Hopefully, someone can help me with a proper syntax. Also, I have several “OR” conditions, I’ve only used a few here for an example, so if there is a more efficient way to make a list to achieve this please let me know.

Thanks.

At the very least, you’ve got two extra closing parentheses (scroll down in the box):

IFS(
  [Length],
  “6”,
  AND(
    [Diameter],
    “10”,
    “13”,
    OR(
      [Length],
      “6”,
      AND(
        [Diameter],
        “18”,
        “73”,
        OR(
          [Length],
          “6”,
          AND(
            [Diameter],
            “22”,
            “121”,
            POWER(([Diameter] - 4), 2)*[Length]/16
          )
        )
      )
    )
  )
)
)
)

Beyond that, the expression is nonsense. I encourage you to read the documentation on the functions you’ve used (see below).

[Length] equals “6”

([Length] = 6)

[Diameter] equals “10”

([Diameter] = 10)

[Length] equals “6” and [Diameter] equals “10”

AND(([Length] = 6), ([Diameter] = 10))

If [Length] equals “6” and [Diameter] equals “10” then “13” is the answer. …

IFS(AND(([Length] = 6), ([Diameter] = 10)), 13, ...)

… OR If [Length] equals “6” and [Diameter] column equals “18” then “73” is the answer. …

IFS(..., AND(([Length] = 6), ([Diameter] = 18)), 73, ...)

… OR If [Length] equals “6” and [Diameter] equals “22” then “121” is the answer. …

IFS(..., AND(([Length] = 6), ([Diameter] = 22)), 121, ...)

If none of these things are true than use this formula “POWER(([Diameter] - 4), 2)*[Length]/16”

IFS(..., TRUE, (POWER(([Diameter] - 4), 2) * [Length] / 16))

All together:

IFS(
  AND(
    ([Length] = 6),
    ([Diameter] = 10)
  ),
    13,
  AND(
    ([Length] = 6),
    ([Diameter] = 18)
  ),
    73,
  AND(
    ([Length] = 6),
    ([Diameter] = 22)
  ),
    121,
  TRUE,
    (POWER(([Diameter] - 4), 2) * [Length] / 16)
)

Note that the line brakes and indentation are just for aesthetics. You can format the expression however you like.

See also:

5 Likes

Wow, this is above and beyond. Thanks so much. This really helps me understand how to break down formulas and I’m sure I’ll be referring back to this post many times in the future as an example.

3 Likes