# Excel 2007: Advanced Formulas and Functions

## with Dennis Taylor

In Excel 2007: Advanced Formulas and Functions, author and trainer Dennis Taylor demystifies some of the most challenging of the 300+ formulas and functions in Excel and shows how to put them to their best use. Dennis starts with a review of the more basic, building-block functions, and a few critical keyboard shortcuts that will speed up working with Excel data, even on multiple sheets. He then covers how to perform advanced searching and data retrieval with Lookup functions, tabulate and sort data with counting and statistical functions, format data with text and math functions, and even work with financial data using advanced formulas. Dennis focuses on practical examples that will help users easily transition to using these formulas and functions in real-world scenarios. Exercise files accompany this course.
Topics include:
• Referencing, copying, updating, and converting formulas
• Using the logical functions and creating compound logic tests
• Searching for and matching data based on specific criteria
• Formatting cell data using functions
• Calculating dates, times, and days of the week
• Analyzing mathematical and financial data
author
Dennis Taylor
subject
software
Excel 2007
level
duration
5h 17m
released
Nov 25, 2009

Keep up with news, tips, and latest courses.

• ### FAQs

Search the closed captioning text for this course by entering the keyword you’d like to search, or browse the closed captioning text by selecting the chapter name below and choosing the video title you’d like to review.

Introduction
Welcome
Using the exercise files
 00:00 If you are a premium member of the lynda.com Online Training Library or if you 00:05 are watching this tutorial on a DVD-ROM, you have access to the exercise files 00:10 used throughout this title. 00:12 In the Exercise Files folder, you will see the names of each of the chapters. 00:18 Simply double-click on the chapter you're interested in, for example, Chapter 1 Tips. 00:24 The worksheet tabs correspond with the names being presented within the chapter. 00:29 Occasionally, more than one worksheet will be used at the same time. 00:33 In chapter 1, one of the examples does involve use of the East, South, Midwest 00:39 and West sheets, as well as a Summary sheet. 00:42 In most other examples, a particular movie will be devoted to just one sheet 00:47 tab, for example, highlighting formulas is all discussed when the sheet tab is present. 00:52 So, the name of each worksheet is the critical aspect to this, and that's going 00:56 to be true in all the different chapters. 00:59 If you don't have access to the Exercise Files, you can follow along from 01:03 scratch or with your own assets. 01:05 Let's get started. Collapse this transcript
1. Formula and Function Tips/Shortcuts
Using the entire row/column references
Copying column formulas instantly
Converting formulas to values with a simple drag
 00:00 After creating formulas, there will be times when you effectively don't want to 00:04 keep the formula in a cell. 00:05 You want to keep the actual result. 00:08 Now, having many, many formulas in a particular range, or in a worksheet, 00:12 sometimes does take up not only more space but also requires some time because 00:16 of recalculation every time we make worksheet changes. 00:19 So let's imagine the example here in column H, about 600 names or so. 00:24 And we've got formulas calculating New Salary. 00:27 Perhaps you've reached that time when you want to say that you'd like to have 00:31 these salaries replace the ones that are the origin over in column F. You may, on 00:37 the other hand, want to keep these new salaries here, perhaps re-label the column 00:41 headings, but there are times when you would be saying, like here, "Let's not have 00:45 "these formulas here. 00:46 "Let's just keep the results." 00:48 The standard way to handle this is a somewhat cumbersome technique of copying 00:53 these values and then using Paste special values and so on. 00:57 Let's make this really fast. 00:59 Now, two different approaches here. If we want this data here to replace the data 01:04 in column F, we, effectively, would select these values. 01:08 So how can you do that somewhat quickly? 01:10 By simply clicking on the top cell, hold down the Shift key and double-click the 01:15 bottom edge. Now that highlights the entire set of data. 01:20 If you hit Ctrl+Period, this will move the active cell to the top of the range. 01:26 I can see what's happening there, they've all been selected. 01:28 You can certainly do that by dragging as well and it will stop when you use this 01:32 technique once you get to the bottom of the column. 01:36 Rather than going through the multi- step copy, paste special, with the right 01:40 mouse button, drag this information on top of the old data. 01:45 That's over in column F. 01:46 With the right mouse button, drag any edge. 01:49 As soon as we get to the destination, you can let go and Copy Here as Values Only. 01:56 Keep an eye on, say, the top salary, the 59,246. 01:58 We are now about to see that over in cell F2. It is in H2. 02:04 It's going to move over into F2. 02:09 Now, meanwhile, we've got some new salaries here, they're irrelevant, but 02:12 here is the value that had been displayed in H2. It was actually a formula and now it's 02:17 over here as a value. 02:19 So, in effect, we took those new salaries and replaced the old salaries right here. 02:24 This information we wouldn't need anymore, perhaps get rid of the entire column if 02:28 that seems appropriate, but, again, this information right here is not relevant, 02:32 but we quickly were able to copy this information over here and throw away the 02:36 formulas at the same time. 02:38 Now there is a similar situation. 02:39 You might use this over in column N. You see order dates and shipping dates and 02:45 simple formulas here, subtracting the two to figure out the time elapsed. 02:50 Perhaps it is at the time when you say you no longer need the formulas here. 02:55 A lot of these you could have done in your head anyway, but there are formulas. We 02:57 simply want to turn these into their values. 03:00 Now, in this situation, we don't really want to put them into another column. 03:04 We want them to stay right here, and so it might seem a little bit strange but 03:08 what you'd want to do here is essentially drag this information into another 03:13 location and then right back on top of the current data. 03:16 It might be faster here to drag the top edge, but it can be any edge, and once 03:22 again, with the right mouse button. 03:23 Remember, every cell here that's highlighted is a formula. 03:27 It subtracts the two dates from the left. 03:30 So by highlighting the data and dragging any edge with the right mouse button 03:34 away from the data, I'm going up here and then back down on top of it, let go 03:39 with the mouse, Copy Here as Values Only. 03:43 This is not a formula anymore. 03:44 It's the value 2 and the value 4, and you can verify that by seeing this in the Formula bar. 03:50 So there are numerous situations when you have copied information, you've 03:54 cleaned up information, you've got formulas and in effect, you want to say, "I 03:59 "don't want these formulas anymore. 04:00 "I just want to keep the results." 04:02 So the techniques we've seen here allow us to do this quickly and easily. 04:06 I want to show you one more variation on this. 04:08 You can do this with the toolbar as well. 04:11 So let me do an Undo, and this isn't quite as fast as the dragging method, but 04:15 it has its merits as well. 04:17 With the data highlighted here and these being formulas again, on the Home tab, 04:23 you can click the Copy button and then jump over to the Paste arrow and choose 04:28 Formulas, and that's pretty fast too. 04:31 And to get rid of those Marquee lights simply hit Escape. 04:34 Different methods of quickly turning formulas into values. Collapse this transcript
Creating 3D formulas to gather data from multiple sheets
Updating values without formulas
Showing all formulas with a single command
Highlighting formulas with two clicks
 00:00 A valuable feature in allowing you to highlight a worksheet to indicate where 00:05 formulas are, where constants are, is certainly valuable and easy to get to in Excel 2007. 00:13 This worksheet has a lot of formulas in it. 00:15 It has got a lot of pure values as well. 00:17 If we would like to select the cells that have formulas, not really see them, 00:21 but just select them and then possibly add a color, 00:24 that would be valuable, and of course, the feature is here. 00:27 On the Home tab of the ribbon, the extreme rightmost group is referred to as the 00:32 Editing group, and the very last button typically is Find & Select. 00:36 Now, with the active cell anywhere in your worksheet, but only on a single cell, 00:41 click this button, Find & Select, and then click Formulas. 00:46 All cells that have formulas are currently highlighted. 00:49 Now, you can certainly look at that and in some cases you will remember the ones 00:53 that are highlighted, but certainly, in this case, you wouldn't. 00:56 What you really would want to do next here would be to add a color to 01:00 these, just to flag them. 01:01 Now, it wouldn't necessarily be a color. 01:03 You can make them be bold or italic or give them borders, but the most 01:07 obvious difference we could make to these, while they are selected, is to 01:10 change their color. 01:12 And as fast as any way to do this would be on the Home tab, in the Font group, 01:17 the button for Fill Color, click the drop arrow, pick a color, and as you slide 01:22 across these, you can review them. 01:24 Lighter colors typically do work better. There we go. 01:27 So, in this case, it's an orange color, and until we add new formulas, this 01:33 is totally accurate. 01:35 All these cells that have this color, have formulas in them. 01:38 We didn't change their content in anyway. 01:40 We just know at a glance. 01:42 When you're doing worksheet troubleshooting, you're trying to figure out what's 01:45 going on, this is helpful. 01:48 If I were to erase one of these cells with formulas, here is one that's coming 01:51 out to be a 0 anyway, but if I delete this, the color stays, and if we were to 01:57 do the reverse, if we add a formula somewhere, it will not turn this color. 02:02 In other words, we do have to run this feature again to make it truly be up-to-date. 02:06 Now, a companion to this could be equally valuable, or more so. 02:10 And that's the idea of saying that you would like to highlight all the cells in 02:14 this worksheet that just have pure values in them, and maybe to make it a little 02:19 bit tighter, just pure numbers, not text. 02:23 Here it takes a few more steps, and once again, click on a single cell. 02:27 If you do happen to have three or four cells highlighted when you attempt 02:31 to highlight either formulas or constants, it only looks within the highlighted range. 02:37 By clicking on a single cell, even though it's not obvious, it implies 02:41 the entire worksheet. 02:43 This time, we are looking to highlight the cells that have constants. 02:48 Once again, on the Home tab, in the Editing group, we want to choose Find & Select. 02:53 This time, we want to click on, and it wouldn't be an obvious choice unless you 02:58 had explored these options, Go To Special. 03:02 And this dialog box, which was present in prior versions of Excel, gives us lots 03:07 of different choices. 03:09 At first glance, it might throw you a little bit, if you clicked the Constants 03:13 button, you'll notice that the checkboxes under Formulas are also available. 03:19 You might have thought that those boxes are related only to formulas. They're 03:22 also related to constants. 03:24 Now, if we simply click OK here, all cells that have any data but are not 03:30 formulas would be automatically highlighted here. 03:33 What we would want to do here would be to uncheck the boxes for Text, Logicals, 03:40 and Errors, leaving only Numbers highlighted. 03:43 Click OK, and now all the cells that have pure values are highlighted. 03:49 And here too, if we wanted to make a note of this, for a while, we might use that 03:53 same fill color bucket that we just used for formulas, and this time pick a 03:58 different color and make these, say, green. 04:02 So based on this coloring scheme, and it's accurate at the moment, like a 04:05 snapshot in time, the green cells have constant values in them, the orange 04:12 cells have formulas. 04:14 So these two techniques, in particular the one for highlighting formulas, can be 04:18 really valuable in scoping out a worksheet and helping you figure out what's going on a bit faster. Collapse this transcript
