“Those who can imagine anything, can create the impossible.” - Alan Turing
Microsoft Excel is one of the most popular programmes in the Microsoft Office suite. Whether you’re using pivot tables, graphs, or spreadsheets, Excel allows you to work more effectively with data. As well as addition, subtraction, multiplication, etc., you can also use a variety of different functions in Excel.
So how do you use some of these functions and logical operators in Excel?
In this article, we’ll show you how to use some of Excel’s most powerful functions. These functions can help you to use Excel more effectively, use conditional formatting, and get more out of an Excel spreadsheet, workbooks, worksheets, and pivot tables.
Logical Operators in Excel
The functions IF, NOT, AND, and OR aren’t often known by users of Excel even though they can be really useful when creating spreadsheets and tables. They allow you to apply conditions to certain sets of data in the rows and columns of your Excel data.
They can be included in a macro.
“A macro is an action or a set of actions that you can run as many times as you want.”
Put simply, macros are a shortcut that allows you to do several actions within Excel at the touch of a button.
The IF function is one of the main ones. According to Excel:
“The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.”
This function is generally used at the end of a row of data in a spreadsheet. You can find it in the toolbar. You can use it in a formula that can have two different results, A or B, for example. By adding the term “IF”, you imply that if the condition is met, the result will be A. Otherwise, the result will be B.
The IF function can be used with other functions (OR, AND, NOT). There are sometimes results that don’t correspond to either of your expected outcomes.
This is why you can also use AND, OR, and NOT alongside the IF function.
Once you’ve created your spreadsheet, you can organise it with logical operators. It might be a good idea to create an organisation chart to keep track of your conditions.
This will help you to create your formulae without forgetting brackets, semicolons, etc. The semicolon is essential for separating possible results. Make sure you don’t forget that your operation can be FALSE.
The IF Function in an Excel File
According to Microsoft:
“[A]n IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.”
An IF function compares a value to another value defined by the user. This gives you a new value that says whether or not the condition has been met.
An IF statement is as follows:
=IF(Condition; Value if true; Value if false)
You can also use logical operators in Excel:
- Equal to (=)
- Different from (<>)
- Greater than (>)
- Greater than or equal to (>=)
- Less than (<)
- Less than or equal to (<=)
The “Value if true” will be shown if the condition outlined in the IF statement is met. Otherwise, the “Value if false” will be displayed.
Unlike what you may think, the values don’t necessarily need to be numerical. If you put text in, the formula will display that text. You can put values or text in the cells:
=IF(A2>10, “For”, “Against”)
=IF(A2>10, A4, 0).
The logic can be used by adding a value like “A” in the first cell (A1) and a condition such as “B” in the second. For example:
=IF(A2>B, “For”, “Against”)
The result will be “For” or “Against” for each cell.
You can also nest IF functions in the event that several conditions must be met. You can nest IF statements within one another.
In this case, the conditions will be met in stages. A formula could look like this:
=IF(B3>90; “A”; IF(B3>80; “B”; IF(B3>70; “C”)))
Each statement is based on the output of the previous. There needs to be a logical hierarchy in place.
Find out more about common keyboard shortcuts for Excel.
IF Functions Used With AND
The AND function can be used in Excel and added to IF statements. Using AND reduces the length of formulae and makes it easier to read.
As we previously explained, if you have several conditions that need to be met, you can nest your statements to get the desired result. This means that one condition is checked before the second comes into play. This structure is endless and focuses on errors.
By using the function AND, you can simplify the nested functions. The function of AND is to ensure that if both conditions are met, the result of the IF statement will be TRUE. If one of the conditions is not met, the result will be FALSE.
Thus, you can create a formula like:
=IF(AND(A1=“TRUE”; A2=“TRUE”; A3=“TRUE”)=TRUE; “EVERYTHING IS TRUE”; “NOT EVERYTHING IS TRUE”)
The negative result indicates that not everything is true but it doesn’t mean that all results are false.
Look up for the best IT security courses.
IF Functions with OR
To create formulae in Excel, you can use logical operators such as IF, AND, and also OR. Like the AND function, OR is used with IF to simplify certain statements. It’s a good way to streamline your operations.
The problem with the AND function is that all conditions need to be met to create a positive result. Thanks to the OR function, you can get a positive result as long as one of the conditions is met.
This is the type of formula you could get:
=IF(OR(A1=“TRUE”; A2=“TRUE”; A3=“TRUE”)=TRUE; “AT LEAST ONE CONDITION IS TRUE”; “NO CONDITIONS ARE TRUE”)
With a single condition met, you’re given a positive result. In the case of negative results, it means none of the conditions has been met.
Just like with the AND function, using the OR function allows you to use just two functions rather than several.
AND and OR functions can be used together within an IF statement. This allows you to create all sorts of formulae. Thus you can combine AND (all conditions must be met) and OR (one condition must be met).
Using three functions at the same time allows you to work with three conditions at the same time. In the case that you want one condition to be met and at least one of the other two to be met, you can use an AND function and then an OR function while nesting them within an IF statement.
The NOT Function in Excel
The NOT function allows you to provide a positive result if a condition isn’t met. You’ll still use the IF function to introduce the condition but you’ll use to NOT function to invert the result. This function allows you to get a positive result from an unmet condition.
This will work similarly to the different to (<>) operator.
Thus: =IF(A1<> “British”; “Foreign”, “European”) will give the same results as =IF(NOT(A1= “British”; “Foreign”, “European”).
The NOT function can also be introduced within the IF statement alongside an AND or OR function. While the formulae will become more complicated, they allow you to do powerful logical operations automatically within Microsoft Excel. Open a spreadsheet and play around with this.
If you still need help, consider checking Superprof for private tutors specialising in IT or office skills. Just search what you want to learn and where you live and you'll find plenty of tutors offering lessons.
There are three main types of tutorial available on the platform: face-to-face tutorials, online tutorials, and group tutorials.
Face-to-face tutorials offer tailored lessons to students and are the most cost-effective of the options. However, they also tend to be the most costly per hour since you're paying for a bespoke service.
Online tutorials are taught over webcam using software such as Skype and tend to cost less since the tutor doesn't need to factor travel costs into their rates. Of course, this type of tutorials isn't great for hands-on subjects.
Group tutorials are cheaper per student but you'll not get the one-on-one time or tailored tuition you get with the other two options.
Don't forget that you can also get free tuition for the first hour from a lot of the tutors on the platform. This is a great way to work out which is the right tutor for you before you commit to learning from them in the long-term.