IF(AND) Function - INCORRECT ARGUMENT

Options

I am trying to enter a value in a column based on the data in other columns. This first part of the formula works just fine. I am basically comparing the value in one field to a value in a helper cell.

THIS PART WORKS:

=IF(AND([Surg Cogn Comp (copy)]@row < [Helper Value]5, [Surg Perf Comp (copy)]@row < [Helper Value]6), "Bottom Left", IF(AND([Surg Cogn Comp (copy)]@row < [Helper Value]5, [Surg Perf Comp (copy)]@row > [Helper Value]6), "Bottom Right", IF(AND([Surg Cogn Comp (copy)]@row > [Helper Value]5, [Surg Perf Comp (copy)]@row > [Helper Value]6), "Top Right", IF(AND([Surg Cogn Comp (copy)]@row > [Helper Value]5, [Surg Perf Comp (copy)]@row < [Helper Value]6), "Top Left", " "))))

BUT, when i try to add in the part about if the values are 0 that i want to fill in NA, i get an INCORRECT ARGUMENT.

Here is the part i added:

=IF(AND([Surg Cogn Comp (copy)]@row = 0, [Surg Perf Comp (copy)]@row = 0, "NA",


THIS PART DOES NOT WORK WITH THE "NA" ADDED IN THE FIRST SECTION:

=IF(AND([Surg Cogn Comp (copy)]@row = 0, [Surg Perf Comp (copy)]@row = 0, "NA", IF(AND([Surg Cogn Comp (copy)]@row < [Helper Value]5, [Surg Perf Comp (copy)]@row < [Helper Value]6), "Bottom Left", IF(AND([Surg Cogn Comp (copy)]@row < [Helper Value]5, [Surg Perf Comp (copy)]@row > [Helper Value]6), "Bottom Right", IF(AND([Surg Cogn Comp (copy)]@row > [Helper Value]5, [Surg Perf Comp (copy)]@row > [Helper Value]6), "Top Right", IF(AND([Surg Cogn Comp (copy)]@row > [Helper Value]5, [Surg Perf Comp (copy)]@row < [Helper Value]6), "Top Left", " "))))))


Is anyone able to help me decipher this? Thanks in advance!

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Lynn Meadow

    It looks like you're just missing the closing parentheses for the AND statement, before the IF true Value of "NA"...

    =IF(AND([Surg Cogn Comp (copy)]@row = 0, [Surg Perf Comp (copy)]@row = 0), "NA",

    That way the AND knows where to stop looking for more criteria and your formula goes back to the IF statements!

    Try this:

    =IF(AND([Surg Cogn Comp (copy)]@row = 0, [Surg Perf Comp (copy)]@row = 0), "NA", IF(AND([Surg Cogn Comp (copy)]@row < [Helper Value]5, [Surg Perf Comp (copy)]@row < [Helper Value]6), "Bottom Left", IF(AND([Surg Cogn Comp (copy)]@row < [Helper Value]5, [Surg Perf Comp (copy)]@row > [Helper Value]6), "Bottom Right", IF(AND([Surg Cogn Comp (copy)]@row > [Helper Value]5, [Surg Perf Comp (copy)]@row > [Helper Value]6), "Top Right", IF(AND([Surg Cogn Comp (copy)]@row > [Helper Value]5, [Surg Perf Comp (copy)]@row < [Helper Value]6), "Top Left", " "))))))


    Cheers,

    Genevieve

  • Lynn Meadow
    Lynn Meadow ✭✭✭✭
    Options

    I scoured and soured to make sure i had all the commas, but missed that one!! Now when i plug it in, i get unparseable.


    Thanks for your help on this

  • Lynn Meadow
    Lynn Meadow ✭✭✭✭
    Options

    Thanks again for your help. I actually tried adding another parenthesis at the end, didn't think about it from the other perspective. I appreciate you also including the reason why you did this, it helps me learn!! Have a great day!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wonderful, I'm glad we could get it working! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!