Simplifying debugging formulas
 00:00 Occasionally, in Excel, you've got long formulas and they're not always giving you 00:04 the answers that you're expecting. 00:06 So there are two quick techniques for helping you debug formulas. 00:10 There are others as well, but here are two short ones. 00:13 In cell F2 is a formula involving not too many other cells, but imagine if it's 00:18 not quite coming up with the answer that you expected. 00:21 You think something is wrong. 00:21 It doesn't read quite right, not sure. 00:25 When you edit a formula, and you can do that by double-clicking in the cell or 00:29 clicking in the Formula bar, 00:30 I'm going to double click in cell F2, what you can do is highlight portions of 00:36 the formula to see what that portion evaluates to. 00:39 For example, if we were to highlight just these cells here, and we wonder, 00:45 "What does that equal to?" right now, on the spot? 00:48 Highlight some data and hit the F9 key. 00:51 That shows us the value of that. 00:54 Now, that might or might not have been revealing, in this case. 00:57 Maybe if we highlight that and the portion of the formula to the left of it, E17 01:03 times that, and we see this. 01:07 That still might or might not have helped. 01:09 So you will have to try this with different components of a formula. 01:13 Now, you want to be careful when you're doing this, as soon as that light bulb 01:17 goes on that says "Oh yeah, now I've got it figured out," 01:19 don't hit Enter here. 01:20 Hit Escape, because sometimes you're not really trying to change the formula. 01:26 You want to give it some thought, maybe. 01:28 Perhaps it is the wrong cell, or maybe you've got these in the wrong order somehow. 01:33 When you're doing this, you might get a strange message if what you're 01:35 highlighting, by itself, doesn't make for a coherent formula. 01:40 For example, if I were to highlight these cells here, and you're seeing why that 01:45 obviously is not a correct formula by itself. 01:48 Hitting F9 here, that goes off into a little tangent to tell us something else 01:53 that really isn't appropriate. 01:54 So I will just click OK, get out of there, and consider this again. 01:58 You certainly can highlight areas that include parentheses. 02:02 Maybe this whole part of the formula is something, maybe, you suspect is a bit off. 02:06 You might highlight that, hit F9. 02:09 Once again, does that help, does it not? 02:11 Maybe include this part of it as well. 02:14 You also run the risk, occasionally, when you do this, of highlighting cells 02:18 where the formula might not be exactly the same order that Excel is 02:21 calculating them in. 02:22 So occasionally, when you do this, it can be misleading. 02:26 But keep in mind the idea that you're just questioning this. 02:29 If you happen to hit Enter, then do a quick Undo, if you really didn't want to 02:33 change it into its values. 02:35 Normally, though, we just hit Escape. 02:37 Now, another shortcut that, by itself, doesn't really debug a formula, but it's 02:42 going to give you some help. 02:44 In cell G16 here is a really, really long formula. 02:49 And this discussion is not really about the If function, and so that's not 02:52 really relevant here, but this is one of those ridiculously long formulas that 02:57 you will see sometimes, and I'm not saying that it's wrong or right. 03:00 In fact, maybe I shouldn't even have said it's ridiculous, but it's pretty long. 03:04 And here's what you can do, sometimes, that will be really helpful. 03:08 This function, like we say, it's quite long, and while editing it, either in 03:13 the Formula Bar, or if you have double-clicked in the cell, as I have, you can try the following. 03:18 In front of the second IF, I'm going to click right here, and use Alt+Enter. 03:22 Now, Alt+Enter simply introduces a line break. 03:25 I'm going to do this in front of the next IF, and that's an arbitrary choice. 03:30 You can put it wherever you want, but I think this would be a logical place. 03:33 Alt+Enter, click in front of the next IF, hit Alt+Enter, and in front of this 03:38 one, and maybe even the first one. 03:41 Now, I would never make the claim that you instantly understand everything 03:45 now, but this certainly structures the formula in a much more coherent way, 03:50 and, of course, you would need to know something about the If function and the And function. 03:55 But the way this displays now, it's much more coherent. 03:59 You've got a much better chance of figuring out how it might be working or not working. 04:03 And here too, as another example, you can highlight portions of this, click F9. 04:08 That will tell you, in logical constructions, whether this is true or false. 04:14 And that helps also in deciphering the formula and trying to figure out what's going on. 04:19 But this is a Nested If, and sometimes these are quite long, and if you didn't 04:23 write it or if you wrote it a long time ago, you need something like this to 04:28 really give you a handle on it and help you figure out what's going on. 04:31 Eventually here, I might either hit Escape or get out of here. 04:34 And maybe I didn't quite finish that the right way, so I would do it again. 04:37 But the point is by doing this you are not really changing the result, unless, of 04:43 course, you hit F9 and hit Enter. 04:46 But by using this capability here, of using Alt+Enter that is, you can 04:51 structure this differently. 04:53 Now, hitting Enter here does not change the formula in any way, other than its display. 04:58 If you happen to come back here again, one downside of this, look in the 05:02 Formula Bar right now. 05:03 You don't see this. 05:04 You don't see all of it because the equal sign is by itself. 05:08 Double-clicking lets you see it here, but how are you going to know that otherwise? 05:13 You don't always foresee that, unless perhaps you were the one who has done this. 05:17 But here is something else you can do. 05:18 You can make the Formula Bar taller, just slide onto its bottom edge up there and do that. 05:24 So that's going to help. 05:25 You've got more room to edit without having it overhanging to the worksheet area. 05:30 So two techniques for helping you figure out what works in a formula and what doesn't work. Collapse this transcript
Creating range names to enhance readability
2. Excel Functions
Understanding and using basic Excel functions
Using common functions: SUM, AVERAGE, MAX, and MIN
Using and extending AutoSum
3. IF and Related Functions
Exploring IF logical tests and using relational operators
Creating and expanding the use of nested IFs
Using the AND, OR, and NOT functions with IF to create compound logical tests
 00:00 The IF function is frequently used with other functions as well, and using 00:05 multiple functions together is not exactly a goal, or shouldn't be a 00:09 goal, but on the other hand, there are going to be times when you are trying 00:12 to use the IF function to achieve more than just a simple test on a single set of values. 00:18 Imagine an organization that wants to provide a bonus for people here, based on 00:21 their job rating, and on their status and imagine their rule is simply this: 00:27 If your job rating is 4 or 5 and you are Full Time, we are going to give you a 00:31 bonus of \$1000, otherwise not. 00:33 So a simple IF function would begin =if(. 00:38 Now, we want to test for two conditions here, in no particular order, perhaps 00:43 the job rating first. Is this greater than 3? 00:47 That's one way to say it. 00:48 We said 4 or 5, so that that would suffice. 00:50 Now, another condition here, and we need to precede this. 00:55 Now we can type this later, but why not put it in right now. 00:57 The word 'and' is actually a function and, AND, as a function followed by a 01:03 series of arguments, two or more, separated by commas. 01:07 And the word 'and' is inclusive, meaning this condition must be true and the 01:12 second one we're about to put in as well, if this person is to get \$1000. 01:16 So, the second condition here is that the status here be ="Full Time". 01:23 Now the entries in the list there under Full Time with a space, double quote. 01:29 The upper and lower case is not critical here, but the space between Full 01:33 and Time certainly is. Right parenthesis. 01:36 This is one use of the AND function as the logical test for an IF. 01:42 You might be wondering, "Why can't we put the word 'and' between the two conditions. 01:46 The reason is that you can have multiple conditions. 01:50 We might have a third condition, a fourth condition, a fifth condition here, 01:53 separated by commas, and rather than using the word 'and' over, and over, and over 01:57 again, we, essentially, put it in front of the parenthesis. 02:01 If both of these are true, comma, the answer is 1000. 02:07 Otherwise, the answer is zero. 02:10 And once again, in the situation where you have got multiple sets of parentheses, 02:14 they must be entered or typed properly. 02:18 And we will drag this down a few cells to check it out and you can see it looks 02:22 like nobody in this list has these. 02:24 So, we will change one of these people here to make, for example, this person, make that be a 4. 02:29 Actually, a few did turn up there. 02:31 There we go, a little more clearly. 02:34 You can see what's happening. 02:35 As we look at this function here, recognize that at certain other points, and 02:40 let's show just for the effect of how this might be different, we do have a 02:44 third rule here to have been here more than ten years. 02:48 So, the year status here, greater than 10. 02:51 Now, if you think this out a little bit, if you have got the combination here, 02:56 you have actually got eight possibilities. 02:58 I won't go through all the iterations but you have three trues, and two trues 03:03 and a false, and a true and two falses and so on and so on. 03:05 That's eight possibilities, and you have got three entries here, and only when 03:10 all three of these are true, does this person get a \$1000. 03:15 The other word that we use frequently, but certainly not at the same time, but in 03:19 context, the word 'or' has, as it does in English, a completely different meaning, 03:25 and now we're saying if any of them are true. 03:27 In other words, if the job rating is good or the status is Full Time, or the 03:31 years of service is above 10, you get \$1000. 03:34 So, recopying this list here, looks like everybody wins out here, except the 03:39 last person in the list, maybe. 03:40 It depends upon the entries. 03:41 And once again, you can go through some of the variations, test this again. 03:46 This person down here, we could play with that a little bit. 03:48 Just for sake of argument here, we will make this person be Hourly. 03:51 Again, these little tests you will do from time to time, just to kind of make 03:54 sure that it's working okay. 03:55 We would be doing undo to verify that that appears to be working as well. 04:01 So, 'or' and 'and' have a role to play and there will be times, and it gets a little 04:05 tricky, where you use 'and's inside of 'or's. 04:09 Suppose, once again, this organization changes its mind and says the following. 04:12 "We are going to give you a bonus here for one of two major reasons." So here is the 'or'. 04:18 "The first reason is you have got a good job rating. 04:21 "The second reason is if you don't have a good job rating, we will check to 04:25 "see if you have got a combination of being full time and you have enough years of service." 04:31 So here we will put the word 'and' in and this is an 'and' inside of an 'or'. 04:37 If your status is Full Time and your years of service is this, and one more 04:43 parenthesis here to match the green left parenthesis up here. 04:47 So that's certainly tricky and when you are doing this yourself and it's your 04:50 own data, it certainly makes sense, and yet you have those little doubts until 04:55 you try this and I am going to put it in here and recopy it. 04:58 You can take a look at some of these here. 05:01 So here and there, you will see situations that look at first like they 05:04 should work, not that one. 05:06 But this person here has a good enough status but not a good job rating. Has enough years. 05:12 So, once again, looking at just this line here, we said if one of two conditions 05:16 is true, you've got a good job rating. 05:18 This person doesn't have that. 05:19 What's the other possibility? 05:21 A combination of Full Time and enough years of service. 05:25 And on this case here, this one should work and it does. So we see that. 05:32 Once again, these are the kind of things that instill a certain amount of 05:36 pride in tackling these and making them work and yet a few days later, you 05:40 come back and look at them. 05:41 You are not always sure you got it right. 05:43 So you have to kind of think of that a little bit. 05:45 And certainly when it is your own data, you have got that edge that helps you. 05:48 But don't overlook these possibilities. 05:51 A third possibility in all of these is the word 'not' which I try, pardon the pun, not to use, but 05:57 you can use this here, and I wouldn't use in this case really like this, but 06:01 just to show you that it can be. 06:03 Sometimes it might be slightly more efficient to say, "Okay. If it's not the case 06:09 "that your job rating is greater than 3, if that's not the case, 06:14 "we are going to give you zero here, otherwise 1000." 06:19 So, that's reverse logic, but it does give us the answers, here. 06:23 In other words, the only people who get bonuses here are those with 4s or 06:26 5s, but using the logic this way invariably seems to be a little more 06:30 awkward to understand. 06:31 In some of these, you might see an older usage of Excel or maybe in 06:36 formula-intensive worksheet, someone decide to say well, I will take the most 06:40 common use and get it out of the way first, as if there is some time lapse here. 06:44 But do be aware that you might see that and it might makes some sense here 06:49 and there, although I think rarely, to be used in the same context with an IF function. Collapse this transcript
4. Lookup and Reference Functions
Looking up information with VLOOKUP and HLOOKUP
Using VLOOKUP for approximate matches
Using VLOOKUP for exact matches
 00:01 When you are trying to lookup information in a table, sometimes the information 00:05 that you are focusing on is text-based. 00:08 Now, in the list that you are seeing on the screen here, there is a Rate Table on the 00:12 left-hand side, and this actually has a range name. 00:14 The cells I am highlighting here from A2 to B8 have the collective name 00:18 RateTable, and you can quickly see what's going on here, or is about to go on, 00:22 because something is not working just yet. 00:24 Each of these ratings here, you will see their appropriate positioning in the 00:28 table there, try and translate them into a numerical score. 00:31 So maybe this is a time-honored approach we've used for ratings or changed 00:35 them into a numerical score, and you can see that the 99 here for Fair isn't accurate. 00:40 So, what's going on here, in cell F2? 00:43 This is using the VLOOKUP function and it is the function to be using here. 00:47 We do want to look up information from the left column of that RateTable over in 00:53 column A and we are trying to find the word 'Fair'. 00:57 But unlike numerical situations, when you are using text or if you're using 01:02 numbers on a non-computational way, say ID numbers, when you are looking up 01:06 information you need a different approach here if you're trying to find an exact match. 01:11 Now unlike a numerical lookup, many times during which we need to find an 01:16 approximate location, we need to find the word 'Fair' over here exactly, and then 01:21 go get the value 71. 01:22 Based on what we are seeing here, this seems to be in place. 01:29 We do want to take that information, the word 'Fair', look it up in the left column 01:33 of the table and when we find it, we do want to go into the second column to get 01:37 the answer, but 99 is not the answer. 01:39 That's the answer you just saw there. 01:41 For exact matches, we need a fourth argument here. 01:45 So following the Column Index number, that's what's there now, a comma, and what 01:50 we next put in certainly doesn't flow out of any innate logic. 01:54 It's either the number 0 or the word FALSE, whatever you prefer. 01:58 I use 0 because it's less typing, but this is what we do for an exact match. 02:04 What we're doing with VLOOKUP here is to say now, when we find this information 02:10 the word 'Fair' exactly, we go into the second column and get the answer there. 02:15 And when we do, as is here, we get the appropriate score, it's a 71. 02:20 Then we copy this down the column to take care of the others as well. 02:23 Now, not all exact matches, as I said, involve text but I think probably 90% of them do. 02:29 The other kind of situation might be if these are employee ID numbers and maybe 02:34 they truly are numbers or Social Security numbers, and need I say that with Social 02:39 Security numbers we are not looking for an approximate match. 02:42 You need to have an exact match with certain kinds of numerical lookups. 02:46 But anything that's computationally based, that's an approximate match. 02:51 So let's get back to this idea of these and what are the limitations. 02:54 First of all, the order here is not in ascending order. 02:59 Now it could be, but imagine how ridiculous this table would look if we put it 03:03 in alphabetical order. 03:05 Excellent would be at the top, but we'd have Very Good at the bottom and of 03:09 course the order of the list would be crazy and the numbers would be all over the place. 03:12 So we wouldn't even consider that here. 03:14 So the order is irrelevant. 03:16 Well, irrelevant in the sense that it can be in any order, alphabetically, but 03:21 it's a coherent logical order, and of course, these are in order, descending by the scores. 03:26 When you are doing an exact match, the order of the information in the left-hand 03:32 column is a logical order, not an alphabetic order or a numerical order, and the 03:38 fourth argument, the 0 or the word FALSE, which, by the way, is not in quotes 03:42 either, will mean we are trying to find this exactly. 03:45 So what happens here, when we have information here that doesn't match? 03:49 Now here's something that's a little insidious and you might run into this at 03:52 different times. I am going to put a trailing space here, N/A. That's P-o-o-r 03:59 and over here what do we have? 04:01 Well, we are trying to find that information in the table, and over in the table it's P-o-o-r. 04:07 That's it. Not a match. 04:08 If you need to work around this, I could throw in a quick solution here. 04:12 You can look at not just E6, but use the trimmed version of this. 04:17 This means trim off trailing, leading and multiple consecutive interspaces, 04:22 reduce those to 1, and in this case here, we are looking for the trimmed version of this. 04:28 Now, in other situations, if you just happened to have a word that isn't there at 04:32 all, for example, Kit Carson's performance here, that's just so-so. 04:37 Well, that's not in the list, and of course, this is not found. 04:40 So I think you can see there is a distinct set of situations where an exact match 04:46 is exactly what we want. 04:47 It's usually text and it works easily and quickly, but nobody will call this intuitive. 04:52 You just have to have that fourth argument, either 0 or FALSE, to signify that 04:56 this is an exact match. Collapse this transcript
