MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course
MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course
8h 22m
144 students
4.5 (75)

Do you want to get efficient and dynamic preparation for your Microsoft exam, don't you? MO-201: Microsoft Excel Expert (Excel and Excel 2019) certification video training course is a superb tool in your preparation. The Microsoft Excel Expert MO-201 certification video training course is a complete batch of instructor led self paced training which can study guide. Build your career and learn with Microsoft MO-201: Microsoft Excel Expert (Excel and Excel 2019) certification video training course from Exam-Labs!

\$27.49
\$24.99

Student Feedback

4.5
Good
52%
48%
0%
0%
0%

MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course Outline

About the MO-201 Exam

5:00
12:00
6:00
6:00
8:00
4:00
5:00

MO-201: Microsoft Excel Expert (Excel and Excel 2019) Certification Video Training Course Info

Gain in-depth knowledge for passing your exam with Exam-Labs MO-201: Microsoft Excel Expert (Excel and Excel 2019) certification video training course. The most trusted and reliable name for studying and passing with VCE files which include Microsoft Excel Expert MO-201 practice test questions and answers, study guide and exam practice test questions. Unlike any other MO-201: Microsoft Excel Expert (Excel and Excel 2019) video training course for your certification exam.

### Advanced Formulas & Macros

##### 4. Logical Operations

moving on to logical operations. Logical operations are made up of logical functions, which are Excel decisionmaking tools. And logical functions are based on the if statement. So if the logical expression is true, then do this; otherwise, do that. And as a quick example, let's say if you're hungry, then order a pizza; otherwise, well, don't order a pizza. So a logical expression compares numbers, text, strings, cells, or functions using operators. And here are the most common operators used in logical expressions. We have equal to, greater than, less than, greater than or equal to, less than or equal to, and not equal to. And here to the right, we can see examples of logical expressions using these operators and their results. So it's ten equal to seven? No. So the result is false. Is ten greater than seven? Well, yes, so the result is true. And you can even compare letters based on their order in the alphabet. Is a less than or greater than r, or does it come before r? Yes, so the result is true. Is it greater than or equal to r? No, so false. And you can also do this with dates using their underlying date value, which will be covered in the date and time functions portion of this objective domain. So is May 2012 less than or equal to May 2008? No, it's not. So the result is false. Is it not equal to May 2008? Of course, this is true. So, as you can see, it's pretty simple, intuitive logic. Now an important note when using these in formulas: Excel will interpret a logical value of true as a one and the value of false as a zero. So just keep that in the back of your mind. Now these are the logical functions we'll review in the following lectures. We have the essential if function, and we have the if function, which performs more than one logical test. We also have the not and or functions, which often work as operators inside of an if function. And finally the switch function, which is similar to an if statement but compares exact matches against the result of a single expression. We'll also review conditional functions, which perform basic arithmetic functions like count, sum, average, and maximum, but require a given criteria or set of criteria. Now there's no need to get hooked or hung up on these yet, as we're going to review them all in separate lectures. But for now, let's roll up our sleeves and get ready to start using logical operations in the course project.

##### 5. The IF Function

We got our bases covered with reference types and went over what logical operations are. Now it's time to get real with the if function. The if function checks whether a condition is met and returns one value if true and another value if false. Now, syntax-wise, the if function has three arguments separated by a comma, the logical test, which, as its name states, is a logical test or expression that can return true or false. So, for example, a one equals pizza, or b two is greater than ten. Now, the value of true is where you tell Excel what to return if the logical test is true. So this can be a value, a reference, or even another formula. And finally, we have the value of false. And this is where you tell Excel what to return if the logical test is false. Now, let's look at an example. Let's consider this table with movies, their genres, and their rating according to Rotten Tomatoes. For those of you not familiar with Rotten Tomatoes, it is sort of an online movie critic. And they evaluate movies as either fresh if they're good or rotten if they're not so good. So these are actually some of my favourite movies, and I'm interested in seeing which are fresh and which are rotten. Well, we know that movies with an AA rating of 60 or higher are fresh. So we can use an if function here in column D to determine where they land in the tomato meter. So our formula would look like this: If C2 is greater than or equal to 60 and this is my logical test, then my value of true will be fresh. And notice that fresh is wrapped in quotation marks, as this is required for all text drinks in a formula, then comma over to my value of false. So if the rating is not greater than or equal to 60, we'll then return it "rotten." Now, if we pressed enter and copied this down, we'd get this. So, as you can see, all movies with a rating of 60 or higher scored fresh on the tomato meter, while the rest were deemed rotten. And that's all there is to it. Now let's jump over to Excel and apply this to our Airbnb data here in Excel. We'll finally be working in the Excel Expert Course project workbook. So if you're following along, make sure you have this open to the Places worksheet. And remember that you can download this file from the Download Course Resources lecture in section one of the course. Now, if you recall, the Places tab contains information about all the listings for Airbnb in New York City, which we're going to be using to analyse the marketplace. Now, the first thing I'm interested in is looking at the Accommodate field, which states the number of guests allowed to stay in each place. And if we look at the current values, we can see that there are places that only accommodate one guest, all the way up to places that can accommodate up to 22 guests. And while this is interesting in itself, the range of numbers is too broad for me to be able to analyse properly. So what I want to do is divide these into size categories to make them easier to digest. And this is where the If function comes in. So first, let's add a new column here between the accommodations and the price columns. So I can right-click here on my price column and select Insert. Let's name this size. Okay, so how do we want to group these? If we think about it, the smallest, most basic size of a hotel room or an AirBnB room accommodates up to two people. So we can use that as our first category. Now let's start writing our IF function. So it is equal if we'll open parentheses and we'll select ourselves as two, which are the number of guests that this place accommodates, and if that is less than or equal to two. So, if this location can only accommodate two or fewer guests, then comma, then the size of this location will be small, which remember, we have to write between quotation marks because this is a text string, then comma over to the value of false. So if this is not true, and the place accommodates more than two guests, then let's say the size is simply large. Again, if they function with the parentheses, the space between quotation marks can be closed. Press enter, and we get our first small assault of small.And this is perfect because this place only accommodates two guests. Now let's apply this to the rest of our places, and we can do that by double clicking this green square at the bottom right corner of the cell. and let's see. Did this work so far? It looks like it did. We have small for one person, small for two, alarge place since it accommodates five, we have another largeone here for four, et cetera, et cetera, et cetera. So to actually check on this more thoroughly, we can filter our whole table by size. So let's say we are only going to select our small places. And if we open up our "accommodates" field, we'll see that we're only looking at places that can accommodate one or two guests, which is exactly what we want. And if we filter these by the large places, then we'llsee that we have the places that accommodate three or morepeople, which is exactly what we wanted as well. So there it is. a quick example of how to use the if function and how it's a great tool for creating categories using our data. So I'm just going to clear the filter size, and we're good to go.