Class 9 - Objective Domain Section 4 - Perform Operations with Formulas and Functions | David Murphy | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Class 9 - Objective Domain Section 4 - Perform Operations with Formulas and Functions

teacher avatar David Murphy, University Lecturer

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

8 Lessons (50m)
    • 1. Class Introduction

      3:32
    • 2. 4.4.1 (Part 1) Insert References

      9:05
    • 3. 4.1.1 (Part II) Absolute References

      7:22
    • 4. 4.1.2 Perform Calculations by using the SUM Function

      5:00
    • 5. 4.1.3 to 4.1.5 Other Functions

      3:26
    • 6. 4.2.1 Perform Logical Operations by using the IF Function

      10:20
    • 7. 4.2.2 to 4.2.4 Using Conditional Functions

      3:37
    • 8. 4.3.1 to 4.3.3 Using Text Functions

      7:19
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

8

Students

--

Projects

About This Class

ad1f06e1

Class 9 - Objective Domain Section 4 - Perform Operations with Formulas and Functions

This is the ninth in a series of 10 classes designed to prepare you to take the 77-727 MOS (Microsoft Office Specialist) exam in Core Excel.

If you are a complete beginner then you should review all of the lectures. 

If you have some level of beginner or intermediate skills in Excel, then you might want to attempt the set project first and discover the gaps in your skills. These can then be addressed by reviewing the lectures.

So this series of classes is designed for everyone who wants to get independent certification of their Excel skills.

This class covers sections 4.1 to 4.3 of the Objective Domain for the 77-727 exam.

In addition, if you want to test yourself under exam conditions, then a further set of classes will be available (77-727 Practice Exams and Video Solutions) which provide you with 3 practice exams, comprising 105 project tasks, with which you can test yourself under exam conditions and time constraints. A full set of video solutions are available with these classes also.

When you complete this series of classes you will be fully prepared to obtain this valuable certification and obtain independent certification of your Core Excel skills from Microsoft, a qualification that is highly valued throughout the world.

Meet Your Teacher

Teacher Profile Image

David Murphy

University Lecturer

Teacher