Nesting Lookup functions
 00:00 The more you work with Excel formulas and functions, the more you encounter the 00:04 need to use functions together and sometimes not just tacked onto the end of one 00:09 another, but actually used within one another. 00:11 We call this nesting, and perhaps you have encountered this and you might 00:15 have needed this as you use the IF function. 00:18 But sometimes this arises, also, when using a VLOOKUP function. 00:21 For example, on the screen here, in column F, we see various entries and farther 00:26 to left are people's names. 00:27 We are trying to look up the regional tax rate here. 00:31 First of all, note that there is a table here in column J and K that lists all 00:36 of the states and the region that they happened to be in. 00:39 First example, here. We'd like to find out which region Colorado belongs in. 00:43 You can see here that it's in the MT, presumably Mountain region, here. 00:46 So let's do a VLOOKUP here using the Colorado reference, comma. 00:53 The table here has a range name. Rather than highlighting it, which I sometimes can 00:57 do and you can certainly do that too. 01:00 this time, I'll hit the F3 key to bring up the various range names available in 01:05 this workbook and a range name for this, as you would guess, is State Regions. 01:09 We are looking in the left column of State Regions, comma, and we need to get 01:14 information out of the second column. 01:16 It's the 2, and this must be an exact match, comma, 0 or FALSE, that fourth argument. 01:24 Complete the entry and we see that that was found. 01:27 Once you have indicated which region Colorado is in, it's the MT region, we then 01:31 want to use the additional table that's seen over in columns M through U, and 01:36 this table over here shows different rates, depending on a number of dependents. 01:42 Here are the number of dependents, and over in the left-hand side, this first 01:45 person has five dependents. 01:47 So the result of the VLOOKUP here is an MT. 01:51 We now want to use MT and lookup the appropriate MT rate over here, based on the 01:57 number of dependents. 01:58 So the answer we got from this first VLOOKUP is going to be the item to be 02:02 looked up in an outer VLOOKUP. 02:04 So think of all this as MT. 02:06 That's what it evaluates to. 02:08 Leave that in place and outside of this, or beginning to the left of it, let's 02:12 put in another VLOOKUP, the so- called outer vlookup, left parenthesis. 02:17 Remember, this is equal to MT. 02:19 This is what we are trying to look up, comma, and the table we are using as 02:23 the source for the lookup are these cells right here and the range name 02:28 RegionalTax kicks in. 02:29 That's the name of that table, comma. 02:32 We are trying to lookup information in which column? 02:37 Well, if a person has five dependents, it's the 1, 2, 3, 4, 5, 6th column here 02:44 and rather than putting in 6 we are going to use that dependent, plus 1, and 02:49 because this needs to be an exact match, in other words, we are trying to find 02:53 that MT exactly here in that left column, 02:56 we need to put in an additional argument, comma, and either the word 'FALSE' or 03:02 the number 0 and a closing parenthesis. 03:04 All the parentheses must be typed here, since we have multiple sets. 03:08 So this should give us the answer and if you are looking ahead, you would be 03:11 suspecting in the MT row here in the column that has the 5 in it, a .30 for this 03:16 regional tax, and there it is. 03:20 So a VLOOKUP, inside of a VLOOKUP and, in this case, you can see the power of it 03:24 and how it solves the need pretty quickly and efficiently. Collapse this transcript
Finding table-like information within a function with CHOOSE
Locating data with MATCH
Retrieving information by location with INDEX
 00:01 As you look at the table of information in column C through J here, the orange 00:05 colored cells, these represent prices based on perhaps packages. This 00:10 represents the size of the package. 00:12 Now, a more sophisticated example might have this broken out by weight, but 00:16 let's imagine, based on the size of a package, and where it is to be shipped from 00:21 a particular location, we've got different shipping zones here. 00:25 From the table, we want to be able to pull out information based on size and zone. 00:31 A function called Index allows us to pull out information from a large table, as 00:36 long as we provide the row and column reference. 00:39 So let's imagine a simple example here. In cell D11, we want to see the price of an item 00:45 that's Size 3 to be shipped to Zone 6. =index. 00:50 Index begins with the actual location of the array. 00:55 Now, there is a range name here. 00:56 You will see it pop in the place as I highlight the cells. 00:59 The range name is ShippingCost, comma. 01:02 Throughout Excel, you will find different functions that use row and column, 01:06 always the row comes first, RC, remember that. 01:10 It's not always the case that everything lines up perfectly, but in this 01:14 particular example, it does. 01:15 The Size here is 3, and so we can use this cell right here if we wished. If 01:20 we were doing it manually, we just type 3, but let's use the cell right here that has the size. 01:25 That gives us the row number, comma, and the column number here. 01:30 It's the 6th column. 01:31 That's found right here. That's the Zone. 01:34 So what's it going to cost? 01:35 And looking ahead, you can see where the 3 and the 6 intersect this way, \$35.29, 01:40 and that's the answer. 01:43 So we pulled out information from a table, based on the row and column number. 01:47 Now in columns, L, M, N and O we see a similar need for this. 01:52 We've got a huge list of items and we want to know what the shipping cost is, 01:56 based on the size of the item in the shipping zone. 02:00 So once again, the Index function here, we're looking at this table again. 02:04 So either type it or highlight it, comma, and the row number we need here 02:10 relates to the size. 02:11 It's going to come out of cell M2, comma and the shipping zone, it's going to 02:17 represent the zones here. 02:18 The column number, that's right here. 02:20 And we are all done with that, Enter, and we'll recopy this, and you can see how 02:25 we've used the Index function in these examples to pull information out of a 02:29 table, based on a row and column reference. Collapse this transcript
Using MATCH and INDEX together
5. Power Functions
Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF
 00:01 Whenever you have database-like data that you are managing in an Excel list, 00:04 invariably, you need to tabulate some information, some sub-totals, some grand 00:09 totals, and there are tons of ways to do this in Excel. 00:12 Sometimes a PivotTable is the best solution, sometimes it's a new column of 00:16 data, sometimes you just want a set of statistics, maybe off to the right of your list. 00:20 There are three related functions that might come into play here. 00:24 One of them is brand new in Excel 2007. 00:28 Sometimes, just looking at the data, an idle question comes up. 00:31 For example, there's a list here and quite a few names, see the status of 00:34 people, salary, and someone just says out of the blue, "How many full time 00:39 "people do we have here?" 00:40 So, what we would like to be able to do is simply count the number of times that 00:44 we see that phrase, Full Time, in column B, and column B might have a ton of 00:49 entries or maybe just a few. 00:50 It doesn't make any difference. 00:52 The function we are talking about here, first of all, is called COUNTIF and as 00:56 always, if where we are looking is in a column where there's nothing else, let's 01:00 refer to the entire column, make it simpler. 01:02 We are looking in column B, and what are we looking for here? 01:04 Well, one way to approach this is simply within double quotes to put in the text entry. 01:09 Now sometimes you might be looking for values and you can simply enter the value 01:13 to see how many times that occurs. 01:15 We'll type Full Time. 01:17 By the way, the uppercase here isn't critical, but logically, you would type it 01:20 this way, because you see that's the way it's entered. 01:23 The space is critical, of course. 01:25 Full Time, double quote. That's it. 01:28 How often does Full Time appear in column because? 01:31 And there it is, 162 times. 01:34 Now, if you'd like to do this for the other kinds of statuses that appear in 01:37 column B, the list off to the right, which is prepared ahead of time, we can 01:42 simply convert to that. 01:43 And although it doesn't really have to be in the same row, I'll just drag this 01:46 up here and change this entry here. 01:49 Instead of referring to the text entry, we'll make it refer to the cell right 01:52 here, and we would get a similar answer, and now we can simply drag this down 01:56 three more cells to get our totals for the other. 02:00 And that total is 296, and you'll see that down in the status bar. 02:05 If you were to click on column B, as I'm doing right now, you might not see 02:09 anything in that status bar, but if you were to right-click down there, you 02:12 can certainly control, 02:13 and this might be a reason for having this around all the time, do a count of 02:17 the cells that have entries in them, and you see they are popping up already. 02:21 And as soon as I click outside of this, you'll see 297 entries there. 02:25 Of course, that's counting B1 also, so that accounts for the difference. 02:29 But getting back to the idea of COUNTIF. That's fast, that's easy, and when 02:33 you're referring to a list, perhaps it's clearer. 02:35 It depends upon what your needs are. 02:37 Let me point out a variation on this too. 02:39 Although this isn't a powerful use of it, but I think you can sense how it could be. 02:43 You can use wildcards here. 02:45 Let's show how, in a function, COUNTIF, looking again in column B, we 02:51 want to find all the people who are full-time or half-time, either way. 02:55 So, what they both have in common, of course, is the word 'time'. 02:58 In this situation, we use a double quote and the asterisk. 03:01 That's a commonly used wildcard, which you perhaps have seen before and then the word 'time'. 03:08 Anytime, in column B, where we see the word 'time' preceded by anything, we want to 03:13 know how many times this has occurred. 03:15 It's 198 and, of course, that's the total as we see up here. 03:18 We were to highlight those two cells. 03:19 It's the total of the full -time and the half-time. 03:22 So, be alert to that possibility as well. 03:25 Now, the long time, I used to call it companion to COUNTIF is SUMIF. 03:29 SUMIF begins and starts essentially the same way. 03:34 Let's say, again, we would like to know not only how many full time people we 03:39 have here, but what are their total salaries? 03:41 SUMIF has a third argument. 03:45 It starts off in essentially the same way as COUNTIF. 03:48 We're looking in column B, comma, and I will use the cell reference G1, which has the 03:54 word Full Time in it. 03:56 And every time we find an entry here, that's Full Time, we would like to get the 04:01 Salary and the word sum, of course, suggests we want to be adding them, so we 04:04 are going to get the salaries out of Column C. 04:07 In situations where you are highlighting cells, for example, if we were to 04:11 choose to do this by B2:B300 or something like that, you would want the C 04:16 reference to match that exactly to avoid problems. 04:20 If this says B2 to B300, then over here at Column C, you would indicate C2:C300. 04:27 So, how much are we paying these full-time people? 04:29 A quick fix here, there. 04:32 It's 8,847,265 and based on that, of course, you'd imagine wanting to know 04:37 Half-Time, Contract, Hourly. 04:39 We'll just drag this down here, and we see the total there, 15.09 million. 04:43 Click on column C. That's the same total, and we are seeing that in the 04:46 Status bar as well. 04:47 So, that's a cross verification, you might say. 04:50 Now in all prior versions of Excel, if you wanted to get an average salary, you would 04:55 just take an entry, like this, and then divide it by the count. 04:59 So, we possibly could put the two together. 05:01 That wouldn't be that outrageous or difficult. 05:02 We could take this and then divide it by the COUNTIF that we used up above here. 05:07 But there is a new variation, a new function in Excel 2007, called AVERAGEIF. Left parenthesis. 05:15 What are we looking here? 05:16 Like before, we are looking in column B, comma. We're looking for the Full Time people. 05:21 We'll just pick up the information from G1 like before, comma and we're looking 05:26 at the salaries in column C. What's the average salary of the Full Time people? And there it is. 05:33 And we can quickly verify this too by writing a simple formula here, equals, 05:37 whatever we got earlier with the SUMIF for the Full Time people, divided by the 05:42 COUNTIF of the Full Time people, and we have the same information. 05:46 Obviously a rounding difference on the display, but it is the same information. 05:50 So AVERAGEIF, which is a new function in Excel 2007, will simply make this 05:55 process simpler and more direct. 05:57 And as in the previous situation, if we wanted to get the average for 06:00 Half-Time, Contract, and Hourly, just drag this down three more cells and we 06:04 have those as well. 06:06 So SUMIF, COUNTIF, and AVERAGEIF, great features for tabulating information 06:12 from a list of data. Collapse this transcript
Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS
6. Statistical Functions
Finding the middle value with MEDIAN
Ranking data without sorting with RANK
 00:01 In dealing with large sets of data or a database-type list, one of the things we 00:04 all need at different times, we don't need a function for it, 00:07 is simply to reorder the list. 00:09 It might be valuable to look at this list with the salaries from high to low. 00:14 We want to rank them that way, or in the reverse order, possibly. 00:17 But at the same time, we might also want to carry with us, along with the data at 00:22 any time, that is, a ranking, and it will change based on the adjustment of some 00:27 of the salaries or the elimination of some of the records here. 00:30 We'd like to have at our fingertips at all times, 00:32 A ranking of the salaries here, and have it in a separate column. 00:35 I have set aside two columns here and will show you how this might work in 00:40 column C, if you want this to be a dynamic list and always know the ranking. 00:45 We do need a function here, and sure enough, it's called Rank. 00:48 It's pretty simple and straightforward. 00:51 We are trying to compare this value here with all the other entries in column 00:55 B. If column B has nothing else in it, simply comma, using the entire column reference. 01:01 That's it for the first one. 01:02 That's the 74th highest salary. This is top down. 01:07 The number one salary, the highest salary will have the value of 1. 01:10 So we'll copy this down a column by double-clicking the Fill Handle and 01:15 here's the first salary. 01:16 And right above this, notice something, and it's coincidence that these 01:20 happened to be adjacent. 01:21 But it's not coincidence, perhaps that they are the same. 01:24 Because that's going to happen from time-to-time and the concern sometimes is 01:28 what does ranking do? 01:30 Well, these two entries share the second ranking, they are the second highest. 01:36 And Rank ignores, in this case, a third rank and the next entry that you would 01:41 find in the list, numerically is not level 3 or third highest, but the fourth. 01:48 And, of course, the way to explain this is this is the fourth highest entry. 01:51 That's true, this is the fourth highest entry because up here we've got the 01:55 first highest and these share second and third, but we see the number 2 there. 02:00 If you really needed to have this displayed in such a way that you've got 1, 02:03 two 2s and then you want that number 4 really to be 3, then you will have to 02:07 explore that using array formula which were much, much more complicated than 02:12 what we are using here. 02:13 So Rank does the job and I think most people would be happy with the way it does it. 02:18 Now based on certain kinds of data, not necessarily salary data, but sometimes 02:22 it makes more sense or your need is based on saying well, for example, I want 02:27 the lowest sorry to be 1 and then the highest salary to be 99. 02:31 Sometimes you want a reverse order here. 02:33 And there is no X rank or D rank or some other way to say this. 02:37 We simply have to put together some functions here. 02:40 And what we need to know is the total count of salaries, or, expressed in a 02:45 different way, we begin with the idea we want to know how many entries in 02:49 column B have values. 02:51 In other words, we do not want to count B1. 02:53 The function we are talking about here is Count, and its purpose simply is to 02:58 tell us how many cells in this particular range, namely column B, have values? 03:04 There are 99 of them. 03:06 So they are actually 100 rows of data here. 03:08 Row 1 has the labels and all the other rows have values. 03:13 So they are 99 entries here and what we want to do with that value is subtract 03:18 from it the ranking. 03:19 Now we could possibly use the data in column C, but let's do this as is if we wanted 03:23 this completely separate in its own column. 03:26 We want to repeat that same calculation for rank. 03:29 How does this salary, comma, compare with all the others in column B? and because we 03:35 don't want a zero in one of our answers, and we would get that here, 03:38 we need to add one. 03:40 So this first person's ranking now is 26, and as we copy this down the column by 03:45 double-clicking the Fill Handle, we see the entries this way. 03:48 So the entry here that is first is now the 99th and somewhere in this list we'll 03:54 see an entry of this 1st in our new way of calculation. 03:57 Here it is, and previously this had been 99. 04:00 So that's how to do a reverse order here. 04:02 Now there are other times, too, when maybe these rankings are more interesting 04:06 when you're dealing with a smaller group of data. 04:09 Again, in a larger context, if this is near other information, we might want to 04:13 do a ranking here and a little shortcut that will come in handy from 04:17 time to time, which you can use in a lot of functions and formulas, 04:20 Why not just highlight the data all at once here? =rank. 04:24 We want to compare this entry here, comma, with all the others in column I. 04:29 Simply hit Ctrl+Enter here and we've got our ranking there, and here too, 04:34 you'll see some repeats. 04:35 This is the fourth highest, but this one too is also fourth highest, so there is 04:39 no fifth entry in there. 04:41 So similarly used, although with a much smaller group of data, the Rank function 04:44 certainly has its role to play in showing us rankings without necessarily 04:48 forcing us to sort the data. Collapse this transcript
