IF(AND) Function  INCORRECT ARGUMENT
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

Hi @Lynn Meadow
My apologies! I think I know why that's happening  when we added in the closed parentheses for the AND statement, I didn't take it off the very end of the formula. This means you likely have 1 extra closing one at the very end.
You only have 5 IF statements, so you should only have 5 closing parentheses:
=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", " ")))))
Try this? If this doesn't work, it would be helpful to see a screen capture of your sheet and the formula open (so I can make sure it's highlighting all the right colours etc), but please block out any sensitive data.
Cheers,
Genevieve
Answers

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

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

Hi @Lynn Meadow
My apologies! I think I know why that's happening  when we added in the closed parentheses for the AND statement, I didn't take it off the very end of the formula. This means you likely have 1 extra closing one at the very end.
You only have 5 IF statements, so you should only have 5 closing parentheses:
=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", " ")))))
Try this? If this doesn't work, it would be helpful to see a screen capture of your sheet and the formula open (so I can make sure it's highlighting all the right colours etc), but please block out any sensitive data.
Cheers,
Genevieve

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!

Wonderful, I'm glad we could get it working! 😊
Help Article Resources
Categories
Check out the Formula Handbook template!