Chapters

“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.

Check out online IT courses here.

The best Information Technology tutors available
4.9 (14 reviews)
Pedram
\$30
/h
1st lesson is free!
5 (9 reviews)
Xavier
\$30
/h
1st lesson is free!
5 (8 reviews)
Varun reddy
\$35
/h
1st lesson is free!
5 (11 reviews)
Arpit
\$20
/h
1st lesson is free!
5 (5 reviews)
Lucky
\$25
/h
1st lesson is free!
Yanush
\$16
/h
1st lesson is free!
Daniel
\$50
/h
1st lesson is free!
5 (2 reviews)
Raghav
\$15
/h
1st lesson is free!
4.9 (14 reviews)
Pedram
\$30
/h
1st lesson is free!
5 (9 reviews)
Xavier
\$30
/h
1st lesson is free!
5 (8 reviews)
Varun reddy
\$35
/h
1st lesson is free!
5 (11 reviews)
Arpit
\$20
/h
1st lesson is free!
5 (5 reviews)
Lucky
\$25
/h
1st lesson is free!
Yanush
\$16
/h
1st lesson is free!
Daniel
\$50
/h
1st lesson is free!
5 (2 reviews)
Raghav
\$15
/h
1st lesson is free!

## 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.

Search for online IT courses here.

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.

## 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 best Information Technology tutors available
4.9 (14 reviews)
Pedram
\$30
/h
1st lesson is free!
5 (9 reviews)
Xavier
\$30
/h
1st lesson is free!
5 (8 reviews)
Varun reddy
\$35
/h
1st lesson is free!
5 (11 reviews)
Arpit
\$20
/h
1st lesson is free!
5 (5 reviews)
Lucky
\$25
/h
1st lesson is free!
Yanush
\$16
/h
1st lesson is free!
Daniel
\$50
/h
1st lesson is free!
5 (2 reviews)
Raghav
\$15
/h
1st lesson is free!
4.9 (14 reviews)
Pedram
\$30
/h
1st lesson is free!
5 (9 reviews)
Xavier
\$30
/h
1st lesson is free!
5 (8 reviews)
Varun reddy
\$35
/h
1st lesson is free!
5 (11 reviews)
Arpit
\$20
/h
1st lesson is free!
5 (5 reviews)
Lucky
\$25
/h
1st lesson is free!
Yanush
\$16
/h
1st lesson is free!
Daniel
\$50
/h
1st lesson is free!
5 (2 reviews)
Raghav
\$15
/h
1st lesson is free!

## 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.

Let's not forget also that the Microsoft software has its own built-in help pages so you may be able to find instructions for what you need to program to do by simply asking it.

If you still need help, consider checking Superprof for private tutors specialising in IT or office skills.

## Learning To Use Excel With A Superprof Tutor

Price: varies Superprof lets you search through a wide database of tutors. All you need to do is enter your postcode and the subject you’re looking for a tutor for, and you’ll be shown all the available tutors in your area, as well as those offering remote tuition. There are three types of tutorials available on the platform: face-to-face tutorials, online tutorials, and group tutorials and each type has its pros and cons. Face-to-face tutorials are usually the most expensive but they're also the most cost-effective since you'll be the only student in the class and the tutor can focus entirely on you. Online tutorials tend to cost less since the tutor doesn't have to worry about their travel costs to and from the student. While these aren't ideal for subjects that require a hands-on approach, they can be really effective for technical subjects like IT. Group tutorials are usually the cheapest per hour since the cost of the lesson is shared amongst all of the students in attendance. Of course, this does mean that you'll get less personal attention from your tutor. Don't forget that a lot of the tutors also offer the first hour of tutoring for free. You can use this time to see if you get along with them if their teaching approaches are right for you, and discuss what you want and expect from your private tutorials. Just search what you want to learn and where you live and you'll find plenty of tutors offering lessons. Alternatively, you can opt for classes at an institution near you.

## Become An Expert In Excel With Pitman Training

Pitman Training is a long-established training provider that caters for all of your office needs. Beginners can benefit from some introductory or intermediate Excel classes, however, those who use the Microsoft program regularly may prefer to challenge themselves with this advanced-level course for Excel users.