Finding the magnitude data with LARGE and SMALL
 00:00 A common function known by many Excel users is simply to find the highest value 00:05 from a list of entries. 00:07 And in cell I2, we see the Max function meaning Maximum being used, the highest 00:12 salary found in column F is simply this value here, straightforward. 00:18 I remember, for years, not necessarily needing but then suddenly needing a way to 00:22 calculate the second-highest entry in a list and also third and I ended up 00:27 sorting the data and that was okay for the moment, because I just simply wanted 00:31 to see the information, but certainly there are times and there have been times 00:34 in my situations too where I really need to tabulate the information. 00:38 I want to know the second-highest salary or the third, based on any ranking that I might need. 00:44 The function is called Large. 00:47 Pretty easy, pretty straightforward, where are we looking here? 00:49 For example, in column F, Salary, comma. Want the second-highest salary? Put in a 2. 00:56 It's as straightforward as that. 00:58 If you wanted to build a list of these like, I've already got the highest 01:01 here, and it's going to put in some numbers over here, but 2, 3, 4, 5 and we 01:06 could work off of that and create a little list of, if we wanted to see the 01:10 five highest salaries. 01:12 So instead of this being a 2, just make it referred to a cell that has that 01:15 value in it, and recopy these by double- clicking and we have all these entries 01:19 and a quick format, it looks pretty good, and there we go. 01:22 Easy to use, the Large function. Just like that. 01:26 Rather than trying to come up with some obscure way of taking the original value 01:30 and subtracting from our total and so on, there is a Companion function, and sure 01:34 enough, you guessed it. 01:35 It's called Small and works, of course, from the bottom up. 01:38 Looking at all the entries here, what's the second smallest? 01:42 If we really want to find the smallest, of course, we could use the MIN function. 01:46 But we may want to know the second smallest, third smallest, etcetera. 01:49 Second smallest is 8,904. 01:50 That's one of our part-time employees here. 01:54 So quick and easy to use, the Small function, the Companion to Large. 01:58 One of those functions that you don't necessarily need, but when you do need 02:02 the capability it's there and it's a reminder that there are so many different 02:05 functions in Excel you just might not have had the need for until one day, and there it is. Collapse this transcript
Tabulating blank cells with COUNTBLANK
 00:00 In looking at the information you are seeing on the screen here, 00:03 in column G, you see that some people have benefits, the D presumably for 00:06 Dental, M for Medical, R for Retirement. 00:09 Not everybody in this list has a benefits package. 00:12 We might want to know, who doesn't, or perhaps, from a different perspective, 00:17 how many people do not. 00:19 In other words, what we'd really like to do is come up with the way to count the 00:22 entries in column G that contain spaces. 00:24 A COUNTIF comes to mind as one possibility. 00:28 But there is a function that gets directly to the point and it's called 00:31 COUNTBLANK, and this example here, let's just highlight the cells we are looking 00:36 at. It would be in column G here, just highlight these cells. 00:39 Now this is a larger list. 00:41 This is more of a drag perhaps, but nevertheless, not too big here. 00:44 How many blanks are found there? 00:46 And that will tell us. How many people do not have benefits? It's 28. 00:50 That's the number of blanks we found in column G. And if you are using that with 00:56 a larger list, be prepared either to drag or possibly to type in the entry. 01:00 If you refer the entire column, you will be coming up with a value like this, or 01:04 near something like this. 01:06 This is the total number of rows in Excel 2007 and so a simple COUNTBLANK here will 01:12 reveal and this might be trivia, but on the other hand just to point this out, 01:15 if we do this for the entire column G, that number is based on this minus the 01:21 ones where there are entries. 01:23 So the other question that might come out of this is, how many people do have benefits? 01:27 And so here what we'd use is equals and we'd probably start off with just a simple 01:31 idea of how many entries in column G do have any data at all, with count A? And 01:39 this will tell us how many actual entries there are in column G, and one of 01:42 those is G1, so we'd be mentally subtracting 1 there. 01:46 So there are 71 people who do have benefits here. 01:49 You could approach to that in a different way with COUNTBLANK function, but I 01:52 think this is probably the better way to do it here, simply subtract 1. 01:56 Now, another technique here, too. If you'd like to fill in the blanks here, 02:00 you can't click column G, and not so well-known is that on the Home tab, in the 02:05 Editing group, on the extreme right side, under Find and Select, there is a Go To 02:09 Special and you could click that button and choose just the blanks here. 02:14 So all the blank cells are highlighted, and not the ones below the data, but 02:18 just within that solid cluster of data here. 02:20 You might just want to put in here N/A possibly, or any word that might be appropriate. 02:26 If you do put in N/A, it might get lost in the mix, so you might put in the 02:29 space, put in some hyphens or something. Do that in a way that makes it stand 02:32 out, and just type Ctrl+Enter to complete the entry and those will all be 02:37 filled in with NAs. 02:38 That's a thought, not necessarily related to what we are doing with COUNTBLANK 02:41 but that might have some merit, in its own right. 02:44 Now as we look at the information over here, here is another example, and 02:48 just to point out that we are not always looking in a single column or in a single row. 02:52 How many blanks are here? 02:53 You can see pretty clearly, there are three. 02:55 But just point out the idea that COUNTBLANK certainly has its uses here and 02:59 there to give us a quick count of how many cells, here, contain nothing in 03:04 this group right here? 03:06 And here the answer is going to be 3. 03:08 So a quick, easy-to-use function which has its uses here and there, as you are 03:12 trying to tabulate information from various sets of data. Collapse this transcript
7. Date Functions
Understanding Excel date/time capabilities in formulas
Identifying the day of the week with WEEKDAY
Counting working days with NETWORKDAYS
Determining a completion date with WORKDAY
 00:00 If you are dealing with data entries, and trying to calculate into the future 00:04 how long a project might take, for example, the information in columns C and D 00:09 is setup automatically. 00:11 Notice that I have put in the starting date in the Project Length three times 00:15 here to illustrate a difference. 00:16 A simple use of the method to add dates and times allows us to simply say equals 00:23 this value, plus this 60 days from June 11th is this. 00:28 Now unless your environment is a 24x7 kind of operation, this would mean the 00:33 project beginning on June 11th working day by day by day takes 60 days, it would 00:37 be finished on August 10th. 00:40 But many, many operations don't work Saturdays and Sundays, so we don't want to include those. 00:45 So for comparison here let's use a function called WORKDAY. 00:49 The WORKDAY function begins with a starting date at June 11th, comma. And how many days? 00:57 We will be working 60 days. 01:00 We do not want to count Saturdays and Sundays. 01:02 And the optional holidays we'll show you in just a second. 01:05 So when is this completion date now as we look at it? 01:08 Now it's quite a bit later. 01:10 It's on September 3rd, but there are potentially some holidays in the next year. 01:16 And having a list of holidays nearby will make a difference. In this case, 01:20 we got a couple of dates that July 4th weekend. Maybe not a huge difference. 01:24 Let's do the same sort of thing here. 01:26 Copy this and adjust it by putting in holidays. 01:30 Now you can certainly include a list that goes well beyond the holidays we need, 01:35 but let's say we might be using this list from time to time with other dates in 01:38 the same column here. 01:40 So we'll highlight them all, and make that be an Absolute Reference and 01:45 a different ending date than we saw previously, and then the other examples and of 01:48 course the difference here is that the July 3rd which was a Friday is not 01:52 included, July 4th is the Saturday, it wasn't included anyway, and I am going to 01:56 quick double-click here gives us some other completion dates. 02:00 In all cases here the function is taking into account, not the Saturdays and 02:05 Sundays and not the holidays that are part of that list that we see over in 02:10 column A. So easy to use just sort of variation on the other kinds of DATE 02:14 functions, but again it gives us that quick ability to tabulate information and 02:19 not include Saturdays and Sundays. Collapse this transcript
