Spreadsheet tip: “is not” or “is not equal to”

If you are looking to set up a condition in a spreadsheet function in a situation where you want to include everything but one thing, you need to use the following:

"<>EXCLUDED_TERM"

Turns out the “less-than/greater-than” combo sign means “not equal to”, and it applies to text strings in addition to number strings.

I wasn’t sure what terms to search for initially, and relevant results for “is not”, and “is not equal to” were not giving me the answer I needed. Including terms such as “spreadsheet”, “Apple Numbers”, “function” and “formula” didn’t help either.

I was initially looking to do this with a function; the NOT function seemed ideal, but confusingly didn’t work.

The less-than/greater-than combo is unintuitive for a text string. But understanding that the combo means “not equal to”, and the individual symbols are not relevant, it makes more sense.

This is named a “comparison operator”. The Apple Numbers help file page on Formulas and Functions will tell you to search for that term in the online help (why they do not link directly to it, I am not sure), but even when you do a search, nothing comes up (oh, that explains why it wasn’t linked to directly).

This solution works with Microsoft Excel as well. When I eventually found the solution, it was on an Excel blog.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s