IF(AND) Function - INCORRECT ARGUMENT

Lynn Meadow

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!

Genevieve P.

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

Genevieve P.

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

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

Genevieve P.

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

Lynn Meadow

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.

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