Tabulating date/time differences with DATEDIF
8. Math Functions
Working with rounding functions
Finding the remainder with MOD
Building random number generators with RAND and RANDBETWEEN
 00:00 The ability to create random numbers might seem like an obscure need for 00:04 many people using Excel. 00:06 It's a mathematical function and it's a great mathematical tool. 00:10 And the word random has different meanings for different people, but let's give 00:13 you two examples of how you might want to use this particular capability. 00:17 We're talking about two functions here. 00:19 The first one is RAND, =rand and its purpose simply in an isolated case is to 00:27 create a random number between 0 and 1. 00:29 I am just going to put it out here in the middle of nowhere for the moment, =rand. 00:32 It's one of these functions that has parentheses but nothing between the 00:36 parentheses, just type it like that, Enter, and depending on how many decimals 00:41 you've got displayed here, it's a random number between 0 and 1, and so that's 00:46 not really giving us much rationale for using it. 00:49 Now, here is a list of data. It's a database. 00:52 It currently looks like it's sorted by department. 00:54 There are times when you've got a list. 00:56 Now it doesn't necessarily have to be an HR list like this might be. 00:59 It could be a list of items. 01:01 It could be a list of sales. 01:03 Sometimes what you want to do is sort of a primitive version of quality control. 01:08 You simply want to pull out some of these rows at random to review them, to see 01:12 if the information has been processed, and it could be almost any kind of data. 01:16 So how might you randomize this list? 01:19 Well, we've got an empty column to the right here. 01:21 Simply put in =rand here, left parenthesis, Enter there. 01:26 You probably want to display at least something so you know that's not wrong there. 01:29 So we could show a few decimals just to see what it is. 01:32 Double-click to copy this down the column and use this as a vehicle for sorting 01:37 the data, and to make it fall into place pretty quick here, give it a temporary title, 01:41 practically anything. 01:42 Just qq or whatever. That's okay. 01:45 By the way, you may have noticed there. 01:46 The numbers have all changed. 01:48 This is one of those functions that adjusts every time you make a worksheet change. 01:52 Now I could click over here and type in a number and hit Enter and they all change again. 01:56 We're not going to let that bother us. Just leave this here. 01:59 But if we use this as our sorting vehicle and the fastest way to do this, 02:04 since it is adjacent to all of this data and this is not associated with the data to the right. 02:08 This is an empty column. 02:10 We could simply click the Data tab, hit the AZ or ZA button and it sorts 02:16 the entire list and now as you look at this list, it is in no apparent order whatsoever. 02:22 And in fact, the numbers that it was based on that were here got all changed 02:27 again, and anytime you make a worksheet change, these change. 02:31 Now we don't care about these numbers anymore. 02:33 This list is in random order, and so we could throw away the numbers. 02:38 Maybe if we needed to do a spot check of 10 of these, we'll just pick the 02:42 first 10 and go at it. 02:44 Review those and no others. 02:46 Another approach to this could be something a little bit different. 02:48 We might just type in an entry here, =rand(). 02:55 If for example, we wanted to do 10% of these, how about =rand()<10%? 03:02 Type that and we're going to get true or false. 03:04 Double-click to copy this down the column and 10% of these are true roughly. 03:09 The others are false. 03:10 So we could just freeze those if we wanted to, and then sort that way. 03:14 That's a little more obscure and probably not as likely to be used, but 03:17 again, another use of it. 03:19 So eventually or at least, all of these are in random order. 03:21 You'll decide what to do with them, but eventually, you would get rid of this column. 03:27 A companion function of this, which I find useful because I prepare a lot of 03:31 sample data and am forever adjusting it. 03:34 In a real business-like environment, you're probably not going to use in the way that I 03:37 might use it here, but for purposes of a certain example, I need some random 03:41 numbers in here, and not numbers between 0 and 1, but numbers that are relevant 03:47 to the context in question. 03:49 And so I'm tabulating. It might be sales. 03:52 It might be well ratings. 03:54 It could be anything, but the values here typically are between, 03:57 for example, 100 and 700. 04:00 So here's a function called RANDBETWEEN. 04:03 So RANDBETWEEN, and this deals with whole numbers. 04:08 RANDBETWEEN, a lower value first, a higher value next, and since these are all 04:16 highlighted ahead time, I'll just hit Ctrl+Enter and we'll adjust the 04:21 formatting kind of quickly on the Home tab here. 04:23 Just hit Comma, and don't show those decimals, there we go. 04:27 We have got some whole numbers, and they too will change every time you make 04:31 a worksheet change. 04:32 Here and there with smaller sets of data, I've had situations where I didn't 04:35 like the numbers, and so you can quickly regenerate them. 04:38 I doubt if you need that here. 04:39 Just by hitting F9, it will do a recalculation, and they all change. 04:43 Some people could figure out a way to pick the lotto numbers maybe and don't like them 04:47 and they'll change them. 04:48 But if you did want these to freeze at some point, you can simply with the 04:52 right-mouse button, drag these into another set of cells and immediately right 04:56 back on top of the current ones, like when Copy Here as Values Only. 05:00 So here and there, creating quick random data for samples, that's easy. 05:05 RANDBETWEEN, and if you wanted to have a series of dates here, for the same idea, 05:12 between those two dates, that's 2010 and 2011. 05:16 That's two years of data there. 05:17 Here we could do simply =randbetween, a starting date, an ending date, and we do 05:27 want these to be absolute here, so we'll hit F4. 05:32 We've got a bunch of random dates, and for the moment, those too will change, 05:35 and so if you want to freeze them, the same idea. 05:38 There are obviously some uses here well beyond the mathematical concept of a 05:42 random number that might have some value, as you work with data, and either need 05:47 to sort data in a random way or possibly create some sample data quickly. Collapse this transcript
Converting a value between measurement systems with CONVERT
 00:00 If you deal with scientific data in Excel, you should probably know about 00:04 the CONVERT function. 00:06 Now some of the applications that we are going to be using there are quite common. 00:09 Even if you're not in the scientific arena, I think they are going to 00:12 be helpful for you. 00:13 And in Column G and H, you'll see a few of these. 00:16 If you check out the Excel Help System, you see quite a few others as well. 00:20 The categories I have shown here Temperature, Liquid, Measure, etcetera are more 00:24 commonly used but you will also see a set of conversion issues related to power, 00:28 pressure, force, energy and magnetism, but let's just give you a couple of 00:33 examples of how this might work. 00:34 You want to convert kilometers to miles, so in cell B2, we'd use the CONVERT function. 00:39 Many times when you make a mistake with the issues here, it has to do with 00:42 upper and lower case. 00:44 So the titles as you see them here, and I wouldn't normally setup titles this way 00:48 but I am in this case to extenuate this idea. 00:51 Here's the value of 180. It refers to kilometers. Comma. 00:56 We are converting from what? "km", and we are trying to convert to miles "mi". 01:07 So 180 kilometers is 111.8 miles. 01:11 And certainly from time to time, you would want to do the opposite, so in 01:14 the case here a CONVERT from these miles into how many kilometers, same general idea. 01:20 And here too having seen the first example, you would know what to do. 01:24 That's the unit of measure, and it's in miles "mi,". 01:27 We want to convert to kilometers "km," we are all set. There we go. 01:33 143.2 kilometers is the equivalent of 89 miles. And then Celsius and Fahrenheit. 01:43 And here I have capitalized the two words to extenuate the idea that we will be 01:47 using the letters upper case C and upper case F. Aame general idea here as we 01:52 convert, and in this case this is the temperature, "C", "F." 98.6 is the 02:07 Fahrenheit equivalent to 37 degrees Celsius and looking the reverse way here is 02:11 the Fahrenheit of 90, what is that in Celsius? Same general idea, =convert. 02:19 The value in question here is 90. So =convert. 02:24 This time I can put it in the cell. 02:25 You can of course do this with real values this way and this time I will be typing. 02:28 I am just going to click on it, E3 comma, moving from Fahrenheit in this case to Celsius. 02:37 And one more. The same general idea and here too I have not used capitalization 02:41 to extenuate the idea, 100 meters, how many feet is that? 02:44 We are converting from meters "m", into feet, and this time ft. 02:54 How many feet is that? 02:57 Here we go. 328 feet. 03:00 And of course this is a mile as we know it. 5280, how many meters in a mile, or 03:05 how many meters in this many feet? 03:09 And that's in cell E8 and we are moving in this case "ft", "m". 03:20 So 1609.3 feet in a meter. You get the general work idea. It works smoothly. 03:25 There are tons of options. The ones being displayed over in columns G and H are 03:29 perhaps a third to less than a half of the possibilities and you want to 03:33 explore that a bit more. 03:34 It also allows for various prefixes and we actually use them here for example, 03:39 the K in front of M is kilometers and so on. 03:42 There is a complete list of prefixes you can use as well. 03:45 So anytime you got data that's in varying measurement indices, you might want to 03:51 if necessary convert them into another measure by using some of the techniques 03:55 and options available with the CONVERT function. Collapse this transcript
9. Array Formulas and Functions
Extending formula capabilities with Arrays
Counting unique entries in a range with an Array formula
Determining frequency distributions with FREQUENCY
 00:00 Creating a frequency distribution in Excel is something many an Excel user needs, 00:05 and it does involve an array function, not an array formula that you 00:11 might need to create on your own, but an array function. 00:14 And the function called Frequency is an array function. 00:18 Even if you're not a statistician, you're probably familiar with the idea. 00:21 A list of entries in this worksheet, about 620 or so, by Social Security and 00:26 salary, of how many people in this organization fall into the various 00:30 categories based on an arbitrary list that you might set up anywhere. 00:36 A common approach might be something like let's just put down the salary 00:39 breakout here, 10,000, 20,000 and so on. 00:43 So the number we would be seeing here would be the number of salaries up to 00:47 and including 10,000. 00:49 And then here we'll see all the salaries above 10,000 up to and including 20,000 00:53 and so on and so on and so on. 00:55 We just want a rough count of how many people fall into each of the categories here. 01:00 The initial process for creating a frequency distribution is to set up a list 01:05 usually in a column anywhere you want. 01:08 This is referred to as a Bins list. 01:11 Now, the salaries in question are over here. 01:13 But we also want to setup a set of cells, a range where we are going to put the 01:18 answer and here we are going to type =frequency. 01:22 That's the function name. Left parentheses. 01:27 Data array, which data are we talking about here? 01:29 It's all the data from here downward. 01:31 Wile you are doing this by the way, 01:33 you can hit Shift+Ctrl+Down arrow to highlight those cells. 01:37 There we go, and you see what's happened in the Formula Car. 01:40 You might want to scroll back again. Comma. 01:43 Now what? The bins array. 01:45 So scrolling back up top here, we are going to highlight these cells and that's 01:50 all we need, but this is an array function, so we'll press Ctrl+Shift+Enter. 01:56 There's our answer right there. 02:00 Then you discover as you look at this cell and this cell, they appear to have 02:04 exactly the same function in each one and they do and yet we're getting 02:09 different answers and that's a little bit unsettling at first. 02:12 But these answers are correct and a quick search of the data here, a quick 02:17 sorting would actually make some sense out of it. 02:18 For example, just click here and on the Data tab just hit AZ here. 02:23 Well, sure enough, what have we got here? 02:25 4 salaries here, up to an equal 10,000, there they are and we could to the same 02:29 thing with the next range, this cluster right in here... 02:32 We're going to have about 32 of those. There we go. 02:33 You see the number 32? 02:35 So you have got 32 of those and so on and so on and so on. 02:38 Sometimes what you want to do with this is turn this into a chart real quickly. 02:43 It'll work most efficiently if you display these as text, so a real fast entry here, 02:47 if simply using the text version of those numbers. 02:50 It's just a faster way to do some of things we need to do from time to time. 02:54 This is not an array formula here. 02:56 It's just a simple conversion of this information here into a text layout that 03:01 matches a format for example,like this and there are a quite few variations, on 03:04 which you might try with this particular format. Okay. 03:09 If we were to highlight this in Excel 2007, just like Alt+F1 and we'll have a 03:13 nice chart to go along with our data as well. 03:16 Probably we want to clean up the bottom of that, take out the legend and so on, 03:19 but a frequency distribution often seems to be accompanied by a chart. 03:23 It just makes things easier to see and that mirrors the data that we are 03:26 seeing there in column F. 03:28 So Frequency is a function, but it's an array function and once again, clicking 03:32 back on one of the cells here, you look in the Formula Bar, you see the braces, 03:36 you do press Ctrl+Shift+ Enter to complete the entry. Collapse this transcript
