Hi All, I tried a nested IF AND & IF OR formula but it is showing as #UNPARSEABLE error. pls help.

Below is the formula I am using.

=VALUE(IFERROR (IF(AND(Level@row = 1, (IF (OR(column@row = "Test 1o1", column@row = "Test 2 of 1",column@row = "Test 3 of 1", "31"),(IF (OR(column@row = "Test 1o1", column@row = "Test 2 of 1",column@row = "Test 3 of 1", "41"),(IF (OR(column@row = "Test 1o1", column@row = "Test 2 of 1",column@row = "Test 3 of 1", 55"),))))))), IF(AND(Level@row = 1, column@row = "Test 2"), "17", IF(AND(Level@row = 2, column@row = "Test 2"), "22", IF(AND(Level@row = 3, column@row = "Test 2"), "30")))))), ""))


Thanks for the help!

Tags:

Answers

  • Ibrahim Khaleel
    Ibrahim Khaleel ✭✭✭✭✭

    Can you try this,

    VALUE(IFERROR (IF(AND(Level@row = 1, IF(OR(column@row = "Test 1o1", column@row = "Test 2 of 1",column@row = "Test 3 of 1", "31"),IF(OR(column@row = "Test 1o1", column@row = "Test 2 of 1",column@row = "Test 3 of 1", "41"),(IF (OR(column@row = "Test 1o1", column@row = "Test 2 of 1", column@row = "Test 3 of 1", "55"),))))))), IF(AND(Level@row = 1, column@row = "Test 2"), "17", IF(AND(Level@row = 2, column@row = "Test 2"), "22", IF(AND(Level@row = 3, column@row = "Test 2"), "30"))), "")

    "code is missing before 55"

  • Mindfull
    Mindfull ✭✭✭✭✭

    @Ibrahim Khaleel it is still giving same error.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are a number of issues with this formula. Are you able to describe exactly what you are wanting to accomplish with it?

  • Mindfull
    Mindfull ✭✭✭✭✭

    Hello Paul,

    In the Level column ther are 3 Levels Level 1,2&3. In the "column" column there are 3 options for all the 3 options (test 1 of 3..test 2 of 3 and test 3 of 3) the value returned should be the same with respect to the Level selection.

    For example if Level =1 and "column" has any of - test 1 of 3..test 2 of 3 or test 3 of 3, the value returned should be "31".

    In the second IF AND it should simply check the Level in level column and then if in "column" it is "Test" return the related value.

    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm still not sure I follow. Could you provide a list of all possibilities and their outcome such as below?


    If Level = 1 and column = "Test 1" then 31

    If Level = 1 and column = "Test 2" then 31

    If Level = 1 and column = "Test 3" then 31

    If Level = 2 and column = "Test 1" then 22

    so on and so forth...

  • Mindfull
    Mindfull ✭✭✭✭✭

    If Level = 1 and column = "Test 1of3" OR column = "Test 2of3" OR column = "Test 3of3" then "31"

    If Level = 2 and column = "Test 1of3" OR column = "Test 2of3" OR column = "Test 3of3" then "45"

    If Level = 3 and column = "Test 1of3" OR column = "Test 2of3" OR column = "Test 3of3" then "55"


    AND If Level = 1 and column = "Test 2" then "17"

    If Level = 2 and column = "Test 2" then "31"

    If Level = 3and column = "Test 2" then "41"


    Let me know if it is clear now.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:

    =IF(Column@row = "Test 2", IF(Level@row = 1, 17, IF(Level@row = 2, 31, IF(Level@row = 3, 41))), IF(OR(Column@row = "Test 1of3", Column@row = "Test 2of3", Column@row = "Test 3of3"), IF(Level@row = 1, 31, IF(Level@row = 2, 45, IF(Level@row = 3, 55)))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!