With over twenty years experience as a systems analyst and project manager with significant experience in the design and implementation of major projects for some of the worlds largest companies, Dave Murphy changed career track in 2000 and has been a professional university educator for over eighteen years. His main interests are in Microsoft Certifications, Project Management and Business Process Analysis. Dave has a B.Sc (Hons) from Trinity College Dublin and an M.Sc Degree in Computing. In addition to obtaining PRINCE2 Practitioner status, Dave is also a Microsoft Certified Professional in Managing Projects with Microsoft Project, has multiple Microsoft Office Specialist certifications and is a MOS examiner.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Class Introduction: Hi, My name's Dave Murphy Anime University lecture myself certified professional on a mosque examiner. This is wool class in a series of classes designed to prepare you to take the Microsoft certification exam. Examine 77 77. In Core Excel, the class is arraigned of people who are relatively new to excel on, particularly at beginners. However, if you're already in Excel User and would like to get official recognition for your skills , then this is the right class for you. If you're starting to learn except from scratch, I've included lectures in class War with Siri's, which will introduce you to accept to the Excel User interface had to navigate within excel until information is entered. Worksheets. If you have some familiarity with software, then you can skip the introductory lectures and go directly to the lectures on the specific elements of the curriculum court. In each class, which Microsoft called the objective domain, I've structured each class of that. You can go directly to the project and test your skills. That way. You know what it is that you have to brush open in order to get a little certification. The most important piece of information that you need to review at this point is the objective domain for the exam. This is a corrected and provided by Microsoft, and you need to ensure that you're proficient and every element of that objective domain. If you are to succeed in the exam, I've included a pdf off the objective remain with the project for this class so printed down have available as recovery rejection off the objective remain completely exercise. You can take each one off as completed. Looking at the contents, you can see that the objective remain is divided into five main sections and that each section have forever silt sections 1.11 point 21.3 and so on. Each of the classes in this series covers one or more elements off the objective domain on the lectures air number to indicate which L'm is being covered. The class projects are specific to the skill within that section of the demand covered in class. The most exams, based on real practical experience with software in this case itself, has to be used during the test, their normal with choice type questions. It's real experience with the product that being examined So if you pass than people know that you're proficient in the level which is being tested, they can be sure of your skills. Some of you may believe that your skills in a particular aspect of the objective remains a strong enough to pass already on that. You don't need to call me the lecture material. If so, fine. Just go to the exercises, calling techniques with that part of the off the domains on test your skills. Gaining more certification is a real advantage on a great way to enhance your CV. It's the number 1 90 certification in the warmth, with over a 1,000,000 exams taken every year. These classes will ensure success and gain that qualification. So go ahead and give yourself on edge in the market place. Get certified. 2. 4.4.1 (Part 1) Insert References: this part of the objective demand is all the shooting form of, um, functions in the cell. The formula is simply a calculation. Voting any number and combination of values cell references on door, built in functions farms did not have to functions and can be very simple and is the only requirements. Were excited to know that what your entry of the former is that you start the data it wasn equals two. Son. You always perform the into the cells where you want the result to appear. Let's look at some simple forms to start with, so you have got to numbers in different cells now conformist, operating these numbers to produce new results. So if I go into a new cell and type equals two, except, you know I'm going to enter a formative, let's add the two numbers together. Now I could type in 6.5 pressure turn and get the answer. 11 Which is correct. However, if I change the data in either of these says, do you concede that has no effect from the answer? That's because I didn't reference the cells in the formula. This is a very important point if you're new to accept formulas. In the vast majority of cases, you want to include the cell references in your formers, writing and typing in numbers. I go back in here in the form there. Now, instead of typing the numbers, I clicked on itself, so we have D one and two. The plus sign on there. Click on E one on press return. Now the interesting 11. But the content of the cell with the form that very different. Because now I could change the content of either of the reference cells, and you can see that the answer is always correct. That's the correct way to shock formula, which are using data from other cells. Incidentally, you could type in the sale names E one and E one. You don't have to stick in them, but it's much better to click on the side because that way you avoid typing errors, especially when your farm is getting more complicated. So always use cell references and farmers, and always clicking them to select them. As you're entering the formula. Let's have a look at some more formula examples, so if in say they want to put the number 10 what we have here is called a constant value. Its new American, This case, every workbook with form it will usually have at least wound constant value. We're going to build a performance starting here now in the woman put equal to a one for this formula. References to current value in cell A one no changes are calculations have gone to the value and sell a one other than to copy it over. If the value and sell they want exchanged Selby, one will automatically displayed in their value. So if I change number and they want 25 you can see that immediately. Be one changes also because being one is using the cell reference to the content of a one and seeing when they put equal to a one month supply base. This references the value enabled and multiplies the pace and the one before equal to see one divided by five this reference value and see one on divided by five. Then just the right of the decimal point will also be displayed, if necessary. Nothing if the value and sell a wound. Changes in the value and sell see one change, which will cause the value this service change in a cascading effect. Announce Sally womb upon evicted some brackets, a one stem colon de would and close the backers. This reference is a built in function calls some which calculates the sum total of all says in the range from a 12 d one. Nothing if the Valium said a one changes, then the values and said being one C one on D one will also change at the same time, which caused the value in this cell changes well, if I change the value and said a one, you can see that all of the foreigners are automatically re calculators. This happens because all of the farmers are in some way related to the value and sell a one . Remember that you don't by default see the actual formed in the cellar to type it into your also. Obviously, that, however, in the formula bar, you can also copy forms for themselves except make a copy of the form that undersell formatting automatic gear just to sell references to take account off where you copy to four minutes to, so you can see that this new form has been adjusted to take account of the new position. I placed it in on the cell. References have been automatically justice. Of course, forms can become very complicated, but at this stage, what you need to understand. If this idea off cell references aunt, her form was used them that's looking. A few more examples illustrate the point. I found the students new to excess sometimes struggle with the concept of several references until this year. Number of examples. Once you get through it, though, you because it'll become second nature to you. It's also very good. Practice your brackets or parentheses to split your forms open of clear what the confirmations are that you're performing. For instance, that sport build up a simple self figures walk sheet illustrate. If I put in some head engine for the month of the air, we use also feel complete, serious No allowed, some say says Paris's names and call you a next week, an ad in the Money Day Bay than each month. That's former these numbers so that they're in your earth through selector says right click on Get the former Tsar's options. Now we can make them a currency on bills. Select yours Now we want the total in each month so allowed in a row. The bottom called Total says. Now any deformities total. All of this says in each month. So I go into the total save cell for January and type in my formula. So we have equals two. A self now knows it's a formula. Click on the first sales figure for the month and be to know entered the plus on. You can see to enter the plus on Taken D to impress. Enter. Do we have the correct answer? Well, if you do the mental arithmetic, you can see that the answer is correct. But the better way to constructive formula. And that's years a function on the cell range here, rather clicking on in each individual cell reference and using the plus sign. We can use the some function, which alleges to add the content of cells together. So we have the equal Cecillon because instead of formula, but no, we type in some which is recognized by except as a function for me is a function you have to put on opening brackets to enclose the addresses of the says that we're going to have the function operator. Now we could type in in several references, Reference admitted before. But there's a better way, and that is to select the group of cells. So you click on the fourth South. Be too. Hold down the mouse button on dry to where the last cell reference is on. Let go. You can see in the foaming about that. The range is indicated by the first cell, then by a colon, followed by the last cell. This is called a sale range and losing close and brackets, so I need to put the closing bracket in and then press enter. Now you can see that the answer is the same, but the form it is much better Imagine if you have 30 sales persons. It will be rather tedious to click on each one and time, followed by clicking on a plus sign. Several ranges and functions make life a lot easier. There were still need to have to talk before the other months. And do you have to type them all in? No. You can use all who filled, which is effectively the same as a copy on the justice saddle references appropriately so you can see that the formed in here has been adjusted to take account of the fact that the original farming has been moved to the right by one collier. So all of the sad references in the original form are changed to raise the column reference to state. See instead of B the actors. Structural reform is identical. As a general rule, you can only use cell ranges within functions. For example, if I try to use a cell range without a function, then accept doesn't know what I'm trying to do. I get an error the same reference we've looked at in this lecture called Relative Cell References. The reason for this is that when I years art a copy to copy the Formula eight Sal adjusted to several references relative to the original, this is just some terminology. What you need to be aware of that comes up in more advanced Excel skills. 3. 4.1.1 (Part II) Absolute References: we've seen that in most circumstances, formulas use relative cell referencing. If you copy formula that contains a relative cell address and paste it to another cell, Excel automatically juices for the new location. However, in some cases you may not want this automatic adjustment feature. This is where we can use what is termed absolute cell references. I found that this concept is one of the more difficult ones for new years of excel to grasp . It may take you a little while, but most students understand the concept. Using some worked examples. Let's consider sales company that have three, says people. They have the sales forecast figures for the first quarter of the year on they've been set up in the following work. She's so we have Richard, Dave and on, and they have the following says forecast figures for January, February and March, the first quarter of the air in this house, we can put in a some formula to calculate the total says for January. So we have equal to film is the formula brackets and then we select the tree cells which have the figures for January close. The breakers and press enter, and we get information of those three figures. Now we can use also feel to complete the same formula for February and March. We don't have type it in separately. So when we select the cell with the formula in it, we can go to the right hand corner off the cell, click and hold down the left mouse button on dry across auto Fill uses relative referencing to compute the totals for the other months. You can see that the original formula referred to Canyon be when we use all fulfill. The formulas were automatically adjusted to account for the fact that we're removing across columns in the worksheet. So here, instead of having a reference to call you B, we have a reference to call, you see. And here we have a reference to call. Um de this is the same if you put former to calculate the quarterly says for each says person. So, Richard says, can be computed by using a some formula like so Now when we use our fit to drag down the column like this, you can see that the column reference is identical in each of the formulas, but that the role reference have been changed relative to the original, so that's relative referencing. This is the default when you use auto fill. However, there will be occasions when you don't want relative referencing to be used, and the alternative is called absolute referencing. Let's say that we want to experiment, Ready said forecast figures to see what additional income we would get if he made an assumption about additional growth. So here I've entered a percentage 5%. I want to see how much additional income that rate of growth on the quarterly forecasts would give me so I can put a formal in here to multiply the total, Richard says. By the 5%. So I click on the equals to sign, and then I multiply Richard Self. So I click on this. So by the 5% so I put in the ass. Aside from multiplication, I click on the 5% on presenter so we can see that I get an additional $95 from Richard. If I were to apply an extra 5% growth, what happens when I use all the field to copy the formula for Dave and then well, as you can see, they get $0 growth. So what's happened? Well, because relative referencing is the default when you overfill on because I'm feeling down. The column. Alcalay Um, references will remain the same. However, as we've already seen. The role references would be automatically get justice. So in the original formula, we had Richard Quarterly Total in Southie three. We had the growth assumption percentage in Cell Hate three, as I used all to fill toe the reference to E three Change E four, which is correct because it's days says Quarterly total. But notice that the reference to sell Hey stry has also changed on went to hate four. Because I have moving down the roads to the three went with four. But what's in age for? Well, nothing. When you multiply, figure by nothing, you get nothing and we have no additional growth for Dave on the same is true for on now. How do I get over this issue? Well, while I need to do is to make the reference to the growth assumptions cell to remain the same as I use overfill, and I can achieve this by making the reference to hate three. An absolute reference to change a relative cell address to an absolute cell address in a formula or a function, Enter dollar sun before the row number on or the condom letter. This ensures that when you copy of formula or used auto fill, Excel will not just the absolute cell addresses for the new location. So here I can type in a dollar sign in front of the row on the column reference, and when they use all feel now to find my growth figure, you can see that I get correct figures. This is because, as you can see in both of these formulas, the reference to Hates Tree has remained the same. Now, you don't have to put the dollar sign in front of both the row on the Coney. Um, you can do it for either. So in this case, the reference to the column that hate is not going to changes in auto fill. Because I haven't moved across the columns, I only need to put the dollar in front of the row reference. Like so on. The formula was still work. Now you can type the dollar signs in, or another method of 10 the absolute signs and sell addresses is to press F four. Once you've typed to sell address, you can go back to the former's You've type previously. Adam Position the insertion point in cell dress you wish to make absolute on press F four. The number of times you press therefore determines which reference has become. Absolute press wants to make both the column and row reference absolute press twice to make only the role references. Absolute pressed three times to make only the Canyon reference absolute on fresh four times removed the absolute references on both the Collier on the row equally. Of course, you can type them in and have exactly the same effect. 4. 4.1.2 Perform Calculations by using the SUM Function: were introduced Exile Function to the Last Lecture Xer provide a large lybia of built in functions. To facilitate this on qualitative mathematical on data operations, These functions enabled calculation of long or complex formulas to be simplifies to be using a specialized function. There are a lot of functions in itself, but there are a few which are the most commonly used in this section of the objective. The main. We're going to look in each one in turn. I introduced to some function in the previous lecture, and it's a good example of a very commonly you function. In fact, it's so problem that you find in most workbooks, as its name implies, its purpose is to calculator some of our numeric values in the range of cells. A function is not the same as a formula, the form that always has unequal to sign of the very first character After the eagles to sign, you construct your farmers using as many values operated in functions as you want. For example, let's look at this for a 10.5 plus a sum of F two to F nine plus B two, divided by eight B three divided by a to put some of G two to G nine, multiplied by five. This is an example of a form that uses standard operators such as addition, subtraction, multiplication and division operators. They'll also used to some function, and its use of cell ranges on the mix of these can be used to build up a formula except for the terminology associated with the use of functions. Beginners sometimes find the terminology quite intimidating at first, but it's really not that difficult. Won't you accept that these are just terms which you get used to very quickly? The function uses the term arguments to describe the improvise it needs to perform its calculations. There's some general rules when the sound about arguments, arguments coming, values the American texture days, depending on the type of function or sell references. Each function has specifications for its arguments, but the most common type of Ireland is a cell range. We've already seen her to some function. Use a cell range is already many functions later, and a variable number of arguments, for example, we could have equal tive some breakers. See six to see eight in close, the bikers, this calculator, some of the numbers and also from C six to see 18. Or we could have equal to some brackets. See six to see 18 comma D 62 d eight in comma F six, F 18 and closed the Breakers. This counter laces some other numbers and also from C six to see 18 on from D 68 in on from F six f A N. In these cases, you separate to sell ranges with the Kama. Okay, have equal sue some brackets. See six to see a day in common, see 20. Close the brackets. This calculator some of the numbers and answer from C six to see 18 on then as C 20. You can intercept, arranged either by typing the cell references directly or by using the pointing method turns appointing mentored. Simply use the mirrors to click on the four cell on dry to the last cell to select the range. The latter has the event advantage of visually identifying the Sarah Range, which reduces the possibility of entry incorrect sale references. As I said before, it's better than typing, and our tears have to manually typing to some function into a cell issues the art of some button in the ribbon to some function is used a lot on Excel provides a number of places to find it on the formula. Stab in the Function Libra Group. Click on this icon, which is called the Autism. This can also be found in the home tab in the editing group Shake Autism, or you can click the Insert Function Command on the left of the formula Bar this allergic to search for a particular function so other type in the words, some into the search box. You can see it appearing here. Notice that there is a narrow next on. Assuming the ribbon when you click the arrow is will display other summary functions such can use in your formula. We're going to explore all of these functions in the next few lectures. Be careful when you're using autism exam. Make assumptions as to the range of serves you want to include in the formula if we get the right amounts occasions, but sometimes it doesn't so make sure to verify that you selected correct Cell Ridge and modified in the former bar. If you need to 5. 4.1.3 to 4.1.5 Other Functions: in the previous actually were introduced. The formulas and functions Army Cell had hears this song formation in particular the following addition. Functions are tested in the mosque or exact exam average. This calculates the arithmetic mean average of the numbers in the specified range men find on display for the lowest numeric value in the specified range. Max finds under space the largest numeric value in specified range on count counts, number says, and a specified range that contain the America or date values. He's operating same age, some function. So I just demonstrated in the contact context of our ongoing example, so never want see the average of the sales figures for January. We'll all I have to do is to enter the phone in this cell, which is where on the answer to appear. So we have our equals to sign. Then we can type in the function name average opening bracket announces itself, for which I want the average to be calculated, then close. The backers knows that I haven't into the tote in the average range selectors. This gives me the average save the three, says persons. I just need the formula and demonstrating all the way to do this, which is to click on the pole, their 1,000,000 beside the artists, um, icon who have the average former slept from I'm gonna do so you can see that Excel populist cell range of things they want to use, which in this case is correct. Remember that it may not always get the rights. Will be sure to check American use our field to get the average for the other months and excel automatically amends, etcetera, just relative to the original formula. The next function is the men function, which gets the minimum value in the range of self selected. Once again, we do this for the three, says persons on. Find out who has the least says. Of course you can see with your eyes what the answer is, but if you have 100 people working for you, then that would be a lot more difficult. So we have unequal suit, then men, then the opening backers. Next we specified, the Rangers says, in the form that on close the bracket, we can see that this to correct answer. Once again, we can art affair. The other months, notice that the men function is also available from the autism pulled on the max function is identical to the men but gives you the maximum value in the range of says selected. So I can edit this form. I'm in place the men with Max and there you can see that we get the maximum sale value on once again. I can also feel the formula. The final farming in this part of the demand is to count function, which simply cancer number of says in the specified range that contain numerical or date values. So if I use this function here and you can see that the answer to three this because Richard is text and isn't counters if they added a date in this road, the answer because four. So these are relatively straightforward functions which are used very commonly in the next section. We're going to consider functions which and I was to test of conditions using various types of if statements 6. 4.2.1 Perform Logical Operations by using the IF Function: on. If function is one, which allows you to test conditions on depending on whether the condition is true or false , you can get except before unspecified actions. So, for example, you could test the content of a sell by especially conditions, such as if the contents of the cell are greater than 10,000. That's an example of a condition. The ability to perform different calculations based and changing values is one of the most powerful new for feature of spreadsheets. The if function makes an evaluation or logical test on performed one of two different calculations based on the results the foreman of the IT function is of fellows equals stuff if opening the Bacchus logical test comma value. If it's true, comer value. If it's false and close the bikers, the logical test can be anything you want. You have to specify what it is that you want excel to evaluate. Such is in the example above. The next part of the former is a comma very important, followed by a statement of what you want excel to do. If the logical test is true. So if the content of said a greater than 10,000 that's the test. If that's should, and this is where you specify what it is that you want to happen, then you have another comma for by a statement which tells excel what to do if the logic condition it false, the logical test and that the function normally uses burning operators to construct the test. These are really not that complicated. That just sound complicated. Some of the comparison of praise you can use our equal to greater than less than greater than or equal to less. Enrico, too, are not equal to let's look at some examples illustrate Herrick News if functions in practice. So looking at our sales performance, walk, cheat, we've been told compared to Target, which is originally given to the sales persons to the actual says they made. If they sold in excess of the target, then we want to highlight these people for bones automatically. So I've extended this information, show the figures for each month on a yearly totally using, using a some function to calculator. I've also and didn't want the tired waas for the year for each shares person. Now I want to compare them. Of course you can see the situation quite clearly as there are only three people involved again. If you had love people working in the company, it will be a different situation. On would be better to automate the bonus process. In the first instance, I want to find out if the pair's is due to have a bonus, so I put in my equals to sign. Then I'm going to use the function, which is an if function now. I had the open breakers there. I put in a logical test notice as I'm typing that except promised me with the elements of this statement, which are required. But what's theological test in this case? It if the content of said end to are greater than the content of self 02 issue change achieved Self figure hired on the original target in this case, it isn't but nonetheless will continue to build a statement. Well, if this test is show, I want some text to appear on this way and tell me that that's the case because I want text to appear except requires that the text being closed in quotation marks. But before I could do this, I need a comma after the logical test The Commons are very important on the function won't work if you don't have them Now we can tell except what I want to happen. If the logical test is true so I type in quotation marks deserve the bones Close quotation marks. Next I need another comma identity. It's tell Excel what I want to happen if the logical test isn't true. So I tried quotation marks. No bonus. Sorry. Close quotation marks. And now we need to put in the closing bracket to match the wound I used at the start. You always have to much rockets and accept formulas. No depressed return que Well, poor Richard is not getting a bonus Now you can use the NFL to create the performance. I don't need to type them in again For the other says persons. We can say that they are geo bonus shuttle. Relatively simple use of their statements to test logic. Logical conditions on make the system do something dependent. I'm headed the condition is true or false. I typed the statement directly into the set but there is a little way to do it but going to the formal type and clicking on the Insert Function Command, which brings up this dialog box if you cells become familiar with this as it can help in situations where your are unsure of the structure of the statement you're trying to create because it gives clues as the water is required. So if I type in if into the search box on Press the Go button, the F seven in Force One found in the list double click at the Fortune Arguments Dialog box opens up once again the word I would use to describe the teams that are going to be put inside the bracket of the function. It doesn't mean that we're going to fall out so you can see that this dialogue breaks up the function into its component parts. So the first part is the logical tests. So I click on. Until then, type the greater than simple on den type 02 Next, I have to tell it what I want to do. If the logical test is true, then I have to tell what I want a happen if the test it false, you can see here that the dialogue is actually telling me what the result is in this case, I pressed the okay button on. My formula is crazy. You can see in the form about that. It's identical to the one I typed in area. The only difference is that I didn't have to type in the comments. XL does that automatically either way works. Although I made assistant to stay some text in the value for the truth and the affections of the statement. You can use these tomatoes system. They're pretty much anything you want. It doesn't have to be a for do display of text. You could use functions aromatic formers pretty well. Anything, for example, say wants to calculate the amount of the bones I was going to pay if the Paris exceed the target and say that the rule was that I paid a 5% of the overall target. If they exceeded their target, well, I could no change the formula. It follows. So instead of the text, I can put in a simple form here, which says that if the logical test is true, I'm going to give it the value of the target, which isn't celo too multiplied by 5% Excel recognized percent of shining calculations. I lot of fill the Formula One former to serve for Europe. If the logical test is not true, then unfortunately the sales person gets nothing in their bones. Now you can see the difference. So Richard gets nothing. But the other two do get a bonus payment in the mosque exam. You may be asked to change an existing form of which is incorrect or you may be asked to construct the form from scratch. Were logic logical conditions required years of other functions, such as some function? Let's look at a couple of the example, so you get a good idea for the expected now instead on the test of one logical test. But in fact you could go further and test more conditions. Using your technique, called nous, is if the most exams don't test this particular aspect of if statements, so you can skip this section if you wish Political ideas. Be aware of how it works when you think about it. In the last example, I was only testing one single condition, which is whether or not the sales person saves the pairs of mid were greater than the target that they had been given. We'll qualify wants to be a little bit more selective in the logical tests I want performed . For example, SE wants to give the Paris and a 10% of bone off target bones if they exceeded their target by 10%. Otherwise, it would restrict bombs to ascended 5%. They're too logical tests involved here. The 1st 1 is, is the target exceeded by more than 10%? If that isn't the case, then I want to test whether the target was exceeded by more than 5%. If that's not true, they're not giving your bones. We can use a necessary if Tolosa test more than one condition. In fact, we contest with 64 conditions using the following technique that's rewrite the formula. The first condition you want to test is whether the target has been exceeded by more than 10%. There's a little bit of math involved here, but it's not that difficult. So we have unequal few sign on, then, innit function. Now we have an opening backers. Next we have forced logical test is the value in into the amount, says person made greater than the says target in the 02 multiplied by 1.1. This is the same, with 110% of the targets and figure. Now, if that is true, we give a bonus off the targeted no to multiply by 10%. Now we can put in what is called a nested if statement this addition, this second replaces the value. It falls part of the statement, So we put in on their function. Open the bracket is normal. Anapa In the second logical test, this is the same as the first if statement, but only years of one point. There are five of the multiplier, which is the same as 105%. If this is true, then we pay at 5% of the target. If it isn't true, that repaired no bonus that the use of necessity is a little more complicated than you need for the mass exam. But try to walk the formal out and get used to necid. If if you intend to move to the next level of exactly fair use with snow about them, remember that it's not a requirement for the mosque or Excel exam 7. 4.2.2 to 4.2.4 Using Conditional Functions: So let's start with US statements and her tears necessities. Also next, we're going to look at the conditional version of the average count on some functions. These are essentially a combination of these functions with an if function into what is known as conditional summary functions average if counted on some if average. If this calculates the average value of all says in a range, which made a logical test, the formula functions of follows. So we have average if brackets quite to your range comma logical test comma on the average range enclosed the bracket. The average ranges option if it's not specified than the average calculations performed on the criteria range count if this cancer, Miller says, in a range that contains a non blank value a mythological test, so they have count if opening backers criteria range comma, unlock jiggle, test clothes, the bracket Some if calculate some tope of all says in a range which meet the logical test . So we have some If bracket criteria range comma logical test comma on some range. Close the bracket that some range is optional if it's not specified than the sum calculations performed on the criteria range Okay, let's see some examples of heading me years of practice for here. We have the help of flight helpless airline flight schedule. I may have the number of passengers shader the fly Say I wanted to find out the total number of passengers were going to carry from Gatwick Airport. I can use a sum if function like so we have the sequences sign. Now we have some if on an opening bracket Next, I need special. The Rangers says that I'm going to check for the logic condition on that is all of the airport names for just select the French. Next I need to put in the Kama and then the condition, which in this case is Gas week enclosed in quotation marks. Because tanks next, we need specify where we want to do to some. What we want is wherever except finds the condition to be true. That it goes over to the passenger numbers range and answer the equivalent number two some . The range you want in the form is this range Now every time gathered have found excel go across this range and extract associated passenger number another to the some notice that equal to Gatwick is implied. You don't have to explicitly post bin the average, if uncounted, functions operating in exactly the same way. So, for example, if I wanted to know the average number of passengers of flight out of Gatwick, I can just change the summit function to the average of function and get the answer straightaway. If I want to know how many flight I have listed of Gatwick, then like news account of function like so ankles, too count if opening brackets select the airport named again comma Gatwick in quotation marks. And now I don't have to specify annual the range because the things I want account are in the four Strange I specified a criteria range. 8. 4.3.1 to 4.3.3 Using Text Functions: although, except of greater manipulating numeric data, I understand some of the functions that you can use to do this. In many cases, you want perform operations on text. I remember Larry India's a long time ago on things and myself. Why would you want to manipulate text where there are several good reason to be able to do so? But in the first census, could I ask you to bear with me? I learned to understand the mechanics of the text manipulation functions, and as we go along, I provide examples which will help you to understand the contact for their use. Another term, which you used to confuse me a lot, was the term string. The image I had in my head was something somebody uses Tamp without of a ball of string. But it's just attuned people using computing to describe a set of characters. It's a simple without a news item. From now on in deflector, the tech functions which are examined in the mosque exam are are the following left this extract a specified number of characters starting from the left side off a text string. The former of the function is left. Bikers take string comma number of characters and closed the bikers. So if I put the word examine into a born on, enter the formula left breakers a one comma, two clothes bikers into B one, you can see the result X has been extracted from the original string. Right. This extract a specified number of characters starting from the right side of texting. On the former of the function is right bracket. Take string comma number of characters. Close the backers. If they added the former nun, insert right rather than left. You can see that we get an E as the answer mitt. This extract a specified number of characters starting from any position in the text string . The former of this function is mid backers texting, comma, starting position, comma number of characters and closed the brackets. If I headed for a News Mid bracket three common to close breakers, I get a M as the function south of character three returns to Carter's starting at position three. Otpor convert all characters in the texture into uppercase. The former dysfunction is Oprah backers texting Close the bikers so Oprah, a one returns the word examine all over case. Conversely, we have lower converts, all carts and texture to lower case. The former of function is the same. Lower bracket texture. Close the bracket. So on the edge of the form new, we get the word examined all in lower case concoction A's. This is just a word to describe joining. Two are more strings together. This function joins two or more textures together to create one texting all turns of the ampersand operator cumbias instead of concussion, a function the former of the function is concoction eight breakers texting one comma texting to comma on so on. Both the brackets. So the concoct a nation of these two says will return examined M. O. S. The trim function removes all spaces from text, except for single spaces between words. You can use trim on text that you've received from another application that may have irregular spacing. For example, if I have space space space, Dave Murphy. When I trim the text, I get rid of the leading spaces automatically there. Let's see some examples of how you might use the forms. In practice, we look at the concoction eight function force we can see in my helpless airline flight worksheet that we have the information. This is in a particular order. I'm going to convert to table back to normal cell reference so that there's no confusion about naming. We look at the table example presently. So let's say my manager have requested that I prepared a list of information in the following format. She wants the destination airport first, followed by the departure airport, followed by the number of passengers and lastly, the flight number. Now I could do this, but using copy and paste to generate the list in the correct order. But I want to do that fast without errors. So we have equal to concatenation opening backers on now a select the destination airport. You can say that I've been prompted for the text during they want to include, so just keep adding them. India that she wants him told the bikers. That makes us look better. We can add in some blank spaces if you want, so that's an example of consternation, a long word for a simple process. Sometimes you make it information in Excel Workbook, which is formatted incorrectly with a mix of upper and lower case characters, and you want to standardize on one of the other for consistency. This is particularly important when you're preparing the data for an automated meeting. This and you want the data to be professional and consistent, so you might have first name and last name like So let's pretend that I've purchased these data. And, as you can see, the Paris new tight information in Walsum being very careful and making sure that the Oprah and lower case of the characters was consistent, I want to be certain that they are. If I had thousands of these names, that would be a big job. But I can use the Excel text functions to make it easy. But you want to do a made sure. You want to make sure that on Lee, the first character of each is capitalized on the other there in lower case. So I'll take this step by step so that you can see how do you function to use? I mean, build it open stages. We look at the first name forced. The first thing I want to do is make sure that the first letter of the first name is in capitals. Excel works its function from inside out. So what? It doesn't find the first of using the left function like this equal to left bracket a two comma, one. Close the bikers. Now we want the result of that to always be in upper case so we can embed this formula in an opera function like so. So we have equals two upper brackets left bracket a two comma one toes. The biker and ink slows the opening backers. But an even easier way to do this is to use the proper function, which converts the first center of a text string on any letter following an on off a better character to upper case all other cards for said to lower case. The former of the function is proper bracket texture because the bikers, so you can see that this work very easily and indeed, looking at the door borrowed name, it's an ideal solution. That's six cent of the tech function you need to be aware of for the mosque core Excel exam