Flipping row/column orientation with TRANSPOSE
 00:00 In Excel, there's a great feature called Transposing. 00:03 And I find myself using it many times when I am laying out data. 00:07 I will often pause and consider whether I should have put data, like we are 00:10 seeing on the screen here in a vertical alignment with the months down the 00:14 left-hand side of the screen and Sales, Expenses, Profits across the top. 00:18 And so looking,at this data, what I would like to do is transpose it and 00:21 it's somewhat analogous to things you might be familiar with when using a PivotTable. 00:25 But if you could imagine a diagonal line along here, maybe transpose this 00:29 without destroying it and not using a function just yet, but simply copying 00:33 this data and then, right below it for contrast, right-click here, Paste 00:38 Special and Transpose. 00:41 Not only is it the same data, but after adjusting the column widths here, 00:44 we'll see that the formulas themselves got transposed. 00:47 So a formula right here, which is adding up 6 cells to it's left, does get 00:52 transposed into a formula here that's adding up the 6 cells from above. 00:57 And usually in situations like this and sometimes you'll be doing this with 01:00 only a single row or a single column, you make your decision as to which one 01:04 looks better and get rid of one of them and keep the other one and proceed. 01:09 There can be other times when what you would like to do maybe a set up 01:13 something like this and maybe on another worksheet, and have the two sets 01:18 of values be in sync. 01:19 In other words you might want to have this be kind of a mirror image in a 01:23 different worksheet and have it be actually linked to the data. 01:27 When you do a simple copy and transpose, a change up here causes nothing down here 01:33 and as I change this to 150, this set of data in the upper part of the 01:37 screen here is changing but not in the lower part. 01:40 So, if you want them to be in sync and you need that kind of a situation. 01:44 What you need to set up is what's called the Transpose function and it is an array function. 01:52 So getting rid of the data here, and doing it on the same worksheet, so you can 01:56 see the example better. 01:58 Let's setup a transposed area and here's what we have to do first. 02:02 If you drag across starting area here, as you do this, you recognize and you'll 02:07 see to the left of the Formula Bar in the Name Box, the indicator 4R x 9C, 02:13 meaning 4 rows by 9 columns. 02:16 Now the transposed version of that will be 9 rows by 4 columns. 02:20 So what we now need to do is go highlight a range that is the reverse of this. 02:25 So it's going to be 9 rows by 4 columns and as you do that you can see the display there. 02:32 So highlight that area ahead of time, =transpose, using this data, [00:02:3963] Ctrl+Shift+Enter. 02:42 Obviously, we would want to do some formatting before we do much else. 02:47 So we will ignore that for the moment. 02:48 But every one of these is an array function and here too, looking a little bit 02:54 strange because they all look alike. 02:56 But in this example here, if I change this to 150 and remember I did that in the 03:01 prior situation, it didn't change the lower data. Here it does. 03:04 Now, probably you would not be doing this on the same worksheet and 03:08 you certainly can and I certainly did it here. 03:11 But you might want to consider how this might work for you when you need 03:14 parallel kinds of views perhaps on different worksheets. 03:18 The data here is in sync and forever linked to the other data. 03:22 So it's a powerful tool. 03:24 It's a Transpose function, which is an array function. Collapse this transcript
Building analysis via regression techniques with TREND and GROWTH
 00:00 On your screen, you are seeing some monthly sales data in column B. 00:05 Columns C and D have been labeled, but there's no data there yet, and the chart to 00:09 the right currently is displaying information from column B. Now I have got 00:13 the chart setup in such way that as soon as data does go into columns C and D, 00:18 we are going to see these on the chart as well in the form of different kinds of lines. 00:23 You may or may not be familiar with regression analysis. 00:25 I had a cursory knowledge of it. I am not sure if I can explain it the way I need to. 00:30 But let's imagine the following situation. 00:33 To provide the underpinnings for what might be a look ahead at the sales trend here, 00:38 I am using trend there in a common sense, where are we headed with growth 00:43 in this particular environment here? 00:45 In order to create an analysis of this line, you can use charting techniques. 00:50 But there's also a function called a trend, an array functions that will take 00:55 advantage of the values found here, and then in effect create a straight line 01:01 set of values here that are a reflection of the data that's in column B. 01:06 As with the Frequency function and the Transpose function, we highlight the 01:10 cells that are going to get the results first. 01:13 We highlight all of them. What we are about to create here is a straight line, 01:18 regression line, based on the data in column B. =trend(. 01:26 Now this function has more possibilities, more capabilities I should say, 01:30 than what I am using here. 01:31 Here is a somewhat simple and straightforward use. 01:34 We simply want to be analyzing the data here. 01:37 We are not bringing out the r-squared factor in it or anything like that, if you 01:41 are familiar with regression analysis. 01:42 We are simply going to be creating a new line in column C or a new set of data. 01:47 It will be reflected in chart. 01:49 There is the function. Ctrl+Shift+Enter and there's that straight line regression. 01:57 Similarly, the word Growth represents another function and this will perform an 02:02 exponential analysis of the data in column B. Same general approach. 02:07 Highlight the information here, =growth(, highlight the column B data again. 02:15 Ctrl+Shift+Enter. 02:17 Now anybody who has worked with regression analysis know there's a lot more 02:23 to it than the simple example we are seeing here, and yet this is a quick and 02:27 efficient way of creating those additional lines that will lead us into a better 02:31 explanation of what has happened, and what is likely to happen, and where this 02:35 organization is headed in terms of sales. Collapse this transcript
Combining multiple functions in arrays
 00:00 If you're familiar with the VLOOKUP, MATCH, and INDEX functions, you know how 00:05 powerful they are, and yet there will be times when they just don't seem to work 00:09 properly, unless you have some new techniques available. 00:13 As we look at the information in column G, we see the name of a person here and 00:19 imagine this data here possibly is on a different worksheet, it's in a 00:23 different workbook, and we are trying to find the information regarding the 00:28 sales for this person. 00:30 Now over in columns A, B and C, we see that information, but it's displayed 00:34 somewhat differently. 00:35 Here's the name Teason Anderson right here. 00:37 We see the sales value of the 59,597. 00:42 So what we would like to be able to do here and probably a VLOOKUP is to say 00:46 take this value, and go find it over here. 00:49 The problem is that VLOOKUP looks in the left-hand column of the range that we 00:54 are selecting, and we could somehow figure out way to pull out Anderson, but in 00:59 a short list here we don't have any repeats, but you can bet in a longer list 01:03 you're going to have some last names that are going to be identical. 01:06 So what we are really looking for here is that combination of names. 01:11 As we see these two here. And if we start to use a VLOOKUP immediately, even if 01:16 you're familiar various concatenation techniques, and other ideas, 01:19 other possibilities in Excel, in effect you're trying to say let's take this value and locate it. 01:25 And then we start to think, well I guess we could put these together, couldn't we? 01:29 But we need to find information in the left-hand column that would be here, and 01:34 then we've got to go into column 3 to get the data. 01:37 So if you start to kick this around, you'll realize this is not going to work 01:41 the way we see this now. 01:43 So instead we might start with a MATCH function, and here too we are going to 01:47 run into some difficulty. 01:48 Now the MATCH function sometimes is used simply to tell us if information is found, 01:54 and let's start off with that thought in mind. 01:57 If Teason Anderson here is found over in column A, B of that other list, which you 02:01 remember might be in another worksheet, 02:04 match(G2, but let's pull together the information from columns A and B. 02:11 The last names are found in column A. So let's take all these at once, and we 02:15 are going to be using an array to pull these together. 02:18 We'd like to take not just A2 and B2, and then A3 and B3, but all of column A, 02:26 as well as all the data in column B. But to make these entries match up, we need 02:31 to also put in here, by way of a concatenation symbol, we need to put in double quote 02:37 comma space double quote. 02:40 In other words we need to take the last names as they appear in column A, put in 02:44 a comma space, one more And there, and the information from column B. 02:52 And we must find an exact match. 02:54 So the third argument in the match function is 0. 02:59 Second argument here of course is quite large. 03:01 We are looking here in column A along with a comma space and column B. [00:03:0 7.83] And youcan't deal with cells like this without making this an array formula. 03:12 So we must hit Ctrl+Shift+Enter and if found, this tells us where within 03:18 the array it was found. 03:20 It was found in the 11th position. 03:22 Well that's actually the 12th row, because we're looking at data starting right here. 03:26 So we could've changed this. 03:28 We could start it at A1. I will do it anyway to show how that might play out 03:31 a little bit, and B1. 03:34 This time Ctrl+Shift+Enter, so at least it matches up the row number. 03:38 It's a little easier to verify. 03:40 So that tells the row number, then we need to use that row number as the second 03:45 argument in an INDEX functions. 03:48 INDEX simply says we're looking at a table of data. 03:52 It might be only a single column, like this data right here, comma. 03:57 This tells us the row number and since it's only a single column, we don't 04:01 need a column number. 04:02 We do need a right parenthesis. 04:04 It's still an array formula. Ctrl+Shift+Enter, and there is that answer 04:10 that we're looking for. 04:11 There it is right there, Teason Anderson, 59,597 and before copying this, 04:17 we'd want to make sure that we've got absolute addresses. 04:19 So the formula is going to look even more complex, as we throw in those dollar 04:23 signs with the F4 key and we'll need to do that here also and here, and again 04:29 it just makes the complexity of the formula look greater. 04:33 It doesn't necessarily. Ctrl+Shift+ Enter, copy these down the column. 04:38 Now we have the answers for all persons here except there are two situations 04:42 where we didn't even find the name. 04:44 Now had we done the MATCH function first for all of these that would have popped 04:48 out immediately, but Renee Hood as we see here, and we find it over here in the list, 04:53 but look at the way her first name is spelled. 04:55 That's an obvious difference and with Yvonne Randell right here, that's Yvonne, 05:01 this is Yvone and of course that's going to happen from time to time. 05:06 But as we look at this formula again, the combination of INDEX and MATCH, 05:11 it's an array formula. 05:12 Now remember you are not seeing those braces until you hit Ctrl+Shift+Enter, 05:16 but incredible power here. 05:17 There is just no other easy way to do this. 05:20 Although this might not seem easy the first two times around, remember you do a 05:24 lot of work to come up with the answer once and then when you copy down the column, 05:28 you've taken care of thousands of cells potentially. 05:30 There is no question this is one of Excel's greatest power tools, an array 05:35 formula and often used with other functions together. Collapse this transcript
10. Text Functions
Locating and extracting data with FIND and MID
 00:00 One category of Excel functions that doesn't always get its due and yet it 00:05 consists of a variety of really powerful functions is a category called text functions. 00:11 On the Formulas tab in the Ribbon, you will see a list of them here and two of 00:17 the more prominent ones, although when you first see some of these you are not 00:20 quite sure how you might use it, involve the word were FIND, which almost 00:25 defines itself and the word Mid. 00:27 Let's take a look at how we might use some of these. 00:30 Small situations, we have a small example. 00:33 The Part numbers being used in this organization are based on the timeline or 00:37 concept that the character position of these Part numbers has some meaning. 00:42 And in a variety of different ways you will here and there hear about a 00:45 descriptive number where maybe the third character, the third and fourth 00:49 characters together represent either the size of the item, the cost of it, where 00:53 it might have been made, the year, the color, all those kinds of things. 00:58 And for whatever reason it's important in this particular example maybe to see 01:02 if the letter G is in here. 01:04 Now maybe it's context, particular position, maybe not. 01:07 We just want to know if that letter exists. 01:10 Now we are really talking about another feature that here and there might be 01:14 what you need and that would be the feature off of the Home tab, the extreme 01:19 right group called Editing, Find & Select. 01:22 So, sometimes you might just want to find and see if the letter G is in here 01:27 and you don't want to match the entire cell contents if you are trying to look inside of it. 01:31 Maybe we are just looking here and we could find them one by one or maybe 01:35 just go to Find All. 01:37 And we will get a little list here and you will see that it was found and cell A3. 01:41 You can see that clearly. 01:42 Actually there are two Gs in there and also on cell A5. 01:46 It's the second character there. 01:48 So, that certainly has its role to play. 01:50 But sometimes we need that information right here, because maybe based on that 01:55 we might want other information in a worksheet. 01:58 So, one approach to this is to use the function called FIND. What are we looking for? 02:04 Put this in double quotes. Double quote. We are looking for a G. 02:08 Now, I should be capitalizing it, right? 02:10 Because we are looking for a capital G and we will get back to that issue in a bit, 02:13 because that's of concern too. 02:14 We are looking for the letter G. Where are we looking? 02:19 We are looking in cell A2. 02:22 Now there might be times when you need to start not at the leftmost position 02:27 of the cell and if you do, you need to put in a third argument here that 02:31 indicates the starting character. 02:33 Now, that's a much more rarely used feature and so very often you won't need any 02:38 more than what we are seeing right here. 02:41 We want to find the letter G in cell A2 and the result here if found will give 02:47 us the character position. 02:48 And of course you can see here, it will not be found, so we will get this as a result. 02:53 Simply by dragging this down we will see and of course we can see it clearly on 02:57 the screen where this is working and in situation number two there, that's 03:01 actually row 3, G was found in the second position. 03:05 Now, notice it didn't find the third one or it didn't mention that there is a 03:08 second one in there. 03:09 That's the first place it found it and in both cases here, it finds it in position two. 03:14 Now, I had mentioned here the idea that I used uppercase. How about lowercase here? 03:19 Is this going to work here? 03:21 No, there is no lowercase G. 03:24 Now, there might be times when you are going to find a G, if it's uppercase or 03:29 lowercase, in that situation what you would want to use is the functions that's 03:33 very similar and same general syntax and everything, but it's called SEARCH. 03:37 There we go and the SEARCH function and let's focus on this one particularly. 03:42 SEARCH says I am looking for G. Now even though I had used lowercase here, 03:46 it finds the capital G. And so you have to think out different variations on when 03:51 you are specifically looking for the upper and lowercase. 03:54 You want to use the FIND function, but if you don't care use SEARCH. 03:58 Now, another function that allows us to work with that data and sometimes either 04:03 by itself or in combination with a Find or Search is a function called MID. 04:09 Now, think of the word middle here and let's not take that word literally. 04:13 But the MID function allows us to pull out information from the middle of a 04:18 character and again, not necessarily the exact middle. 04:21 Here is one example and possibly you might approach this differently too by 04:25 taking the information in the column C and splitting it into different columns. 04:29 But we might want to extract from here the state. In all cases, the state 04:34 entered here is two characters, but where is it? 04:37 If we were to approach this, while we are just looking at the MID function or 04:41 thinking of it only by itself, we would say well, what are we starting at here? 04:45 What are we trying to find out of here? 04:47 And so we're looking at this text right here, but we don't know where to start. 04:52 And the first case here of Boulder Colorado, Boulder, that's seven characters, 04:59 the comma is eighth and the space is ninth. 05:01 We need to start at the tenth position. 05:04 Obviously, in the next entry it's not going to be ten or the entry after that. 05:08 So, sometimes you will use these together, and not always. I don't mean to 05:12 suggest of the MID function is used always with FIND because it isn't, but there 05:17 can be situations where you need to pull out data from a given starting point. 05:21 So, in this case we might start with =find, just like we did in the previous 05:26 example and what indicates the end of the city? The comma. 05:31 So, we are looking for within double quotes the comma, and we are looking in 05:37 the cell right here. 05:39 So, this of course is going to tell us where the comma is found. 05:42 It's in the eighth position. 05:43 So, armed with that knowledge, we can then use the MID function and say we're 05:49 looking here, comma. And where do we want to start extracting data from? 05:54 This tells us where the comma is. 05:57 We want to start two characters later, not one character later. 06:00 That's the space, but two characters later, so we are going to add 2 to this, comma. 06:06 Now how many characters do we want to extract? 06:09 Just two to pick up the state. Multiple parenthesis, we need to put it in the 06:14 closing parenthesis, so this will extract the state. 06:18 Now, if you have any choice in redesigning the way this data is displayed, 06:22 ideally the data in column C really should be split into three columns. 06:26 That's a different issue. 06:27 But we are able to pull out information. In this case using the MID function 06:32 along with the FIND function. 06:35 Now based on different needs at different times and looking back at the data in 06:39 column A, you will at different times to have a different need for this. 06:43 You will want to pull data out of column A. Maybe it so happens that the 06:48 fourth and fifth positions in column A reflect the color code of the items in questio.n =mid. 06:55 We are looking at the Part# here. 06:57 We are looking here and we want to start at the fourth position. 07:02 In that first example that would be the letter K. So we want to start at the 07:05 fourth position and extract two characters from there. 07:10 So, we want to pull out of that first set of data there with the first cell in A2. 07:14 We want to pull out K9 and that's what we will see. 07:19 And you will see what's happening in the others of course. 07:20 Same general idea. We are pulling out information, not necessarily from the pure 07:25 middle, but the word Mid helps us remember what it means. 07:29 One by one functions like this are not always that compelling and yet sometimes 07:34 used in combination or in dealing with data that we didn't design, 07:39 they can be extremely viable when trying to extract data from cells that we 07:43 didn't necessarily design, yet we have them on our screens and we need to get to the information quickly. Collapse this transcript
Extracting specific data with LEFT and RIGHT
 00:01 A common problem in some kinds of data in Excel is that there is perhaps too 00:05 much information in a given column. 00:07 Now, we don't need to go into all the details to tell you why maybe column A 00:10 would've been better if we split out the names or column F would have been 00:14 better if we split that into three separate columns. 00:17 Let's imagine a situation like we see in column C. There is nothing wrong with 00:20 that display, but we might just need to get information either from the left or 00:25 the right side of the entries there. 00:27 And like a lot of part number codes maybe these are position sensitive and 00:31 perhaps in this example here, the first two characters from the left represent a 00:36 color code or size code. 00:38 Let's extract two characters from the left-hand side of the data in C2 for 00:42 starters, using the function =left. Where are we looking? 00:48 Right there in C2, comma. How many characters do we want to extract from there? Two. 00:53 And we see what happens of course on the other cells. 00:55 We just pull out the two leftmost characters. 00:59 Similarly, as you might imagine in a different scenario, a different situation, 01:03 maybe there are the three rightmost characters have some meaning and so without 01:06 skipping a beat here, we could simply use for that example here 01:10 RIGHT, meaning right of course and from the right-hand side, how about three characters? 01:15 Just to show the contrast here, pretty simple, pretty straightforward. 01:20 And also take a look at column F. Again without going into the details of why 01:24 that could have been better, let's simply pullout the five rightmost characters, 01:28 because we'd like to sort our data by zip code. 01:31 We need to isolate the zip code. =right, simply looking at this data, comma, 5 01:39 and we'll do that for the other cells as well. 01:42 Pretty straightforward! 01:43 Now, the data in column A is in an appropriate order but we might want to 01:47 isolate the last name only, and possibly the first name too. 01:51 That might take a little bit more work here but let's just pullout the last name only. 01:55 The problem here might be that we don't always need the same number of 01:58 characters, so we can't use the same function exactly unless we happen to know that, 02:03 and we do know, let's say a comma tells us the end of the name. 02:08 So if we were to find the comma first, this is again a suggestion along the 02:12 lines of one-by-one some of the text functions might not be that strong and yet 02:17 when used with others really can give us some power here. 02:21 So let's find the comma first, =find, find the comma in A2, and in this case, 02:30 it's in the 6th position so when we know that, then we can then say we want to 02:35 pull out data from the left-side of A2 and if we use the value that we've got 02:43 from comma, we're going to be extracting in this first case Baker comma. 02:48 So wherever we found the comma, we want to takeaway 1 from that. 02:51 In effect in this case, we want to extract the five leftmost characters, and 02:57 that's exactly what happens here, and it would do the same thing here. 03:00 So once again, you can get a little creative using these in combination, but for 03:03 the most part the Left and Right functions are fairly straightforward. 03:07 You are extracting data from the left or the right side of a given cell. Collapse this transcript
Removing excess spaces with TRIM
Using CONCATENATE with functions
Adjusting case within cells with PROPER, UPPER, and LOWER
 00:00 There's nothing really wrong with the data in column A, nor the data in columns 00:06 D and E. But let's face it. 00:07 Many times we don't want to see data that's all uppercase. 00:12 And in B2, we see the way we might want to see Donna O'Brien's name. 00:18 Just the O and the B capitalized in O'Brien and the D in Donna. 00:21 And we certainly don't want to re-type this. 00:23 It's is a great function for this, simple and easy to use, =proper. 00:28 Proper will capitalize the first letter of the entry as well as any letter that 00:34 follows a space or any punctuation. 00:37 So the B that follows the O actually follows an apostrophe. It's going to be capitalized. 00:42 And we're simply looking at the data here and D will be capitalized and fast and 00:47 easy, what else do you need? 00:49 Copy this down in the column 00:51 Occasionally, I get a question when I'm teaching this in live session. 00:54 Anything we can do here with McDonald? 00:56 Is there way to make that D capitalized? 00:59 Certainly no easy direct way, and you might imagine somewhere down the road 01:02 in the future that maybe there will be a way to do this. 01:05 It does work here with the apostrophe. 01:06 But you see what's happening in all other cases. 01:09 Keep in mind this works for other kinds of situations. If these are book titles, 01:13 it might be what you want too. 01:15 But it will be capitalizing all those Thes and the As and the Ans and so on. 01:20 You don't have to adjust those manually. 01:22 This not a question. 01:23 This is a fast and easy choice here. 01:25 And in column F, you might want to do, not only an adjustment here, but also 01:30 pulling these together. 01:31 And it's just a reminder that sometimes knowing different capabilities with 01:34 text strings allows us to do this. 01:36 So a quick adjustment here might be let's use proper here, along with the 01:41 ability to pull together, for example, Baker just coming out of E2 and then 01:47 after that double quote comma space and Mark and that's all we need there. 01:54 So we're pulling together the names from different locations as well as using 01:58 the Proper function here, to make only these significant letters be capitalized. 02:03 There are other times when you got letters in lowercase, the way we see them in 02:06 the column H, and we want to make the letters here in uppercase. 02:11 So the function of course is called Upper. 02:14 It does nothing with the numbers that are in there. Nothing it can do with those. 02:17 It simply checks the text entries and makes them uppercase. 02:20 So here we've got upper. 02:22 All the text entries here become uppercase. 02:25 If for whatever reason, you have data like this and you did want those letters 02:29 to be in lowercase, you would use this function. 02:32 And I just can't think of a very good example when I would ever use the Lower 02:35 and I can't think when I have ever used it except just to prove an example. 02:38 So that's Lower looking at the data in column A. 02:42 So fast and easy and a quick adjustment. In all these situations where you're 02:46 using these functions to adjust, the follow-up step most of the time is to say, 02:52 okay I would like to keep this data and throw away the original. 02:57 And a very fast way to do this is simply to take the results by way of these 03:02 functions and with the right mouse button, drag them on top of the old data as 03:06 we might do here, drag it on top of the old data with the right mouse button and 03:12 from the pop-up menu Copy Here as Values Only then, we could just get rid of 03:16 this and we're all set. 03:18 And sometimes, you'll do that to itself, like in this case, here put a new title 03:23 up there, perhaps take this data and simply with the right mouse button copy it 03:28 up or down, left or right, to bring it right back on top of itself, let go. 03:32 Copy Here as Values Only. 03:35 The formulas are all gone, the results are left, get rid of this. 03:38 You don't need that anymore. 03:39 So fast, easy techniques for adjusting upper and lowercase using the three 03:44 functions Proper, Upper and Lower. Collapse this transcript
Adjusting character content with REPLACE and SUBSTITUTE
 00:00 The two text functions Replace and Substitute have something in common. 00:05 And the Replace function actually has something in common with one of the 00:08 command sequences in Excel. 00:10 Let's see how these two different text functions are related to one another. 00:14 In column A and B, we see some entries and in this first cluster of 00:19 information right here, I've titled in B1 what we actually want to see below 00:24 and this is manually done here. 00:26 There are times when you want to replace a given character with either other 00:32 characters or perhaps nothing, and you see the effect of what's happening here. 00:37 Now if we wanted to replace all the hyphens in a list like this, we could 00:42 just easily jump in on the Home tab in the extreme right in the Editing group, Find and Select. 00:48 They can do replace here and simply say every time we've got a hyphen here, 00:53 we replace it with. 00:54 We don't even have to jump into the next panel below where it says Replace with. 00:57 We will just do a Replace All, and all hyphens, and remember there are two 01:01 in each of that set of data that are in column A, they are all replaced and 01:05 it tells us how many. 01:06 Simple straightforward. 01:07 You don't need a function for that. 01:08 So I am going to click OK and close and then Undo, Quick Access Toolbar there. 01:14 But sometimes we might to want to replace this either with a specific character 01:18 or with something else. 01:19 Let's talk about how to use the Replace function here, =replace, and this is position based. 01:26 We are not looking for a text here. 01:27 We are going to do this by position. 01:29 What might throw you at first is it says old text. 01:32 You certainly can use double quotes here, but we want to be working off of the 01:35 data in cell A2 here, and we are looking for what's in the fifth position and 01:41 in all cases here the fifth position has a hyphen. 01:45 So we put in the 5. 01:47 And we only want to replace one character. 01:50 It says number of characters. 01:52 We want to replace one character and what do we want to replace it with? Nothing. 01:57 So I will just put on a comma, all done here and we see what has happened. 02:01 Of course, we can copy that down and see the effect of the way it's done 02:05 manually, but this is how it's done here. 02:07 We are replacing the fifth character and only the fifth character with 02:12 effectively nothing. 02:13 Now, a slight alteration here. 02:15 If we wanted to replace the hyphen with some other character, all right, 02:18 or it can be a letter certainly, if you wanted to. Whatever it takes here. 02:21 First of all, double quotes are required, but you want to replace this with the 02:24 letter X. That's big and prominent, just to show what it would do here. 02:28 Something like that and you see the effect there. 02:32 Similarly, if these were multiple characters, you might put in XXX, just to show 02:37 how that might work. 02:39 So here we are replacing one character with three characters and it looks like that. 02:44 So lots of variations and as soon as you will see a few examples of these, 02:47 you will just keep that in the back of your mind. Here and there when you have 02:50 to massage data, when the command isn't appropriate because you don't want to 02:53 get rid of all characters, this is based on a character position. That's where we remove. 02:59 Example over here, just for variety, same general idea. 03:02 Here's the eighth character, and if you look at the original data, the eighth 03:06 character is a hyphen over there, a dash, and replace that with the (USA) and so 03:12 just a quick look of this =replace and same general idea. 03:16 This is the data we are looking at. 03:18 In this case, we are starting in the eighth character and we want to replace 03:21 one character over there again, but this time what we are replacing with is 03:25 within "(USA)" and done. 03:32 So you see how that could be done too. 03:34 Quite a variety of options with this. 03:37 Now the other function called Substitute, in contrast with the Replace function, 03:43 the Substitute function is about content not about positioning. 03:47 The description at first looks similar to the one related to Replace up in row 1, 03:52 but in row 7, this says Substitute - remove the first dash. 03:56 Now we can remove the second dash, as you see over in column E there. 03:59 This works a little bit differently. 04:01 Same general idea though. 04:03 We are trying to change the content of the body of cells, group of cells perhaps. 04:07 Type =substitute this time and here's what we are looking at, comma, what we 04:14 are trying to substitute for it, as the case may be a hyphen, and what we want 04:19 to put in place of that is essentially nothing, so we can just leave that argument blank. 04:24 But we only want to replace the first instance of it, and there we go and 04:27 you see what's happened. 04:29 The second one remains there. 04:30 So we only removed the first one. 04:32 Again, so you don't necessarily get the idea of that the Substitute means 04:36 remove, because it doesn't. 04:37 Instead of that hyphen, we might want to put in, and again I'll use just 04:41 a different symbol. 04:42 Maybe it's the pound sign, "#", just something else to put in there instead of the 04:48 hyphen and you see what happens there. 04:51 If we wanted to do that for all occurrences of this, if we just leave off the 04:56 last argument here, this will replace all occurrences of hyphen with a pound and 05:01 there too we might be thinking about using this by way of the command rather 05:05 than actually using a function here. 05:07 But we wanted to do just a second occurrence, so we will put in a ,2 05:10 and this will change the second dash there to a pound sign. 05:14 So you could see variations on how that might work. 05:17 In the example on the right, similar idea, here we are replacing that 05:21 second hyphen with XXX. 05:24 Once again, a quick look at this Substitute. 05:28 Here's the data we are looking at, once again we are using that hyphen or dash 05:32 here and we want to replace that with, in this case, XXX and we only want to do 05:38 it for the second example. 05:39 And you see what's happening there, and that's what's done in the other cases as well. 05:44 So here and there using Replace and Substitute might be quite valuable when 05:48 you're dealing with tons of data. Collapse this transcript
11. Financial Functions
Calculating payments with PMT
Finding future values with FV
Determining total amount of future payments with PV
 00:00 An important function when it comes to investments is the PV function. 00:05 Question at C1 stated, what is the present value of monthly payments of \$2000 at 00:12 6% annual interest I will be making over the next 360 months? 00:15 That's probably talking about a house here. 00:18 What's that money worth right now? There it is. 00:21 How do we get to this value? 00:23 The function is pv, =pv. 00:27 6%, and in nearly every financial function that deals with rates, if it's 00:34 anything related to payments, more often than not it's related to the monthly 00:37 payments, so we will divide by 12. If it were quarterly, we divide by 4, and if it 00:42 were yearly, we wouldn't put any division in, over 360 months, 2000. 00:46 That's the present value of those payments we will be making. 00:52 And as in many cases, although that is technically the correct answer, to make 00:57 that appear and perhaps be able to want to use it and plug it into other 01:00 formulas, that would probably work better for most people if it were a positive entry, 01:04 so a quick edit here. 01:06 Put a minus in front of the amount here, and it gets displayed as a positive entry. 01:12 Another use for the PV function is expressed in Row 6 with the question, 01:17 how much money do I need to invest now if I want to have \$150,000 in ten years? 01:23 Well, if you're confident that you can get 5%, you need to have \$92,000 available. 01:29 6%, 7%. Are we talking stock market here or some other scheme that you are sure you 01:35 know it's going to work? 01:36 I am sort of making fun of the idea that we are not always so sure. 01:39 But let's show how we can come to this value here by way of the PV function, =pv(. 01:49 We want to be referring to this 5% right here, comma, 10 years. 01:54 We are not going to be making any payments here, 0. 01:58 We would like to reach \$150,000. 02:02 Be sure not to put a comma after the 150. 02:06 If you work with financial functions you wouldn't do that, but sometimes you 02:09 slip up a little bit. 02:10 So what do we need here? 02:12 That's the same value we saw before. 02:13 Once again, it's negative, so ideally what we would do here, most people would 02:16 put a negative in front of this so we get this value here. 02:20 Now, if we would like to test this out for the other amounts here, we can 02:23 simply copy this or double click it to copy it down a column to see how this 02:28 stacks up with other rates. 02:29 If we are confident we can get 10%, then this would be our starting point. 02:33 This is how much money we need to invest if we are going to get 10%, to have 02:37 \$150,000 in 10 years. 02:40 So here are some valuable uses of the present value. 02:43 That's the PV function in Excel. Collapse this transcript
12. Information Functions
Working with the IS information functions
Using error-checking functions: ISERR, ISERROR, IFERROR
Getting data from remote cells with OFFSET
 00:00 As you look at the data in columns A and B and also the information in columns 00:04 D and E, you can imagine what we are trying to set up in this particular worksheet. 00:09 Column A will grow and grow and grow as will column B. We're tracking a closing rate, 00:15 maybe it's stock market, maybe some kind of futures market, some kind of 00:18 number that's vital to our interest and we want to track the information and 00:22 maybe we've been tracking it for only months but for years, so column A could 00:26 get quite long. And elsewhere not necessarily in the same worksheet, we always 00:32 want to have the latest entry right here. 00:35 And so if for example here, if the next entry is going to be the 10th year, 00:38 I'll just drag it this way. 00:40 We'd like to see this be the 10th and if the next number our here is going to 00:43 be 267 or whatever, 00:45 we want to see that right up here in cell E2 as we set up here on the screen 00:50 and it's not changing. 00:53 Now there's an unusual function and I say unusual because like certain kinds of 00:58 functions you might just gloss right over it if you happen to encounter it. 01:02 It actually is part of the lookup and reference category of functions and if you 01:06 happen to click this button on the Formulas tab in the Ribbon and come to 01:12 OFFSET, look at the description. 01:14 It returns a reference to a range that is a given number of rows and columns 01:19 from a given reference. 01:20 And it's definitely programming- like in nature and that's certainly is 01:25 insufficient in terms of explaining exactly what this does and yet that's just 01:30 what we need in situations like this. 01:32 So instead of the manual entry here, let's put in a function that will and this 01:38 is going to be OFFSET function, of course. 01:40 That will always keep track of the latest entry as they start to appear here in 01:46 column A consecutively after the existing entries. =offset. 01:55 Now the reference is going to be to the cell starting in cell A1. 02:01 How many rows down do we need to go? 02:03 This is going to vary. 02:05 If we put information in A9, we'll have more entries than we have there now and 02:09 obviously this will keep adding and that's going to be the case. 02:12 So what we need next is some way count how many entries we have in column A 02:18 and some of you are familiar with the function CountA. And just CountA. 02:24 That counts text entries in column A. And right away you're probably thinking what is 02:31 in that counting A1 as well? Yes it is. 02:33 That's all right. 02:35 Subtract one, do not count that. 02:38 That's how many rows we want to move downward and you're probably saying 02:42 yourself, what is the value of CountA looking at column A? The value is 8. 02:48 We subtract one and so we're looking at 7. 02:51 So what we're we about to say here? 02:53 From position A1, move down, how many rows? Seven. 02:59 How many columns do we move to the right? None, zero. 03:04 In other words, we don't want to move into cell A9. 03:07 We essentially want to move into A8 and grab that data. 03:13 And we see this and a quick test of this occurs 03:16 is so what if we do put in a different data after this? 03:23 Immediately this is reacting. 03:24 Now once again looking at the logic of this, what are we seeing? 03:27 We're always looking at A1 as the count of entries in column A goes up. 03:33 That's one bigger than the offset that we need. 03:36 For example, right now we need to go into cell A9 and pick up that value. 03:41 This is telling us there are nine entries. 03:44 We want to subtract one so this moves down eight. 03:47 And remember the nice shortcut too. 03:48 When you're evaluating formulas, you can highlight a portion of it and simply 03:53 hit F9 to see what that's equal to. 03:56 It really helps understand certain functions. 03:59 So, of course, now nine minus one would be eight, move down eight cells from A1 04:03 that would take us down to A9. Don't bother with the column 0. 04:06 We could even leave that off, and we don't hit Enter. 04:10 We'll just hit Escape to adjust it and the same idea here. 04:14 I could even copy this. 04:15 If I drag it it's going to copy the format. 04:17 Another way, I might just copy this here, right-click over here, do a Paste Special. 04:23 Don't copy the format but just the formulas over there into E and this should be 04:29 set up automatically too. 04:30 And that's picking up the wrong-- well, not exactly the wrong entry yet because 04:34 we don't have any number here. 04:36 Let's put in a number down here and now we see how that works. 04:40 Now you could say in a certain sense this is a specialized function but the use 04:45 as it's seen here is the powerful one. 04:48 If you do some exploring in various Excel Help menus, you'll see the OFFSET 04:52 function being used for describing dynamic range names. That might be worth 04:57 looking into on your own. 04:59 This is certainly one of the more powerful uses of it. 05:01 At first, it seems a little obscure but it's easy to understand the more you 05:05 look at it and it certainly gets done what we need to get done. 05:08 And this is going to work for a long, long time particularly if you get a 05:12 situation where you keep adding dates, keep adding information to column A, keep 05:16 adding information in column B, it's always going to pick up the last entry. 05:21 When you've got situations like this too, make sure you don't have empty cells 05:25 in there because then it's going to mess up the count. 05:27 If you have only one, possibly you can make an adjustment and not subtract the 05:32 one and somehow work around that but here's the basic idea. 05:35 This is the Offset value right here, the number of rows to move downward, and 05:41 the number of columns to move rightward in some cases. Like in this not at all. It's not an issue. 05:44 So a great tool when you need it. 05:47 It's the OFFSET function. Collapse this transcript
Returning references with INDIRECT
Conclusion
Goodbye
 00:00 Hi! This is Dennis Taylor. 00:03 Thank you for watching and listening to these videos on Excel Formulas & Functions. 00:08 I am sure that after watching these videos you are a much more confident user of Excel. 00:12 You know a lot more about functions. 00:15 And in the future, I think you are going to be less intimidated by what you 00:18 see in Excel, and particularly if you have those challenges where you need to 00:21 manipulate data and come up with totals that you just found a bit illusive in the past. 00:26 I wish I could help each one of you on a one-to-one basis. 00:29 We can't really do that of course, but it's been a pleasure presenting this 00:33 information, and perhaps we'll together again soon in another course. 00:37 Thank you! Collapse this transcript

Are you sure you want to delete this bookmark?

### Bookmark this Tutorial

#### Description

{0} characters left

#### Tags

Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:
cancel

### bookmark this course

{0} characters left Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:

Error:

go to playlists »

# Create new playlist

 name: description: save cancel

# You must be a lynda.com member to watch this video.

Every course in the lynda.com library contains free videos that let you assess the quality of our tutorials before you subscribe—just click on the blue links to watch them. Become a member to access all 98,648 instructional videos.

Your organization has a limited access membership to the lynda.com library that allows access to only a specific, limited selection of courses.

You're logged in as an account administrator, but your membership is not active.

Contact a Training Solutions Advisor at 1 (888) 335-9632.

# How to access this video.

If this course is one of your five classes, then your class currently isn't in session.

If you want to watch this video and it is not part of your class, upgrade your membership for unlimited access to the full library of 1,899 courses anytime, anywhere.

You can always watch the free content included in every course.

Questions? Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com.

You're logged in as an account administrator, but your membership is no longer active. You can still access reports and account information.

To reactivate your account, contact a Training Solutions Advisor at 1 1 (888) 335-9632.

# Need help accessing this video?

Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com for help accessing this video.

site feedback

Thanks for signing up.

We’ll send you a confirmation email shortly.

• new course releases
• special announcements

Keep up with news, tips, and latest courses with emails from lynda.com.