IF Statement with nested AND, OR, and a Third Overriding Result

Hi Everyone,

Notes40 contains a manually input numeric value that I'm using in a calculation to populate Notes70. Notes69 is a parent of Notes70, with a manual input of either "NA", "N/A", or no input. The result in the Notes70 calculation should display only if Notes69 is NOT "NA" or "N/A". Otherwise, I want Notes70 to show the value of Notes69.

I have:

=IF(AND(Notes40 < 500, (OR(Notes69 <> "NA", Notes69 <> "N/A"))), "One Server", "Two Servers")

which works great, but it doesn't address the Notes69 = "NA" or "N/A" condition. (As a side note, the "NA" or "N/A" value is important because it triggers some checkbox and conditional formatting actions.)

I tried (among other things):

=IF((AND(Notes40 < 500, (OR(Notes69 <> "NA", Notes69 <> "N/A"))), "One Server", "Two Servers"),notes69)


=IF(Notes69 = "NA",Notes69 = "N/A"), Notes69, (IF(Notes40 < 500),"One Server","Two Servers")

but no dice. How can I override the result of the Notes70 calculation against Notes40 when Notes69 is NA or N/A?

Thanks for any input!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!