Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel TRANSPOSE Function
  • Lookup entire row in Excel
  • How to use Excel MATCH Function
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • Conditional Formatting Icon Sets Examples in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to create a Histogram in Excel
  • Conditional Formatting Color Scales Examples in Excel

Data Validation Examples

  • Excel Data validation number multiple 100
  • Excel Data validation unique values only
  • Excel Data validation must contain specific text
  • Excel Data validation exists in list
  • Excel Data validation must not contain

If NOT this or that in Excel

by

This tutorial shows how to calculate If NOT this or that in Excel using the example below;

Formula

=IF(NOT(OR(A1="red",A1="green")),"x","")

Explanation

If you want to do something specific when a cell is NOT this or that (i.e. is NOT equal to X or Y, etc.) you can use the IF function in combination with the NOT and OR functions to run a test, then take one action if the result is TRUE, and (optionally) do something else if the result of the test is FALSE.

If color is NOT red or green

In the example shown, we simply want to “mark” or “flag” records where the color is not either red OR green. In other words, we want to check cells in column B, and then take action if they contain a value other than t”red” or “green”.

Worked Example:   Basic outline numbering in Excel

In D6, the formula were using is this:

=IF(NOT(OR(B6="red",B6="green")),"x","")

In this formula, the logical test is this bit:

NOT(OR(B6="red",B6="green"))

This snippet will return TRUE if the value in B6 is not “red” OR not “green” and FALSE if not.

Since we want to flag items that pass our test, we need to take an action when the result of the test is TRUE. In this case, we do that by adding an “x” to column D. If the test is FALSE,  we simply add an empty string (“”). This causes an “x” to appear in column D when the value in column B is either “red” or “green” and nothing to appear if not.*

Worked Example:   Calculate conditional mode with criteria in Excel

You can extend the OR function to check additional conditions as needed.

*If we didn’t add the empty string when FALSE, the formula would actually display FALSE whenever the color is not red.

Increase price if color NOT red or green

If you need to do something more complex, just extend the formula.

Worked Example:   If else in Excel

For example, say you want to increase all colors except red and green by 15%. In that case, you could use this formula in column E to calculate a new price:

=IF(NOT(OR(B6="red",B6="green")),C6*1.15,C6)

The test is the same as before, the action to take if TRUE is new.

If the result is TRUE, we multiply the original price by 1.15 (to increase by 15%). If the result of the test is FALSE, we simply output the original price.

Post navigation

Previous Post:

If else in Excel

Next Post:

If this AND that in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract substring in Excel
  • LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation
  • How to Remove Leading Spaces in Excel
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • How to get number of days, weeks, months or years between two dates in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate next scheduled event in Excel
  • Basic timesheet formula with breaks in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate present value of annuity in excel
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles