Pass Your Microsoft Excel Expert MO-201 Exam Easy!

Microsoft MO-201 Microsoft Excel Expert (Excel and Excel 2019) exam dumps vce, practice test questions, study guide & video training course to study and pass quickly and easily. Microsoft MO-201 Microsoft Excel Expert (Excel and Excel 2019) exam dumps & practice test questions and answers. You need avanset vce exam simulator in order to study the Microsoft Excel Expert MO-201 certification exam dumps & Microsoft Excel Expert MO-201 practice test questions in vce format.

Advanced Formulas & Macros

8. The AND/OR Operators

up the and operator or the and function. The and function performs one or more logical tests and returns a value of true. The and function, like the not function, is commonly used within an if function XY's if all arguments are true. The and function is made up of at least one logical test, each of which can return true or false. If all the logical tests listed return true, then the and function will return true. But if even just one of them returns false, then the and function will return false. Let's look at an example. Consider this table with wine tasting scores: So we have the name of the wine, its variety, the points it received from experts, and its price. Now, I don't really know much about wines, so I want to use this data to determine whether each of these is worth buying or not. As long as it scores over 85 points and costs less than $15, then I'll buy it. And because both of these must be true, it's an ideal situation to use the and function. Okay, so let's look at our formula. Start with an if, and then in our logical test, we enter our and function. And the logical test within that is c, which is two greater than 85. So the points have to be more than 85, and D is two less than 15. So the price needs to be under $15. And if both of those conditions are met, then I'll buy it. So? Yes. Otherwise, I'll pass. So no. Now apply that formula and see which ones are right for me. Well, it looks like I'm going with the Cabernet. 87 points for $10 seems like a good deal to me. A lot of wines, as you can tell here,are actually scored over 85 points, but they weretoo expensive, which is why the formula returns no. And the Michelle Lynch Merlot is only $9, but it's scored too low, so no for that as well. Okay, so moving on to the or operator or the or function The or function performs one or more logical tests and returns a value of true if any argument is true. So where the and function needs all the arguments to be true, the or function just needs one. Now, the syntax is identical; at least one logical test can return true or false. But again, just one of these needs to be true. for the or function to return true. And looking back at a wine as an example, I'm going to have to be honest and say that I'm not really that strict with my wines. As long as it's under $15, then I'll buy it regardless of the score. Also, if wine is over 90 points, I think it's worth buying regardless of the price. So let's see what our formula looks like. If or and, the logical tests are now c two greater than 90, for a total of more than 90 points, and D two less than 15. under $15. And as long as either of these conditions is met, then the value of true is yes, otherwise the value of false is no. And let's look at the results. Well, we have a lot of options. The only exception is the Jaffline Pinot Noir, which did not meet the point or price requirements, but the rest are all going on my shelf. All right, so now let's move this over to our Airbnb and B data. Okay, here in Excel, we're still in the Places tab. And for those who are familiar with Airbnb, you'll know that some places are marked as a rare find. And these are the places that have a rating of four, eight, or higher and more than 100 reviews. I'd like to identify the places that are rare finds in New York City. So let's add a column after the number of reviews, and we'll call it "Rare Find." Now, since there are two conditions that both need to be met, we'll need to use an and function. So we'll start with an if function, open that, and then add an and function in the logical test. So we'll open that, and now what we need to think is, "Okay, so what are the conditions that need to be met?" Well, the rating needs to be greater than or equal to 4.8, and the number of reviews needs to be greater than 100. So we can close that and come back to the value of true. And that is what we need to return if both of those tests return true. So, yes, it is a rare find. And for the value of false, what would just return no. So let's close this. Let's apply that downward and look at our results. So these first two places are not rare finds. And even if they both have 473 and 123 reviews, respectively, the rating doesn't go up to 4.8. So that's why they are not. Our third one is a rare find. It has a very high rating of 4.99 and 233 reviews. And let's see if we can find one with a rating over 4.9. Here we go. and less than 100 reviews. So beautiful are the attic, bedroom, and Chelsea. Again, a very high rating, 4.99. But since it hasn't gotten up to 100 reviews yet, then it's still not a rare find. In fact, if we filter Rare Find by Yes, you'll see that all the ratings will be four, eight, or higher, and they all have more than 100 reviews. So let's clear that filter. And I also want to focus on the room type. Now, as it stands, there are three types of rooms: an entire place, a private room, and a shared room. And while this is already a category, we can take it one level higher. I'd like to know if each place has any shared spaces or not. So, as you know, or even if you don't have a private room, you have your own room, but you do share some of the other spaces in the place with other people. And in a shared room, all the spaces in the place can be shared with other guests or the hosts. So in summary, private rooms and shared rooms both have shared spaces. So we can use that and add a new column called Shared Spaces. We'll begin with the anif function. Again, open that. And in the logical test, what do we need? Well, we know that if the room type is a private room or shared room, then it will have shared spaces. So that means we can use an or function. Open that. So or D two is equal to private room. Now here, since we're working with text strings, you need to make sure you're writing this exactly as we'll find it here. So private with capital P, room with a lowercaser or D two is equal to shared room. Again, first letter capital and shared but not in room. We'll close the door. So if it's either a private room or a shared room, then our value is true. Yes, it will have a shared space. And if it's neither of those, then no, it won't have a shared space. So close, press Enter, apply it down, and everything is fine. And again, a quick filter to check So yes, we're getting private roomand shared room, no shared spaces. Then we just have the entire place. So let's clear our filter. And there you have it. We've now used the and and or functions to identify the places that are rare finds and those that have shared spaces. Also, as a quick disclaimer, the logic we used for the rare find isn't actually how Airbnb determines this, but it was just used for the purpose of this lecture and this course.

9. The SWITCH Function

In this lecture, we will go over the switch function. And this is an interesting one in Excel because even though it's not used very frequently, there are certain specific scenarios in which it's the best solution available, and we'll go over those in a bit. But to start, the switch function evaluates an expression against a list of values and returns the result for the first matching value. So syntax-wise, it has three required arguments and a few optional arguments. And these are actually pretty self-explanatory, even if they still don't make the function very understandable at first. So the first argument expression is simply an expression or function to be evaluated for the value; you need to enter a value to compare to the expression result. And for the result, you tell Excel what to return in case the value listed matches the expression result. Now, after the first required value and result pair, you can either enter a default result if no value matches. So think of this as a value if it is false, or you can add additional value and result pairs. Now, I know the wording is a little tricky, so it's hard to get a full grasp of how this works from the syntax, but I think looking at an example will help solidify the concept. So consider this table of flights. We have the date of the flight, the airline, the flight number, and the origin and destination airports. And we want to know the weekdays on which each flight is available. Well, this is a great use case for the switch function. So if we were to write a formula in Column E, it would look like this switch. And then the expression we'll use is the "weekday" function, which is representing the date column, or "sell a two." And don't worry about the weekday function right now. We're going to cover this individually later in the course. But all you need to know for now is that it returns the number representing the day of the week for a given date. So from one being Sunday to seven being Saturday, But in this case, we don't just want to return the number; we want to return the abbreviated name of the weekday. And this is where the switch function kicks in. So we have the expression, and now we just need to tell Excel what to return for each of the values that the expression can take. So if the weekday function returns one, well, we want the result to be "sun" for Sunday. If it returns two, it will be Monday, three, Tuesday, four, Wednesday, and so on every two weeks until we reach seven on Saturday. In this case, we don't need to enter a default value to return here, as the weekday function will only return numbers from one to seven. And we've got them all covered. Now let's see what happens if we apply this formula. So, January 1, 2015 was a Thursday, and since the dates are all consecutive, it makes sense that the rest are Friday, Saturday, and Sunday. So that's all set. Now, a quick pro tip: switch instead of ifs when your comparisons only require exact matches. And this is related to the comment I made in the beginning of the lecture, where I mentioned that there are scenarios where switching simply works best. The most common of these is the weekday example that we just went over, using the same logic for the names of the month with the month function. So to highlight the protip, we could use an Ifs function to do what we just did with the weekdays and simply say Ifs. Open parentheses. Weekday a two equals one, then return Sunday. Weekday a two equals two, then return Monday. Weekday a two equals three, then return Tuesday. But you can already see how it starts to getlong and repetitive, where switch keeps it short and sweet. So with that in mind, let's jump over to Excel and use the switch function in our course project so we have a better idea of how it works. Okay, here in Excel, we're back in the Places tab for our course project workbook, and we'll be focusing on the rating field. Ratings on AirBnB are now assigned on a scale of zero to five, with five being the highest. And if we look at our data for New York City, we'll see that it ranges from 2.5 to five. And what we'll do for this exercise is add a Rating Class column that ranks each place by rating it as either excellent, good, okay, or bad. So think of the rating classes as having stars. Any rating that rounds to five stars is going to be excellent. If it rounds to four, it's good. If it rounds to three, it's okay. and anything beneath that is bad. So let's add our column, call it Rating Class, and just expand the width by double clicking here. Now, the first step we need to take is to figure out how to round these ratings since right now they have two decimal places. And luckily, Excel has a round function just for this. So start by writing that, opening it up, and you'll see that it has two arguments: the number that we want to round, which is the rating, and the number of digits to which we want to round it, which in this case is zero, since we need whole numbers. So we can close that out, and we'll see what results we're getting. Now, Excel kept the formatting of the two decimal places, but as you can see, we're just getting whole numbers of three, four, and five, which is exactly what we wanted. But we can't end here because we don't want numbers. We want excellent instead of five, good instead of four, OK instead of three, and bad for anything else. So if we were to ignore what we just learned about the switch function, we actually still have enough knowledge with what we've learned so far to turn these numbers into the rating classes that we want. So let's try using an Ifs function. So we'll delete what we have now and replace it with Ifs. And our first logical test is basically going to be "if." The rounded version of this rating is equal to five, right? So we'll write the round function again. So we want to round the rating down to zero decimal points. And if that is equal to five, then our value of true will be equal to excellent. And our second logical test is really going to be the same thing. But we want to see if the rounded version of this rating is equal to four. So we can actually just copy all this and paste it here at the end. So if this is equal to four, then we want it to be good. If this is equal to three, then we want it to be okay. And finally, if this is less than three, we want it to be bad. Close that out, press Enter, and we get good since 4.38 rounds down to four, which is good. And then we get excellent for the ones that rounded up to five and good again for the ones that rounded down to four. So far, so perfect. and that does get the job done. But this isn't really a very elegant formula. So let's try again using the ever-confusing switch function. So again, we're going to delete this out.I'm going to start writing our switch function. Open that up. And first is the expression that we're going to evaluate, which by now we should know is the round function I 20.Close that out. So we have our expression. Now come the value-and-result pairs. So if the result of the round function is five, then we want Excel to return excellent. If the answer is 4, we want to return the goods. If the answer is three, we want to say that everything is fine. And for anything else, we can actually use that sort of value. If there is an error, we can simply return bad. Close that out and press enter. And before we apply this, let's actually take a look at the difference between the two formulas we wrote. So this is the switch andthis is using the Ifs approach. Now, I don't know about you, but I'm definitely feeling better with the application of the switch function here. And let's apply this down there and get the same exact results. And that's it. Again, this is definitely a function that we need to reserve for very particular cases, but it's still important to know how it works and how to use it.

10. The COUNTIF/SUMIF/AVERAGEIF Functions

Moving on to conditional functions, we'll be talking about count if, sum if, and average if. The count if, sum if, and average if functions calculate a count, sum, or average based on specific criteria. Syntax-wise, they're very similar. Count if it has two arguments, range and criteria, and sum if it does, and average if it has range and criteria plus the sum or average range. Now, for the range argument, you need to specify what cells need to match the criteria. And for the criteria, you need to specify what condition those cells need to meet. And this is all the counter function needs, as it will simply count the cells in the range that match the criteria. Finally, the sum, range, and average range are where you tell Excel the range of cells you want to add to or get the average from. And if left blank, Excel will just consider the cells and the range argument as the sum and average range. But as always, the best way to fully understand this is through an example. So, going back to our wine tasting data, let's consider this list of wines, with the wine name, variety, and price. And in this scenario, we're the owners of a wine store and want to figure out by wine variety how many wines we have, their accumulated prices, and their average price. So if we were to do this for the tempanya variety, we could use the account if, sumif, and average if functions to do this. To calculate the number of Tempano wines, we can use COUNTIF, and our function will look like this. So count if our range is B 2 to B 14, which is our variety column, and the criteria is Tempanyo, since we want to count the wines of the Tempanyo variety. So if we look at our data, you'll see that we have one, two, or three tempanillo wines. And so the result is, in fact, three. To calculate the total price of the temporary lines we have, we can use SUMIF, and our function would look like this. So, SUMIF, our range remains B2 to B14 because we require variety to match the criteria, and the criteria remain Temperino, but the sum range is C2 to C14 because this is where my prices reside. And if we look back at our data, we can see that the prices for our three Tempanya wines are $27, $26 and $19, which added together give us $72. Finally, to calculate the average price of the seasonal wines we have, we can use average if our function looks like this. So average if has the same range criteria and average range as our SUMIF function. But now, instead of adding 27, 26, and 19, it will calculate its average, which in this case is $24. Now, pro tip when using these functions: if you use greater than or less than in the criteria argument, you need to add quotation marks around the entire thing. For example, greater than 100 would have to look like this, and it's just one of those quirks that Excel has, but it is important to know. Now let's head over to Excel and start using these functions here in Excel. We're still using the Excel Expert Course project file, but now we'll be moving on to the Host tab. And as you recall, this tab contains more information about the Airbnb hosts of places listed in New York City, like the date they joined, their response time, or whether they're a super host or not. And here to the right, we have a template for the Ahoist dashboard that will actually start filling out right now. So let's enter a host ID here. We can start with one, and what we want to do is calculate the number of places they have listed in New York City, their total reviews, and their average rating. As you may have guessed, we'll be using the accountif, sum if, and average if functions to do this. So let's start with the places. And if we jump back to the Places tab for a second, we can see that each row represents one place and that we have the Host ID for each place. Therefore, we can just count the number of times that the host ID of one is in this table, and it will represent the number of places that host one has. So let's go back to our host tab and write our formula. So count on it. Our range is the number of cells that need to match the criteria. So, in the Places tab, we can select the Host ID column entirely by clicking on it, and then comment over to our criteria, which is going to be the Host ID field in our Host dashboard. So cell L-2 can close that out, press Enter, and it looks like Host One has one place listed in New York City. Let's now get the number of reviews for that place. And since we want the total number of reviews for any number of places that a host may have, we need to use some here. So some of our ranges and criteria will be the same. So we have the Host ID column in the Places tab and the Host field in our dashboard, and our sum range is the Number of reviews column in our Places tab, because we want to sum the number of views for the places where the Host ID matches the host ID in our dashboard, so we can close that, press Enter, and we get 32 reviews. And finally, let's get the average rating. As you might expect, we must use average if here. So, same range, same criteria, and the average range here will be the rating column in our Places tab, correct? So we select that, close the formula, and press Enter, and we get an average rating of 491. So awesome. Now let's see what happens when we change the host ID so we can create that too. And we'll see that the totals change and now represent the places, reviews, and ratings for this particular host. And we can make it into anything we want. We can make the three, four, or even fifteen. Okay, so here it looks like our host ID of 15, or our host 15, has three total places, 78 total reviews, and 451 as the average rating. So let's see if we can prove these numbers are correct. Let's go back to our Places tab and filter our table. So we're only looking at the places for host number 15, so select that and press OK. And as you can see, he does have three places. The total number of reviews is 78, and the average rating is 4.51. So perfect. So, as a pro tip, when taking the exam, using these types of filters is a great way to double-check that the formulas you're writing are returning the correct results so we can clear that filter. from now, go back to our ID. And there we go. an example of how and when to use count if, some if, and average if.

11. COUNTIFS/SUMIFS/AVERAGEIFS for Multiple Criteria

Next up are the COUNTIFS, SOMEIFS, and AVERAGE IF functions. And as you probably guessed, the count if, someif, and average if functions calculate the count, sum, or average based on multiple criteria, and their syntax is almost identical to their younger brothers. But the sum range and average range are moved to the beginning of the function, which I actually prefer. So again, the sum and average ranges contain the values that you want. The sum or average of the criteria range contains the cells that you want to match the criteria, and the criteria is the condition that the range needs to meet. And finally, you can add additional pairs of criteria, ranges, and criteria if you have more than one condition. Now, going back to our wine store example, we have the wine IDs, their varieties, their points, and their prices. And now we want to see how many tempanillo wines we have, but those also have at least 85 points. We must use count ifs because we have two conditions: the variety must be tempanyo and the points must be greater than or equal to 85. So our formula would look like this: COUNTIFS. Our first criteria range is from B 2 to B 14. As a result, both the variety and the criteria are tempeh. Our second criteria range is from C 2 to C 14. So the points and the criteria are greater than or equal to 85. And for our pro tip in the last lecture, notice that we wrapped this in quotation marks for it to work. So looking at our data, we still have three temporary and two wines, but only two of those have scores greater than or equal to 85. So our result is two. Now, for the total price of these, we need to use SUMIFS. So our formula would look like this: SUMIF Our sum range is D 2 to D 14, which is our price column. And then the criteria range and the criteria are the same as in the example above. So the ride needs to be temporary, and the points need to be greater than or equal to 85. And looking at our data again, we can see that the prices are 27 and 19, which add up to $46. And for the average price, we'll use averageifs as follows, which is using the same information as the SUMIF function but returning the average of 27 and 19, which is $23. Okay, now that we have the hang of these, let's jump to Excel and start practising here in Excel. We're back in the Host tab on our course project workbook, and we're going to continue to populate our host dashboard. And what we want to do now is obtain the reviews and ratings of the places, but broken down by room type. So what we're essentially doing is adding new criteria to our original formulas. So the host ID in the Places tab will still need to match the host ID selected here. But the room type in the Places tab will also need to match the room type in each of these columns. Therefore, we'll need to use COUNTIFS, SUMIFS, and average. If so, let's start with COUNTIFS. And our first criteria here is the host ID. So places, host ID, and our criteria are going to be the host ID in our dashboard, and we'll comma over to our second criteria, and that's going to be the room type column. So we have column D in our places, and the criteria there is going to be the room type in our dashboard. Now, it's being blocked, but we can just use an arrow up to select it, as you can sort of see by the outline here. So we can close that out, press Enter, and we'll see that host number 15, even though they have three total places, only one of those is an entire place. Let's drag this across to see what else is there. Something's wrong here. We know that hostid 15 has three total places, but we're only getting one here. So what happened? Well, let's look at our formulas. So starting with our original one, we can use F two, and it looks like everything's good. I mean, the host ID is selected here, the room type is correctly selected here, and our criteria ranges are Places, column B, and column D, which are the host ID and the room type. And if we go over to the next one now, we'll start to see what happened. So a quick reminder of the importance of reference types here As you can see, since we didn't fix this reference in cell L 2, as we dragged it to the right, it moved over to cell M 2. And the same thing happened with our criteria ranges. So instead of being column B, it moved over to column C, and instead of being column D, it moved over to column E in our Places. So let's press Escape to get out of that. And we're going to modify our original formula and fix what we need to fix. So the host IDs will always live in column B of our Places tab, so we can fix that with F four. And our host ID is always going to be in cell L2 in our dashboard, so we can fix that as well. Now, our room type will always be in column D of our Places tab, so we can fix that, but the room type in our dashboard will need to be moved to the right because that's where the rest is, so we're fine with leaving that relative. Now press Enter again and let's try this out one more time. And there we go. So, one entire place, two private rooms, three total places. So perfect. Okay, now for some ifs, so, for the total reviews, our sum range is going to be the number of reviews column in our Places tab, and we're going to go ahead and use f4 to fix that to avoid our past mistakes. Our first criteria range is going to be our host ID. Fix that as well. And our criteria is the host ID on our dashboard, which again, we're going to use our four to fix. Now, for the second criteria range, room type, fix that to column D. And our second criterion will be the room type, which we'll leave relatively close, drag over, and perfect. And finally, for the average IFs, our average range is going to be our rating. So column I fix that, and then we're going to go through the same process. So host ID "hostid" here, and notice I'm fixing all these references. And then the room type and theroom type here, leaving relative close that. So take the 424 average for our entire facility, apply that, and it appears that we're getting a div zero error here for the shared room. And let's think about that for a second. So what we're doing here for the average is summing the ratings for each room type and then dividing by the number of places for that room type. But since the number of shared rooms for host15 is zero, well, then we're dividing by zero, which is why we get this error. So to fix this, what we can do is wrap our entire formula in an if-error function. So let's go back to our original one, and I'm actually going to select here before the average. I'm going to write my if-error function. And this tells Excel what to return in the event of an error, which we specify in the second argument here, which we'll go over later. And in this case, I won't say return zero because saying an average rating of zero is incorrect; it's actually null. So what we can do is just return a dash, which will need to wrap in quotation marks since it's a text string, so we can close that. As you can see, we're getting the same result for these, but as we drag it across the final time, we'll see that instead of the error, we're getting that dash that we wanted. So we're just getting a cleaner dashboard. And as you can see, as we move our hostID around, well, our dashboard is going to update.

12. The MINIFS & MAXIFS Functions

Next up are the MAXIFS and MINIFS functions, which, by now, I'm sure you can guess what they do. But a quick note, however, these are onlyavailable on Excel 2019 or three six five. So those of you following along on earlier versions of Excel won't have access to them. Not to worry, though, since they work the exact same way as the sumif and averageif functions, as you'll see. So the MAXIFS and MinIFS functions return the maximum or minimum values based on multiple criteria. Now, their syntax will look very familiar. The Max and Min ranges are where we tell Excel what values we want. The maximum or minimum value of the criteria range represents the cells that need to match the criteria, and the criteria is the condition the cells need to meet. And we can also include multiple criteria and ranges. Now we'll be looking at the same wine store example. And now we want our highest-priced tempanawine that has at least 85 points. So our formula would look as follows MAXIFS, where the maximum range is D 2 to D 14, which is the price column since we want the highest price. And the first range of our criteria is B 2 to B 14. As a result, both the variety and the criteria are tempanyu. The second criteria range is from C 2 to C 14. So the points and the criteria are greater than or equal to 85, which again needs to be wrapped in quotation marks. So looking at the data, we have the three tempanillos, but only two of these have at least 85 points. And by looking at the prices, the highest is 27. So our formula returns $27. And if we wanted the lowest price company you'llwine with at least 85 points, well, we'd useMINIFS, and in this case, the formula is exactlythe same, but will return the minimum value inour price column, in this case, $19. So let's go to our course project in Excel and put these functions to good use. Here in Excel, we're still working in the hosttab of the course project workbook, and we're going to continue to round out our host dashboard with the first review and last review fields. So we have host 29 right now, and what we're going to do is actually jump to our Places tab and filter it out by the places for that host. Now, to filter, we can either select manually or we can use the search bar here, type 29, clear the results, and select 29. So here we have the three places for this host. And what we want for the first review field is to pull the earliest or the minimum date from our first review column, in this case, August 5, 2016. And for the last review, we want the latest date or the maximum date from our last review column. In this case, it looks like March 1, 2020. So with that, let's clear this and write our MAXIFS and MINIFS formulas. So for the first review, like we mentioned, we need the minimum, so MINIFS the Min range being the first review column, and our criteria range is the host ID, and we need that to match our criteria of the host I selected in the dashboard. Close that out on August 5, 2016. Perfect. Now, for the MAXIFS, our max range is going to be the last review column in our places. The criteria range again is going to be the host ID, and the criteria is the host ID for our dashboard. Close that out. March 1, 2020 Perfect. And again, as we continue to move the hostid around, you'll see that these will update, and that's it. After having worked with the COUNTIF some ifs and average ifs functions, we've completed the logical operations skill of this objective domain. And now let's move on to date and time functions. I hope you're ready.

Go to testing centre with ease on our mind when you use Microsoft Excel Expert MO-201 vce exam dumps, practice test questions and answers. Microsoft MO-201 Microsoft Excel Expert (Excel and Excel 2019) certification practice test questions and answers, study guide, exam dumps and video training course in vce format to help you study with ease. Prepare with confidence and study using Microsoft Excel Expert MO-201 exam dumps & practice test questions and answers vce from ExamCollection.