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

Kevin Segrist

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)

and

=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!

Paul Newcome

Ah. Ok. Try something like this...

=IF(Notes69 = "", IF(Notes40 < 500, "One Server", "Two Servers"), Notes69)

Paul Newcome

Let me make sure I understand exactly what you are trying to accomplish...

If Notes69 does NOT equal "NA" or "N/A", then you want to display the value of Notes69? What if Notes69 does equal "NA" or "N/A"?

Kevin Segrist

Hi, thank you for responding.

Sorry if I wasn't clear, I'm looking for exactly the opposite. Notes69 will either be NA, N/A, or nothing. If Notes69 IS NA or N/A, then I also want Notes70 to be NA or N/A. If Notes69 has any other value, (which will usually be nothing), then I want Notes70 to evaluate the numeric value in Notes40 and show the result.

Paul Newcome

Ah. Ok. Try something like this...

=IF(Notes69 = "", IF(Notes40 < 500, "One Server", "Two Servers"), Notes69)

Kevin Segrist

That did it! Thank you Paul.

Paul Newcome

Happy to help! 👍️