#### Transcripts

1. Intro: Hi, I'm Karen and I live in Switzerland. I've been self employed computer consultant for over 20 years, and I've helped hundreds of people to get comfortable with their computers. I discovered spreadsheets 35 years ago, and I've been using them in my everyday life and in my job ever since. I'm an absolute Excel enthusiast. For the past 20 years, I've been teaching people how to use their office software and have to make the most of it . I've taught beginners as well as advanced users. On. My biggest joy is when someone walks away from one of my classes with renewed enthusiasm for the software. In this class, we're going to learn how to calculate With Excel. We'll be dealing with basic calculations, working with text, working with dates and times, using look up functions and using various logical functions. For that, I'll be guiding you through an Excel file, which I've prepared for you and that you could find in the project section of this class ready for download. So by the end of this class, you should have a fairly widespread knowledge of the functions you can use in excel and how they can help you. So go ahead and roll, and I really look forward to seeing you in our first lesson
2. Basic calculations: so we're going to start with basic calculations. Excel can calculate using three different elements. The first element are constant, which are numbers. The second element is references to cells like a four B 10 or any other reference, and both of those elements will be separated by whichever operator you need. The operators on computers are plus and minus for addition and subtraction and the star and slash for multiplication and division. The third thing that could be used for calculations is what Excel calls a function. Functions are words indicating what the function does, followed by different parameters. For example, to had a column of numbers, the function will be some, followed by the range of cells. Toe add in brackets. Some functions have a series of arguments, and then they'll be separated by a comma or uncertain computers. A semi colon that depends on your computer Regional set up, and you can go check it out in the control panel in the region settings. Then you click on additional settings and then look at what shows up right here in the list . Separator. That's what you'll need to use to separate the arguments of your formula so Now that we know how to calculate, let's get going on. Look at a few basic calculations on a spreadsheet. You can go to the about section of this class and download the file. I prepared for you with several tabs, which contained this red sheets for each of the different lessons of this class. So I'm opening the file, which is called Project Workbook, and it should open on the first tab. Basic calculations. So, as you see, you have prepared a little spreadsheet for you with a list of artist names and sales amounts and dollars, there's the second column that shows the figures in Swiss francs. Because, as you may or may not know, I live in Geneva, Switzerland, so I use the Swiss rank on a daily basis. Now, if you go and click on the first of the dollar figures, you'll see that in the Selby five. It looks like this, whereas if you look in the formula bar, you'll see it looks like this. Now, if you look in the first Swiss franc cell, you'll see that both sell on the formula. Bar showed the same thing, which is this. This is because when I entered the data, I actually typed the C H f and the apostrophe on the 0.0 The end. You should never do that, and I stress never, because it will prevent Excel from calculating. We'll go ahead and change that in a minute. So let's go to the bottom of Colin. Be Click and Selby, 58. Now, to add up the column of figures we have two ways of going about it. First is to start with an equal sign, then go click on Be five, then plus then be six and keep going down the Colin. - The final formula will look like this, and the total will be $37,522. As you see, that formula works to add up a column of figures. But it's time consuming, and there's always the risk that you'll forget a cell or count one twice. So instead of using bat method, let's go use a function toe. Add this Colin up first, erase your calculation from Selby 58. You'll do that by clicking in the cell and hitting the delete button on your keyboard. Now go up to the right side of the ribbon and click on the auto. Some tool, then select some. The sum function will be inserted in your cell, and Excel will automatically add up the figures of the Colin above it. As you can see, it shows up in little dots. If you had wanted other figures, you can just go ahead and select what you want instead of what Excel suggests for you to finish entering your function. Hit the enter key as before. The total will be 37,522 but it was a much easier, painless process. I suggest you leave manual formulas with operators for cases when you don't have a choice, like subtractions divisions or even adding up two different functions together. Now we're going to copy that formula across to cell C 58 will do that by putting your cursor down here on the filin handle, which is the bottom right corner of the Selby 58 right here and we'll click and drag across to cell C 58 and there, As you see, we have a result of zero. Why, because of the way I entered my figures in Collen C as I said earlier on the fact that I typed the CHF in the cell is going to cause problems when it comes to calculating, so you can go right ahead and select all the figures and call and see under a system. So what we're going to do is to calculate this with frank value and each cell. Go ahead and click in C five. Then we'll type the equal sign click on B five than times, then enter the exchange rate of the dollar to the Frank, which right now is it 0.98 then hit Enter, and you'll have a Swiss franc value of 1254 40. Unfortunately, for now, it's showing his dollars, but will take care of that in a minute. Now you can go ahead and drag that formula down from the corner of Cell C five and track all the way to the bottom of C 56. As you can see, the color format has been copied off of the first cell in the Colin. To avoid that when we finish using the Fillon handle, we need to click on the icon on the right and select fill without formatting. This will leave my cells with the original alternate line colors, but they will also have the dollar signs. So now we'll go ahead and select all of currency, then go up to the number format of the ribbon. Click on the banknote Aiken, Select more accounting formats, then in the symbol drop down list. We type of see on her keyboard, and it will go to the first of the sea currency codes, and then we'll scroll down until we find the CHF. There. My Colin now looks as it did originally, but this time by using formatting instead of typing in every figure with the CHF symbol in front of it, and it can now be calculated with Now. If you look at the formula and see 58 which we had copied beforehand, you'll see we now have a result of 36,771 0.56. The only problem with the way I've done things here is that when the exchange rate changes , I'm going to have to change every one of my Collins he formulas. The risk here is to forget where the exchange rate has been used and still have cells calculating with an exchange rate, which has not been updated. So in the lex lesson, I'll be showing you a work around for that problem as well as a few more useful formulas, okay?
3. A few more basics: in this lesson. We're going to see how to be smart about using constant by giving them names, and we'll also learn how to calculate percentages. You can click on the tab called Basic, too. I've summarized the data from the previous tab for you and added three Collins showing sales for the same artists in Continental Europe, Great Britain and Switzerland. We're going to start by formatting or figures. Colin. Beer dollar amounts. Soldiers go ahead and click on the bank notes and select the dollar. Sign Colin. See, amounts are in euros. Now. At my end, when I click on the banknote, you'll see the drop down list shows me dollars, pounds, euros and Swiss francs, the Swiss francs or on my computer, because in the region settings, I told it I was in Switzerland. I'm not sure if the dollar and euro formats are available in the drop down menu for everyone, so I'll let you check that if it is available, all you do to format your Colin is select it. Click on the banknote tool and then select the euro. If not, you'll have to click on additional formats than select the symbol, which you want to use like this. We'll do the same thing on Collin de selecting the pound sign and now calling E with the Swiss franc symbol by clicking on additional formats. Hit the C on your keyboard, then scroll down to the CHF. There. Now we have all their Collins formatted properly. Next, we're going to add up each. Colin I showed you in the previous lesson how to click where you want your result. Then select auto some a man hit. Enter. We're going to use a shortcut in this case as we want to add up all four Collins. So what we do is select all their figures as well as the empty line where we want to put her sums on. Now we're going to go up and click on the auto some button, and there you go. Excel has calculated all there Colin totals without any help from us. This is a really cool feature. If you've got big tables where you've got loads of Collins and Rose to add up, it will do it in one click. Now I want to add up all the sales for the U. S. Europe, Britain and Switzerland, but as there. No, all in the same currency. I need to apply an exchange rate to my figures so I could add them all up to the right of my table. I have entered the current exchange rates, which we're going to use in our calculations. What we need to do is divide the currency amount by the exchange rate and then add up all the results so the final formula will look like this. I don't need to add in any parentheses because Excel will prioritize operations by first doing divisions and then sums. However, if I now drag that formula down my Colin, you'll see that we get a narrow message, which is the hash tag. Divide by zero, indicating the Excel cannot divide by zero. Let's analyze why this is the formula I entered in cell F five. Now this is the one that excel modified for cell F six. The problem here lies in the fact that this formula is not using the correct exchange rates . I need to be able to tell excel that the exchange rates are always in the same place. I'm going to do that by adding dollar signs to the reference which I need to lock. This is called an absolute reference, as opposed to relative references, which we've been using so far. I'll go ahead and change my formula and F five and then copy it down again. And there we go. Now the total is correct because I used to fill in handle twice in a row. If I click on the button and tell it not to copy the formatting, it won't change the look of my common. I should have used the undo button the first time I used the Fillon handle before changing my formula enough. Five. So what I'll need to do is use the format in Colin B and use it on Colin F. I'll do this by selecting all of Colin Be then hit the format painter button and then click on Colin F. Now I want to show you something, which is going to make things a lot easier for us. We're going to give names to our exchange rate cells. Now we can do it manually. For that, I go in select, sell em five. Then I'm going to open the ribbon called formulas and there I'm going to click on define name. A dialog box opens and suggests you are as a name for my cell. I'm just going to go ahead and click on, OK, In her case, we have three cells which we want to name. So I'm going to show you method to do it in one fell swoop rather than have it to do it one by one. So first I'm going to undo what I just did with a little back arrow up here. Now I'm going to select cells L five to m seven and now click on Create From Selection in the dialog box that comes up. Exhale suggests to use the left calling those names, which is what I want to do. So I'll go ahead and click on. OK, now if we go back in to see what happened by clicking on name manager, we'll see that sells em. Five m six and m seven are now called ur g b p. And see it, Jeff. Now let's go back to the formula. We have enough five and change the references to their names. Making the formula look like this. Okay, Now what I'll do is I'll drag the formula all the way down. And as you see, I don't have toe worry about my, um, absolute references. Okay. Along the same lines were going to calculate a 50% of 15 sorry percent sales fee, which will then deduct from the total. I'll put it over here under the exchange rates again. I'm going to name the cell. I'll call it sail Fee sell names can't have any spaces in them, so the convention is toe. Either capitalize each word or two separate words with underscores. I personally prefer the capitalization method, but both work. So whichever one you decide to choose will be fine. Now that sells named will go ahead to Colin G and enter the formula as follows, and we can go right ahead and copy that down to call them to make things easier. When you're copying formulas down to Colin, you can actually just double click on the filin handle down here. This will copy your contents as far down as the call in next to it has content. This is very convenient. If you have a large number of lines filled in now we need to calculate than that dollar amount, which I will get by taking the total U. S. Dollar amount minus the sales fee. Again, I'm going to drag the formula down without the formatting reformat $2 I have my net sales. The last thing we're going to calculate is the percentage of sales in the U. S. A. For this, we need to put in proportion the U. S. Sales with the total sales. It's a simple division, so we'll click and sell I five and topped the formula in like this where we divide the U. S. D. Amount by the total amount. Now all we need to do is slide the formula down the Colin and we'll know what percentage of sales is made in the USA. It looks weird, though, because it's not showing as percentages to get Excel to show it as a percentage will go click on the percent sign in the formatting Ruben, it will go around the number two no decimals. But if you decide you do need decimals, you can click on this little tool to add decimals or on the one next to it to remove them. So that's it, for this lesson will be delving deeper into functions in the next lesson.
4. Counting cells: in this lesson, we're going to learn how to count cells. This is very useful for statistics, keeping track of tasks you've done or knowing how many people have answered an invitation. So go ahead and click on the tab called Counting. I've drawn a lift of artist names, which I want to invite to a party. This is hypothetical, of course. As you will see, some of the artist on that list are actually dead, so let's just use your imagination. As you can see, we have four Collins, the artist name the date at which the invitation was sent to them. A Colin showing what they answered, left blank if they didn't answer yet and a common showing how many people they will show up with. First, we're going to count how many people we invited for that. Go Click and Selby 23. Go ahead and click on the auto some menu and select count numbers from the list. Now select cells from be five to be 21 and hit Enter. The result is 12 invitations sent. If you go at a date and sell B 12 you'll see the number automatically goes up to 13 So this is a very convenient little formula that will keep your accounts updated. Now hit the undo button and enter the word Yes, in B 12. This time you see, the number does not go up. This is because the function were using count only counts. Numerical cells, anything alphabetical will not be counted. Now we want to count how many replies we've received. But I entered the data in that Yes, no format. So we're going to need to count alphabetical cells. For that. We're going to click on the FX icon on the Formula Bar, which calls up the formula wizard. Select the statistical category and scroll down to find the count a formula. Then we click on OK and select cells from C five to C 21. When we hit OK, it will tell us that we have received seven replies. It doesn't tell us if the reply was positive or negative, though we'll deal with that in a minute. Now we want to know how many people will be attending the party. I have entered numbers, not Colin. So all we have to do is sum it up. There we go. So now let's look into the formula that allows us to count positive or negative replies. Let's click and B 25 type Yes, then in B 26 type note. Now we'll click in C 25 click on the formula Wizard and scroll down to find the count if function count. If allows us to count cells that match criteria, we can enter in the function or cells in which we've entered the data to match. In our case, cells B 25 26 in the range area will go and select the cells from C five to C 21 and in the criteria box will click on B 25. What this is going to do is it's going to match all the cells that have, yes, as an answer and count them. Now we can copy the formula down. It will count the no replies, but the range is counting is wrong. So let's go ahead and double click on See 26 put our mouths on the blue border of the range cell and dragged the blue rectangle up so it covers C five to C 21 again. Now we hit. Enter and it will correctly count the numbers of knows received. So as you can see, the count formula and it's variations is very useful. To start analyzing data that you have stored in Excel will delve deeper into its possibilities. And the lesson about the if functions in the lex lesson, we're going to look at functions useful for working with text.
5. Working with text: in this lesson, we're going to learn how to manipulate text cells when you get a fall from someone else. There are chances that the text data has not been entered the way you would have entered it . In this case, there are a lot of functions that you can use to make it look the way you want, so let's dive in. Click on the text functions tab of the Project workbook. As you can see, it's a database with client names, towns, birthdates and revenue. The names and the male female letters have been entered in caps in small letters and improper case. With the initial capitalized, we're going to transform all the text into one format. For this, we're going to use three functions which are lower, upper and proper. Click and Colin J. We're going to try the different text formatting functions as a column header. Enter lower case and hit. Enter now click on the Function Wizard, Select the text category and scroll down to lower hit. Okay, The only argument in this function is the text you want to put into lower case so we'll go ahead and click on Cell B two, then hit. Okay, As you can see, it has put the name in lower case. Now we'll double click on the fill in handle. In this case, as we have 250 lines in the database, it will help us save a lot of time and effort to drag the formula down. Now I'll hit the end key on the keyboard, followed by the down Arrow, and it will take me down to the bottom of the common so I can check that it's all been properly filled. End and down will take me to the last filled cell in a column to go back to the top all hit end and the up arrow. Now go to Colin Kate. Enter proper case as a header. Confirm with Enter, then click on the Function Wizard and Scroll to find the function proper again. Only one argument. Go click on B two hit Enter now a double click on the Fillon handle, and this time the initial will be in capitals. The rest in small letters. It's my preferred format. Next click on L and her upper case in the Colin header hit Enter then function Wizard Select Upper again one argument be to and then fill in handle all the way down. So which is the format that you prefer for me? It's definitely the proper case, so I'll go to Colin K and type the header last name. This will then allow me to take the contents of that Colin and put it back into Colin. Be if you prefer lower or upper case. Just change the header of Colin J or L instead. Now we'll go ahead and do the same thing. For the first names. I'll click on Colin M. And her first name as a header, then enter, then function wizard and proper and select cell C two. Then a quick double click on the filin handle. And my first names are all shipshape. Now we'll go to call it an enter M slash f as a header than in the first cell. Alleged of the function upper because in this case, I want them all to be in capitals. Click on D to then. Okay, then double click the fill it handle. Okay, so now I have three Collins which are properly formatted, which I want to put back into their original place. So What we actually need to do is copy the values we have in our favorite Colin back into call and be not the formulas. So what we'll do is copy the whole call it, then go to be click on the right mouse button and select the paste values button, which is the one with the 123 on it. As you see, it has copied all the text across, but not the formulas will now do the same for Collins, M and N. We can do that in one go, so we select him and then a copy. Go across to call in see right mouse button paste values. There we go, our databases not formatted as we wish. Another useful thing to do with text is concoct innate. That means a pending two strings of text together. For that, let's go to call on Oh, I'm going to upend the first and last names separated by a space. I can actually do that with a formula rather than a function, so I'll type equal. Then click on C two, then enter the ampersand, which indicates I want to upend text. Then I'm going to enter a space but I have to show Excel. It's text string and I'll do that by putting it in apostrophes. Then another and percent. Then click on B two. When I hit Enter, I'll get Ruth Hole as a result, which is the first and last name off the first person in our database, we can get the same result with the Khan cat function. In this case, when I entered the space between the first and last names, Excel will add the apostrophes for me. So whichever way works best for you, they both give you the same end result. Another useful function can help you isolate parts of a text left will get you the characters on the left of a text string, right? Those on the right left can be useful to just get the initials of a person. Let's go to call in Q hit FX, then select left from the tax category. We have to enter two arguments. First is the text. Second is the number of characters you want, so in this case the function will read out like this. You can also combine several functions. For instance, we could combine can Cat and left to extract the initials of people. It would look like this. And if the name was John F. Kennedy, the final result of that function would be JFK. So don't hesitate to play around with ease text functions and experiment with what they do in our next lesson will be working with dates. So see you then.
6. Working with dates: in this lesson, we're going to learn how to copulate with dates. This is very useful to culturally due dates. Figure out by how many days a task is overdue or calculate someone's age. So let's get going. Click on the tab called Working With Dates one. First of all, I want to show you a little trick if you have a numbered list to create. In this case, I have a client list and I want to add an invoice number and call in a I'm going to type I envy 001 in the first cell. Then I'll double click on the Fillon handle, and, as you see, it automatically fills in the rest of the invoice numbers. Now let's go to call an E to calculate the due date of the different invoices. I'm going on the premise that the invoices are payable within 10 days of receiving them, so I'm going to start with a sign equal, then click on D five, then plus 10 and hit Enter Excel detects that I want to calculate a date because my original cell D five is formatted as a date. Now let's double click on the Fillon handle and fill the other cells. All right now, we need to calculate by how many days Thean voice is overdue. There is a handy function called equal today, which doesn't have any arguments it calculates to today's date, and it could be used in calculations. I'm going to use it to calculate my due dates. I'll type it directly into my cell, so equals today. As you see it chose up down here. I can double click there, and it will insert the formula and then subtract due date from it. However, because I'm calculating with dates, Excel thinks my result should be a date, and so it's going to show me a weird result. So I just need to click on the home tab and then select the general format from the number of format. Drop down list a double click on the Phil and Handle to finish my formula Now. It would be nice to only calculating over due date if the bill hasn't been paid yet and will be dealing with that in the lesson about the if functions. For now, let's go to our next tab working with dates to you'll probably recognize the database from the lesson on text functions. I've added to Collins one for the age of the people. One for an age category will be categorizing them in the next lesson. So let's go and take a look at the functions available in the date and time category. Scroll right down to the bottom and you'll find a function cold year frack. This allows you to calculate the fraction of years between a start date and in the end date , and it will effectively calculate a person's age, but it will bring it back with decimals. We'll have to deal with that later. So let's start in J two. So function wizard, you're frack. The start date is the birthdate. The end date is today's date soil. Enter today and put an open and close parenthesis. The basis argument can be left blank as it is not bold. If you don't fill it in, Excel will use the default method to calculate years. The different possibilities are 0 to 4 02 and four are used for interest calculations and banking. In our case, we're calculating ages, so we want to use actual days for each year, which is option one so we'll go ahead and enter one in the formula here and hit Enter. As you can see, the ages showed and decimal format. We could remedy that by just removing the decimals in the formatting like this. But in this first persons case, that means we consider she's already 51. No, I don't know about you, but I don't really want to be counted as older before her actually turn older. So we're going to use a rounding function to get rid of the decimals. So click on your formula bar here between the equal and the why and start typing round a list will come up showing round ran down, round up and em around. In this case, we want to round down so that we only turn a year older on her birthday. So we'll double click on around, down, then click at the end of the function. Atacama Until excel, we want no decimals by typing a zero and just close the parenthesis and hit Enter double click on the Fillon handle. You must be getting the hang of this by now. And there we go. Here are a few more functions that could come in handy to you. E. Tate brings back a date X number of months ahead. Aiso Week number calculates the week number for the date. Net work days calculates number of working days between two dates, excluding weekends. There's also an international version of that function, which allows you to define what days or your weekend and if you have one or two days in a weekend, which can come in handy for businesses that are open Monday to Saturday, for example, you can see there loads of date formulas that you can experiment with and play around with in your different spreadsheets. For now, let's head to her next lesson, which will be calculating times.
7. Working with times: Now let's move to our next tab, which is calculating times. We have a spreadsheet showing takeoff and landing times for three helicopters. The first thing we want to do is fill cells B six and G six. For that, we go to use the functions Men and Max, which you find under the auto. Some many. This will retrieve the smaller our first date and the bigger or last date from my Collins. The's functions air easily available under the auto, some drop down menu. So in B six, we're going to enter men, then select the dates and times from C nine to see 14 and hit enter. We just need to reform at the cell to show only the date. There we go. Now we're going to retrieve the end dates by clicking on Max and then selecting D 92 d 40 and hit. Enter again re formats, and we have her start and end dates. No. Now let's go calculate your flight times. All we need to do in e nine is equal. Then click on D nine minus C nine and hit. Enter as you see it will show a flight time of 0.13 and lots festivals. This is given as a portion of a day, so it means this helicopter flew for 13% of a 24 hour period. Now we'll have to format it as ours to make it easy to read. First, however, we're going to copy the formula down the Colin with a double click on the Fillon handle There. Now we click on the icon next to the handle and select Fill without formatting. Now that Colin is selected, we're going to go ahead and for matter times correctly. For that, let's go ahead and click on the additional number four months. Click on time, and we're going to go ahead and select the third of the shown formats, which shows us hours and minutes. Okay, now all we need to do is add up all the flight times, using the some function, so I'll click on auto some. As you see when I click on Auto, Some Excel doesn't suggest an area to sum up. That's because it's unsure what to do with times, so we'll have to go ahead and select myself manually like this and hit enter. However, if my result isn't what I would have expected at only seven hours and 38 minutes, and I can see right off the bat. But I've got at least eight hours, so the result is definitely wrong now. This is because Excel, when it calculates with times, will reset every 24 hours and start counting again, and I need to tell it not to do that. So go back into the additional number formats, click on Custom and scroll to Time formats. The 1st 2 will show in 12 hour formats with a MPM next time. The next two will show the time in 24 are formats, but all of these will reset 24 hours. The next will show just minutes and seconds, so not useful for us and finally done here. We have the format with the H in square brackets. The square rockets indicates that it doesn't reset a 24 hours, and that is the format will need to select. And there we now have a total of 103 hours and 38 minutes, which seems a lot more accurate Now. The last thing we need to calculate on this sheet is eat Pilot's salary as you can see. I've split up their individual flight times. And if you go and look, the formula used is some if which is very convenient to calculate things like that, and I will show you how to use it in the lesson about the if formulas. But for now, we're just going to go ahead and calculate the salary. Now we would assume that all we need to do is multiply the total hours by the early salary , which is right up here at the top of the sheets. But as you see when I do that, it doesn't tally up. I only get a result which would correspond to a bit less than two hours. That again is because of how Excel stores the times. Remember, it's portions of 24 hours. So in this case, my PA that has flowed for 38 hours, which means he has flown for one point something day, not quite two days, which is why he doesn't quite get the two hour salary because it's counting days and the hours. So what I have to do is I have to multiply my formula by 24 and then multiplied by the salary Now I need to block the reference for the hourly salary with the dollars. And then I can copy the formula down with a little double click on the Fillon handle and that will I need to do is format. Them is dollars, and there we have our salaries finally calculated. Now I can add them up with the's auto some function, and the work on our spreadsheet is done. So that's little array of things that can be done with dates and times. In the next lesson will be looking at very useful functions that are called look up functions that will start automating processes, injuries, bridges.
8. The lookup function: this lesson is going to focus on the B look up function. This function is very useful to retrieve data automatically from a database. For instance, if you enter a client number, V look up will help you retrieve all of the clothes information. So let's get started and click on the look up tab. I've prepared a spreadsheet where I want to enter a client number, and I want all the other information to be retrieved automatically. I'll start by entering and client number between one and 250. This refers to the database we've been working on in the less non text functions and on calculating ages. What the look up function does is looks for a value that you enter on a spreadsheet in the first common of a table that you define and then brings back the content of another Colin of that table. So let's enter our first look up function in Selby six. Click on the Function Wizard on the category. Drop down, select, look up in reference, then scroll down to V. Look up. The arguments we have to enter can basically be summed up as what we're looking for. Where we're looking for it, What we want to retrieve and the last one do we want an exact match or not? So what we're looking for is our client code, which isn't B three where we're looking for it. Is there a database with her client list? And that's on her tab, working with dates to I'll just select the entire Collins. This allows for my database to grow if I select a fixed range of cells, and if I add lines at the bottom of my database, they won't be included in the look up. Now I tell Excel, which Colin I wanted to bring back. Now here it gets a bit tricky, as we normally refer to colons with letters. But in this case, we need to tell Excel, which number the Colin we want is. So in our case, we want the name of her client, which is in the second common of her database. Soil entering, too. The last argument is optional. If you don't set it, it will default to True. This indicates that you'd be content with the closest matching value, or, if you want an exact match, you'll have to enter false in this case, your client number is unique to one client. So I'll say false because I want an exact match. This means that if I enter a client number that doesn't exist, Excel will give me an error message rather than bring back any client that it deems matching closely. Now will hit enter. If I go change the client number, you see the name changes automatically, and that is fairly neat. As you see, if I enter a number that doesn't exist, I get a hashtag n a r a message. Now I need to copy the formula to my other cells to retrieve the first name and the amount ordered to my page. Before I do that, I just need to make my reference be three absolute with the dollar sign by hitting F four on my keyboard. Now all copy and paste my formula to be seven and be 10. All I need to do is change the Colin number being retrieved. So in B seven I will go ahead and enter call in three and in B 10 I'll go ahead and under Enter Coleman mine. I'll just format be 10 $2 now. I also want to retrieve the client's age. I want to copy the formula to the right, which means it's going to change my database reference. As you see it's now looking in Collins See toe M rather than a two K. So I can either block my references A to K or I could also name my database, so I'll go back to my client database, select all my Collins click and formula, define name and call it client table. Now I can go back to my look up tab, edit my formula and your client table and tell it to retrieve Call in 10. Now that I've retrieved the age of my client, I'd like Excel to tell me which age category they're in. For that. I've created a little look up table called age category. Look up my age categories are zero for under 21 21 for a 21 to 40 40 for 40 to 60 on 60 for over 60. I'll go ahead and enter my formula, looking up the age in the category table and bringing back the second Colin. If you look at the result now, you'll see. It tells me that my current client is under 21. Now, if I go back into my formula and put false in my range, look up argument, you'll see Excel won't bring back anything. This is because my age table refers to a range of age. I'm not going to have a table showing every single age. So in this case, because we're looking to match a number to a range of numbers, I'll leave the last argument empty. The V look up function is one that I use pretty much in every spreadsheet I make. It allows you to avoid data entry errors. You might get by copying things from one place to another. And it means that all the data you entered is always up to date. I'll go back to our working with dates tab and then to look up formula toe. Add the age category, toe all my clients. The value I'm looking up is the age of the person I'm looking in my age category. Look up table, which I'll go ahead and block with a four and bring back calling to. Now I'll double click on the filling handle and there we go. All their clients now have an age category. In the next lesson, I'll show you a way to manage the error messages you get will be look up formulas as well as other if functions that will come in handy.
9. IF functions: this'll lessons going to focus on the various IT functions. We already saw the account. If function in the lesson about counting cells now, we'll see other if functions that are very useful for all sorts of different uses. For this, I created a file called If Functions, which you'll find in the project section of class for download. So let's hope in the file and get started on their functions. First, we're going to learn how the if function works. The if function has three arguments. The first is a logical test. It's result is either true or false. A test will always be expressed by comparing two items like, for example, B three equals F or C 12 smaller than 1500 or D four greater than a one, so you can compare it with text, numerical values or other cells. The second argument is what to do if the test is true and the last argument is what to do. If the test is false, this could be a calculation, a message or even indicating to do nothing, which will indicate by putting to quote science. So let's go ahead and calculate by how many days a bill is overdue for this. We only want the calculation to be made if the bill hasn't been paid yet. So I'll take on the function wizard, go to the logical functions. Select if and click on OK, My test will be to see if the bill has been paid. And I will do this by testing if F five is greater than zero, which would indicate that there has been a payment date entered and therefore the bill has been paid. You'll immediately see the result of the first test next to the box over here. In this case, it's true. This means this particular bill has already been paid. So in this case, we have nothing to do. So my next argument will be two quotes. My value of false is going to be calculating by how many days my invoices overdue. So, in the value of false box, I'll enter today. Open brackets close them again, minus e five. No hit, enter. And in this case, get an empty cell. No, I can go ahead and drag my formula down or double click on the Fillon handle as usual. Okay. And the next Colin, I'm going to indicate by how many days the payment was late. So in this case, I want to test if it was late by comparing the paid eight to the due date. So again, click on the Function wizard and select if now will select a five greater than E five, indicating the bill was paid after the due date. In this case, I'll do a subtraction at five minus e five and get the number of days it was late by. If it's false, then again, I'll do nothing so double quotes and finally, a double click on the filling handle to copy my formula down. So this function is very useful in truthful scenarios. But what happens if you've got more than two options? In this case, you can either perform another if within your first, if which is called nesting functions. Or you can use the new ifs function available in Excel 2016 for instance. Let's say that in addition to calculating how late people have paid me, I want to know if the outstanding bills or do, or if they're still within their pay by date. This means I have four options to cater with instead of two. Option number one. The bill was paid late. Option number two. The bill was paid on time. Option number three. The bill hasn't been paid and is overdue. Option for the bill hasn't been paid, but it isn't due yet, So here's how we'll go about it. Let's start with nesting if formulas. So I hit the function wizard Select if then fill in the first part of the function like I did last time with the test and then the subtraction. The value of false is where things change. I have to enter a second if indicating that if the cell doesn't meet my first criteria, I need to do a completely different test, so I'll type the if then I'll click in the formula bar. And now, if I click on the function wizard, it will reopen a blank wizard that I can fill with my new condition and values. Now, I'm just gonna test if the bill has been paid. So all test if F five is greater than zero. If so, it means the bill was paid on time. If not, then I have to do yet another test. So all type if click on the formula bar hit the function wizard again. This time I want to test if the due date is beyond the reference date I entered. So I learned to the test like this. Then, if it's true, all type overdue, if not all, type open. Don't forget the quotes around the text values or you'll get errors in your functions there . As you can see, it can get quite complicated. So let's try the method with the ifs function. As I said, it's only available in Excel 2016 or if you have an office 3 65 subscription and the latest version of Excel installed. Theis Function allows you to enter a test and a value if true pair and have is many of 127 tests and value of true pairs. There is no value of false, so you'll have to enter a last test that will take place of your value if false. So and we go to the function Wizard Select ifs, and her first test is going to be F five greater than E five. And if yes, then we subtract F five minus C five. Our next testers F five greater than zero, which means the bill has been paid, so my value, if true, is paid on time. Next is my unpaid bills are overdue, meaning that E five is smaller than the reference date. And I left for the reference, in which case the message is overdue. And the last is my case, where all other tests were not correct. So just enter true and then put my final message, which is open as the bill isn't paid. But it isn't due yet there. This is much easier than nesting if functions. So if it's available in your version of Excel, I would strongly advise you to use it. A quick comment about it functions. Excel will stop testing as soon as it has found one condition to be true. So be careful as to which order you put the arguments in. In my previous case, if I had tested if F five was greater than zero first, meaning the bill was paid, then all of my paid bills would have said paid on time. It would not have gone on to test if the payday was greater than due date, because for Excel, the first condition was met and so the rest of the formula would not be evaluated. So if you're not getting the results which you're expecting to get, make sure that the logic of your testing is correct and that you're testing things in the right order.
10. SUMIF functions: Now let's go to our next tab to see how the function some. If works, so click on the top some if you'll probably remember the spreadsheet with helicopter Flying Times, so now we'll see how to calculate the flight time by pilot on by helicopter. The summer function is very similar to the count. If function that we saw in the lesson about counting cells, it has three arguments the area to test the criteria to meet and the area to sum up. So in our case, we're going to add all the times that have her first public name in the name area. So let's click on the Function Wizard, then go to the math and trade category, then click and list. Hit the S on her keyboard and scroll down until we find some if a little side note. If you don't know what category to find your function in, you go to the all category or in the search at the top of the function wizard, you type some if and hit enter, it will display the functions that it thinks match your search. So our first argument is to select all the names in the spreadsheets. Don't forget to hit F for on your keyboard to block the reference so we can copy it down. The second argument is the name of her current polit Anderson. We could type it in the function, but then we'd need to edit the function to reach Pilot. So instead will go click on Cell A 47 where Anderson's name has been entered. Last. We need to select the Colin of Flight times, which is what we want to add up again at four so we can copy. Make sure that both your name area and your flight times area have the same number of lines . Or Excel might not calculate your some accurately. No, we'll hit, Enter and Excel shows. It's a total flight time for Anderson of 38 hours and 16 minutes. Now double click on your filling handle, then click on the icon to the right and select fill without formatting there. Now we'll do the same for helicopter names. - Now we'll show your neat little trick if you want to set up a little tool to select which pilot or which helicopter has flown on the total time for it that start by naming or private list . Select your cells good formula tab. Click on to find name type pilots and hit. Enter. Next Will Did the same for a helicopter list. Select defined name type helicopters and hit. Enter Now go to a 55 and type Select pilots go to be 55 type Select Helicopter. Now we're going to create a drop down lists that you can select which part that you want, So for that will click in a 56. Then we go to the data tab, click on data validation and in the dialogue box under allow select list and then in source type equal pilots, which is the name you define for the pilot list. Now click on. OK, Now you see a drop down our next to the cell, and when you click on it, you get a list of the four pilots. Okay, let's do the same. The helicopter area and Selby 56. Now we're going to use the function. Some ifs again. The Sony exists in Excel 2016. So function wizard, math and Trig. Then it s scroll down and select some ifs. The first argument is the Cullen that we want to sum up. So our flight times list. Next, we want to enter criteria areas and the criteria to match first pilot names. Then select a 56 where the partner name is selected. Next helicopter names, then select B 56 where the helicopter name is selected. When you hit Enter, it will return a value, but it's not formatted as a time, so we need to go into the formats. So click on the home tab, then select additional number format. Lincoln Custom Scroll to find the hour format with the H in square brackets there. Now, if you click on the drop down list and select another pilot, the total flight time will automatically adjust. Isn't that a very cool front?
11. Other IF functions: So we're gonna finish this class by using a few of the new Excel 2016 functions. The next tab is a client database, which by now you're familiar with because it's the same one we've been using and then the last tab is a client statistics page. So I've entered what I want in English, but I now need to lay it down and Excel functions to be able to get a result. The first question is how many clients are under 21 in New York? So my criteria are the age category has to be equal to under 21 on the state has to be. And why so Electra this and sells a four and a five, and then in a seven, I'll start the function wizard. Select the statistical category and then click on see on my keyboard and scroll to Count ifs a nanny to enter criteria range and criteria pairs. So the first pair is my age category, so I'll select the client database, select the whole of Colin K and then in the criteria, I'll go click on a four. Now my second criteria range is back on the client database. Call an F this time and my criteria is a five. And when I hit enter, it will show me that I have three clients under 21 in New York. Now, I'll do the same for my total revenue earned in Oregon for clients over 60. So I'm gonna go put my criteria in a 12 and a 13 like this. Then I started function wizard in a 15. The category is gonna be math and trig. Then I hit the S on my keyboard and scroll to some ifs. In this case, my first range is the common I want to add up. So back onto the client database called an I. And then I've got to enter my different criteria ranges. So my criteria range Number one is calling f. The criteria is a 12. My criteria range number two is Colin K, and the criteria for that is 1/13 hit. Enter. Other result is 489,870 which I now I just need to format as dollars there. The last thing I want to do is the average age of the female clients in Washington. So in a 19 I'll enter F for female eminent A 20 I'll enter W. A for Washington, and in a 22 I'll start the function wizard category statistical again and select average ifs. The first range is the range I want toe average. So, in this case, the colon with my client ages, which is Colin J. And then my criteria range Number one is the male female Colin, which is called in D. The criteria is in a 19 then the criteria range number two is calling F, and my criteria is a 20 I had enter, and I get a result of 46 a half years. So that says that the end of the if functions lesson. As you can see, there are lots of possibilities out there, and I just advise you to play around with them as much as you can to see how much of a potential these functions have to help you out on all sorts of spreadsheets.
12. Class project: in what way? Class project that I prepared for you is to try and set up a simple invoicing system. For that I'm prepared to file in which you'll have a client database and a product database client databases, the one we've been working through in our various lessons. So you should be comfortable with it by now. I have also attached an image of the final invoice template, which I would like you to set up. Now the colors and all of that will be up to you. I've used the colors that I enjoy, but you can use whichever color you prefer. As for the formulas and the functions that you're going to be using in this invoice, I will let it entirely up to you. I'm not going to guide you too much because the idea is for you to try and find the potential of Excel and all the functions that we've seen together and set them up in your own invoice. Now what I'm going to do is I'm going to put a final version of my file in there as well, and that way you can compare what you have done with what I have done it doesn't mean that what you have done is wrong. If you haven't done it the way I've done it, it just means we've got two different ways of doing the same thing. That is not unusual with Excel. If you have any doubts, or if you want clarification, or if you want to ask me a question, just start to discussion below, and I will answer your questions as students. I see them, so good luck with your work, and I look forward to seeing your results in the project section below.
13. Outro: Thanks for taking this class. I hope that your newfound knowledge is going to help you make your Excel files a lot easier to use for you. I will hopefully see you in one of my other classes. Thank you very much. Help me out. You can click on Thank a teacher and you can also follow me. Thank you very much and see you soon.