### Excel Expert

If you don't have a Pitman centre near you or you want to study from home, then consider an online course that can offer you a similar qualification.

## Brush Up On Your Excel Knowledge With The New Skills Academy

Course Details Course Code: UKFEC16MEA Location: Online Duration: 10 hours Cost: £299.00 Qualification: Includes the Following Courses Microsoft Excel Advanced Certificate Further Details Course Access: Lifetime Exams Included: Yes Compatibility: All major devices and browsers

"What you will learn Some of the concepts that you will learn in the MS Excel Advance Course include the following:

• VLOOKUP Advanced formula options and manipulations
• Other advanced functions including OR, AND, CHOOSE, INDIRECT, REPLACE, LEN, LEFT, FIND
• Functions including CEILING, CORREL, DATEDIF, DATEVALUE, DAVERAGE and EDATE
• Colouring a column and row with a formula
• Highlighting a cell with a formula
• Functions including ISODD, ISNUMBER, ISTEXT, ISLOGICAL, ISNONTEXT, ISERR and ISBLANK
• Functions including DGET, DMAX, DPRODUCT, DCOUNTA, DCOUNT and DSUM
• How to calculate depreciation in Excel, including SLN depreciation and SYD depreciation
• Calculating loan IPMT and EMI
• Functions including DATEDIF, DATEVALUE, EDATE, EOMONTH, MATCH and INDEX
• Full explanation of the INDEX and MATCH functions, covering several modules
• Looking up data
• Selecting only cells containing comments
• Hiding formulas
• Automatically inserting serial numbers"

"Learn Excel 2016 Essential Skills with The Smart Method: Courseware tutorial for self-instruction to beginner and intermediate level £14.99 Paperback Here are some of the reasons that you should choose this book to learn Excel 2016:

• Absolutely anybody can learn Excel using this book. You will repeatedly hear the same criticism of most Excel 2016 books: “you have to already know Excel to understand the book”. This book is different. If you have no previous exposure to Excel 2016, and your only computer skill is using a web browser, you’ll find it really easy to work through the lessons. Everything is concisely described in a way that absolutely any student, of any age or ability, can easily understand.
• It provides the fastest possible way to learn Excel. With single, self-contained lessons, this book caters for any self-learning or teaching period. Many learners have learned Excel by setting aside just a few minutes each day to complete a single lesson. Others have worked through the entire book in a single day.
• It focuses upon skills used in the real world of business. This book makes it easy to master Excel to a standard that will greatly impress most employers because it doesn’t confuse by including skills that are not commonly used by most office workers. Click Amazon's Look Inside feature to review the vast number of Excel skills you will master. By the end of the book you'll be able to very quickly create sophisticated worksheets like the one shown on the cover of the book.
• Smart Method® books are #1 best sellers. Every paper printed Smart Method Excel book (and there have been ten of them starting with Excel 2007) has been an Amazon #1 best seller in its category. This provides you with the confidence that you are using a best-of-breed resource to learn Excel.
• Learning success is guaranteed. For over fifteen years, Smart Method courses have been used by large corporations, government departments and the armed forces to train their employees. This book is ideal for teaching or self-learning because it has been constantly refined (during hundreds of classroom courses) by observing which skills students find difficult to understand and then developing simpler and better ways of explaining them.
• It is the book of choice for teachers. As well as catering for those wishing to learn Excel by self-study, Smart Method books have long been the preferred choice for Excel teachers as they are designed to teach Excel and not as reference books. All Smart Method books follow best-practice adult teaching methodology with clearly defined objectives for each learning session and an exercise to confirm skills transfer.
• It provides a route to become a true Excel guru. If you later decide that you'd like to become a true Excel guru we also have an Expert Skills book that will teach you very advanced features (such as OLAP multi-dimensional modeling) that very few real-world Excel users need in their everyday work."

Finally, although your effort is required to learn something new, your money isn’t necessarily. You don’t have to spend lots of money on books or courses to get ahead with your learning of a new skill, as a range of books can be borrowed from libraries in your nearby towns. Library card at the ready, and off you go!

The platform that connects tutors and students