Become a member and get unlimited access to every course in the library.

# Excel 2010: Advanced Formulas and Functions

## with Dennis Taylor

In Excel 2010: Advanced Formulas and Functions, author Dennis Taylor demystifies formulas and some of the most challenging of the nearly 400 functions in Excel and shows how to put them to their best use. The course reviews the building-block functions, along with a few critical keyboard shortcuts that will speed up working with Excel data. It also covers how to perform advanced searching and data retrieval with LOOKUP functions, tabulate data with counting, statistical, and math functions, reformat data with text functions, and work with financial data using advanced formulas. Exercise files accompany the 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
• Reconfiguring cell data using text functions
• Calculating dates, times, and days of the week
• Analyzing mathematical and financial data
• Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
• Working with rounding functions
• Returning cell references
author
Dennis Taylor
subject
software
Excel 2010
level
duration
5h 5m
released
Dec 03, 2010

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
 00:04 Hi! I am Dennis Taylor, and I am pleased to present Excel 2010 Advanced Formulas and Functions. 00:10 For many Excel users, the central focus of what they do is writing formulas and functions. 00:16 If you really want to take advantage of Excel's power, you need exposure to 00:20 its many functions. 00:21 Nobody needs to know them all, of course, but just mastering the intricacies of 00:25 such vital functions as IF and VLOOKUP-- as well as the related MATCH and INDEX 00:30 functions--gives you enormous control over your data, and opens the door to 00:34 creative data analysis and manipulation. 00:37 I'll start it off by showing you some great shortcuts for building formulas and 00:41 functions, numerous variations on the aforementioned IF and LOOKUP functions, 00:46 vital statistical and MATH functions, and some DATE functions that could prove 00:50 to be indispensable. 00:52 I'll show you how TEXT functions can help you clean up flawed data, 00:56 how powerful array formulas and functions give you even more analytical 01:00 possibilities, and I'll show you how to use functions in combination with one 01:05 another to achieve even greater power. 01:08 To become a proficient and more confident Excel user, you need to know more 01:12 about formulas and functions. 01:14 This course is filled with tons of techniques and tools to help you reach those objectives. 01:20 Welcome to Excel 2010 Advanced Formulas and Functions! Collapse this transcript
Using the exercise files
 00:01 If you have access to the exercise files for this course, you can put them on 00:04 your Desktop--as I have-- or anywhere else you want. 00:08 Each file here--for example, Chapter 5--has various worksheets in it that 00:13 corresponds with each portion of each course lesson. 00:17 If you do not have access to these files, you can follow along with your own 00:21 files as we proceed through the course. Collapse this transcript
1. Formula and Function Tips and Shortcuts
Using the entire row/column references
Copying column formulas instantly
 00:01 It's not uncommon when dealing with large lists of data in Excel to have 00:04 formulas that need to be copied through an entire column. 00:07 In column H here, we need an adjusted salary. 00:10 It's simply going to be the total of current salary and the bonus. 00:13 So formula, one or many different variations might simply be =G2+F2. 00:19 I am going to press Ctrl+Enter, so the active cell doesn't move. 00:22 I am ready to copy this down the column. 00:25 And although we certainly can use copy and paste techniques, by far the best 00:29 way to do this--that most people would agree on-- would be to drag the lower right-hand corner. 00:34 However, that's not going to be the fastest way, 00:37 particularly if this list is huge; think of how long this might take if this is 00:41 thousands and thousands of rows. 00:43 If you excuse the bad pun for a moment, this could be a real drag. 00:46 It might take a long, long time. 00:49 A much better way--and practically flawless--is simply to double-click the 00:54 lower right-hand corner. 00:56 That corner, by the way, has a name. 00:57 It's called the fill handle, f-i-l-l handle. 01:00 I am going to double-click. 01:02 First question in your mind might be, "Well, how far did this get copied?" 01:06 And did it really get copied? 01:08 Well we can look at the numbers in the second example here, yes. 73,430 + 2,000. 01:13 We have got a new total. 01:14 This is working properly; the formulas have been copied. 01:18 How far did this get copied? 01:20 A not so well known shortcut is Ctrl+Period. 01:24 I am going to press it now once. 01:25 You can see what happens: the active cell is in cell H709--why only that far? 01:32 Scroll down a little bit. 01:33 That's the bottom of the list here. 01:35 Every time you press Ctrl+Period--if you have got a highlighted range that is-- 01:39 the active cell moves around the corners of the range. 01:43 In this case, it's in a single column, so it simply--in this case--tells us 01:47 quickly and easily the extremities of this range. 01:51 Now what happens if we have other kinds of data here? For example, what if you 01:55 forget? What if there is nothing in this cell? 01:57 I am going to press Delete. 01:59 In prior versions of Excel--I am also going to double-click this section here 02:03 and press Delete to get rid of it-- 02:05 in prior versions of Excel, if you double-click the lower right-hand corner, if 02:09 there is an empty cell to the left, as we see here, the formula would only go this 02:14 far. But here that's not going to be the case in Excel 2010. 02:17 It goes down to the bottom of the list. 02:20 The change, and you want to note it from time to time as you are using perhaps 02:23 different versions of Excel, as long as there is other data off to the left, we 02:28 can see that this is a larger list. 02:29 We have data in other columns. 02:31 Now here and there, you might need to experiment with that a little bit, but we 02:34 don't have to worry about the empty cells to the left. 02:37 Let me press Ctrl+Z to do another Undo here, and point out another aspect of this. 02:42 On purpose here, I am going to put in an empty column to the left. And now I am 02:47 going to double-click. Nothing happens. 02:50 Same thing would have been true in prior versions. 02:52 If there is data to the right, however--I will just make up a heading 02:56 here called Tax Code, over a few cells I am going to type in 5%, and press Ctrl+Enter-- 03:04 what happens now if I double-click? 03:07 If the column to the immediate left is empty, then the double-clicking technique 03:12 follows the column to the right. 03:14 Another aspect of this too is to press Ctrl+Z to undo. What if we hide the column H? 03:21 Right-click Hide. 03:23 I am going to double-click here. 03:26 You see what's happening. 03:27 In prior versions, it would have worked in the reverse way, so it's interesting 03:31 the way that redefined the use of this. But the general idea--and the way this 03:35 is going to work most of the time and for you--is when you got data lined up, in 03:40 this case we want to take out that empty column, typical situation. 03:45 You need to copy information. 03:46 Now this does not need to be a formula, although we are talking about formulas 03:50 and functions in this particular movie. But whether this is text or a value or 03:55 in this case the formula, double- clicking the lower right-hand corner takes us 03:58 to the bottom of the column. 03:59 And again, Ctrl+Period quickly determine the limits of how far it was copied. 04:05 Unfortunately, there is no corresponding shortcut for copying formulas across 04:09 a row, but rarely what you need to do that across the thousands and thousands 04:14 of rows that you might need to use when data is set up like this in the column. 04:18 So this is a great shortcut, and you can use it even when you have got a small 04:21 amount of data. Even if it's only five or six rows downward, make this fast and easy. 04:26 Enter the formula or text as needed, double-click the lower right-hand corner, 04:29 and have it quickly fill in the remainder of the column through the active part of your data. 04:33 It's a great shortcut. Collapse this transcript
Converting formulas to values with a simple drag
 00:01 If you've created formulas that manipulate data on a worksheet, you usually want 00:05 to keep those formulas to provide the paper trail that shows you where the 00:09 information comes from. 00:10 In this worksheet called ValueConvert, in cell H2, we see a formula--and this is 00:16 copied throughout column H--that simply provides a new salary here. 00:20 Now for the moment, we want to see the current salaries, and we need to see the 00:24 mechanism here for creating new salaries. And the Job Rating, 00:28 we're simply ignoring those who have a job rating of one and taking the others 00:32 and giving them \$1,000 per job rating. 00:34 So in the example here, this person has a job rating of 5. 00:37 We see what's happening here. 00:38 It's four times 1,000, plus the current salary. 00:41 So there we see what's happening. And, for example, in row 5, that person doesn't 00:44 get any salary increase at all. Simple little formula. 00:48 Now formulas take up space, in the sense take up time, in that they have to be 00:52 recalculated. And if this is a few hundred rows, not a big deal--even a few 00:56 thousand. But we get into the tens of thousands sometimes worksheets that have 01:00 formulas, it becomes a concern, and sometimes you have to balance that 01:05 recognition with the rationale you really need the formulas. 01:08 So what we're talking about here is the idea that there will be situations where 01:11 either for purposes of saving time and space or simply for clarity, you want to 01:16 get rid of the formulas but keep the results. 01:19 Maybe eventually these salaries have been approved. 01:22 We don't need to understand the mechanism here. 01:25 Maybe what we really like to do is to take these results in column H and put 01:30 them in column F and get rid of column H. Maybe it's that end-of-year 01:33 adjustment here that we make. 01:35 So we could either double-click the lower right-hand corner here or simply with 01:39 the Shift key held down, press Ctrl+ Down Arrow and highlight all of these. 01:44 So what is it that we want to do here? 01:45 In effect, we want to take formulas and convert them into the results. 01:51 In other words, let's not make this cell H2 consist of a formula anymore; 01:56 let's actually make it be equal to 61,520. 02:00 Now there are certain times when you want to make the conversion right within 02:03 the cell itself, or possibly copy the actual values, for example, into column 02:10 F. So let's take the situation where we want to effectively move these into 02:14 column F. Now a simple move is not going to work because we're moving a formula. 02:20 So I'm going to point to any edge--top, bottom, left: doesn't make a difference-- 02:26 and hold down the right mouse button and drag these cells into column F. And as 02:33 I let go here, there is a choice called Copy Here as Values Only. 02:38 So the 61,520 that we see will end up in cell F2 as 61,520. 02:46 So we've moved those so-called new salaries into column F. Now over here, 02:50 we've got old formulas and things to the left over here we don't need. We'll get rid of this. 02:54 So we effectively have thrown that away. 02:56 Now a different approach to this--and it's six of one, half-a-dozen of another-- 02:59 is I press Ctrl+Z and return to the earlier layout here. Here we are again 03:03 with these formulas. 03:05 If we wanted to, we could've copy these formulas to themselves. 03:09 Now recognize too that what we're doing is simply a shortcut for the longer 03:13 process of copying data. 03:15 We'll do it the longer way. The data is highlighted. Here is a copy. 03:18 You can use Ctrl+C, Copy button, or right-click > Copy, and now what? Paste > 03:25 Paste Special > Values. 03:29 I could have used one of those buttons as well too. Values, this way. 03:33 So that's another way to do this. 03:35 Even faster, once you get familiar with the concept--I'm going to press Ctrl+Z 03:39 again to undo--is simply to take this list and with the right mouse button this 03:44 time, drag it off the selection and then right back onto it. 03:47 So I simply dragged right and left with the right mouse button. 03:50 I'm letting go. Copy Here as Values Only. 03:54 So in this kind of situation here, maybe you will change the labels. 03:57 We don't want to keep both of these-- probably wouldn't forever. Possibly you've 04:00 hold on them for a while, but at some point may we'll just, now since these are 04:04 no longer formulas, move this into column F. You can do that with the right 04:08 drag and then a move. That's fine. 04:10 Now we'll get rid of this. So, a couple of different approaches there. 04:14 Another situation off here to the right. We probably would want to keep these 04:17 formulas, at least for a while, but if the data is all old and we're finished 04:21 with this, everything has been verified, 04:23 let's throw away the formulas. 04:24 We need to keep the data. 04:26 These are all formulas that subtract the two cells to the left. 04:29 This time I'll do it from the bottom. Right-drag, down up, 04:32 let go: Copy Here as Values Only. 04:36 And one more quick one off here to the right. 04:38 Here I think you can see what would happen. 04:40 Nothing really wrong with these names, but it would look better if they weren't 04:43 capitalized, so we'll simply use a little function here called proper. 04:49 Double-click to copy this down the column. 04:51 We want to keep this data, but not the data in column Q. With the right mouse 04:55 button, simply drag any edge on top of the data in column Q. As soon as we let go, 05:00 Copy Here as Values Only. 05:02 Don't need these anymore. Delete. So, fast, easy ways to convert formulas into 05:08 their actual results. Collapse this transcript
Creating 3D formulas to gather data from multiple sheets
Updating values without formulas
Displaying and highlighting formulas
Simplifying debugging formulas
2. Formula and Function Tools
Reviewing function basics
Using and extending AutoSum
Using absolute and relative references
Using mixed references
3. IF and Related Functions
Exploring IF logical tests and using relational operators
Creating and expanding the use of nested IF statements
Using the AND, OR, and NOT functions with IF to create compound logical tests
4. Lookup and Reference Functions
Looking up information with VLOOKUP and HLOOKUP
Finding approximate matches with VLOOKUP
Finding exact matches with VLOOKUP
 00:00 In this worksheet entitled ExactMatch, we'd like to use column F here to convert 00:06 the scores we see in column E-- the ratings--into a numerical score. 00:11 There's a table over in columns A and B. Excellent is 99, Very Good 92, and so on. 00:18 This Fair rating that we see here in E2 should be a 71. And the VLOOKUP 00:25 function that's already in place isn't working, and there's a reason, but it 00:30 should give us an answer of 71. 00:32 It currently is giving us the answer 99. 00:35 What's different here? 00:37 Unlike examples with numbers, if you're looking up text information, or if they 00:42 are numbers of an ID type--for example, Social Security numbers, Employee 00:47 Numbers--LOOKUP can't be an approximate. 00:50 We can't say, "Oh, that Social Security number is close enough," and we can't 00:54 say that here either. 00:55 We're not trying to find how close the word Fair is to something else; 00:59 we need to find it exactly. 01:02 So, when a VLOOKUP function or an HLOOKUP function is using an exact match 01:07 scenario, as we're looking at here, we need a fourth argument. 01:11 As it now stands, this VLOOKUP is accurate in every respect, except it lacks 01:16 that fourth argument. 01:17 We're trying to look up the word Fair in this table, located in cells A2 to B8. 01:24 We want to get the answers out of the second column, comma, and the pop-up, as 01:29 soon as I clicked comma you saw it. 01:31 TRUE means approximate match. 01:33 We don't want that here; we want the word FALSE. 01:35 You can either type the word FALSE, or put in the number zero. It doesn't tell 01:40 you that on the screen. 01:42 And to me the word FALSE somehow suggests that maybe something isn't working or failed. 01:47 So, it's a little bit strange as to why we use these words, but FALSE certainly 01:50 will work here, and we will get the correct answer. 01:54 Having used this many- many times, I just use zero. 01:57 It also has a nice correspondence with another function we will see called 02:00 MATCH, and it certainly is less typing. There we go. 02:04 And before copying this, since we don't have a range name, we need to make this 02:08 an absolute address. 02:09 We'll simply press F4 here, and then Ctrl+Enter, and then simply double-click or 02:15 drag to make this work properly. 02:18 So, in every case here, we are seeing the correct answers. 02:21 Anytime you need to make the match be exact, 02:24 what that means is, unlike when in an approximate match, if you have an entry 02:29 here, suppose this entry said So-so, well that's a nice thing to say possibly, 02:35 but that doesn't fit in. 02:37 It's not anywhere in the table. And what else can Excel do here in this function 02:42 but give us an N/A, not applicable. 02:44 Because we've got this 0 out here-- remember it could be FALSE or 0, either way-- 02:50 this means an exact match. 02:52 We didn't find this in the list. 02:55 Imagine a situation where maybe you have thousands of these. 02:58 If there's a trailing space here, look what happens. 03:03 So a quick workaround on these, if you did have trailing spaces, is to use 03:07 another function, one of the text functions. 03:10 We want to look at the trimmed version of this, 03:13 the one that takes off spaces. 03:15 So we can use a function called TRIM here, and use it in a creative way here. 03:20 This was going to give us the TRIM version, and that does work. And of 03:25 course ideally, we would copy that downward and also upward to cover other 03:29 potential uses of it. 03:30 It would also tackle leading spaces right here, for example, put in a leading space. 03:36 Because we've got TRIM in place here, it, in a sense, you could say corrects the entry. 03:40 It doesn't really correct the entry, but it does use a corrected version of it 03:45 in here to find the answer. 03:46 So, sometimes you have to deal with that as well. 03:49 But the main focus here is this idea that when you're trying to look up 03:52 information, and it must be exact, 03:55 you need to have a fourth argument in a VLOOKUP. 03:59 If you're using HLOOKUP, the same rules apply. 04:02 In that scenario of course, you're looking up data in a row, across the top row, 04:07 but there, too: the 0 or the word FALSE means an exact match. 04:12 Not exactly intuitive--in fact, not even in close--but that's what has to be done 04:16 when you're using the VLOOKUP to find an exact match. Collapse this transcript
Nesting LOOKUP functions
Finding table-like information within a function with CHOOSE
Locating data with MATCH
 00:00 In this worksheet called MATCH, column E has a list of Social Security numbers. 00:05 Let's imagine that the lists in columns A and B might be in a different 00:09 worksheet--maybe even a different workbook--and what we would like to be able to 00:12 do here is to see if this Social Security number--and the rest of them, of course-- 00:18 are found in the other list. 00:20 Now first thought might be, it sounds like VLOOKUP. But the purpose of the 00:24 VLOOKUP is to not only find a match, but to return another value. 00:29 Let's say that we simply want to know whether if this is found over in column B, 00:35 which again could be on another workbook, another worksheet. 00:38 We use a function called MATCH. 00:42 The MATCH function starts off looking very similar to a VLOOKUP function. 00:46 Here is the LOOKUP value. And by the way, the entries in column E don't truly 00:50 contain hyphens, nor do the ones in column B. The hyphens are in the format, 00:55 not the actual content. 00:57 So they do match up in terms of their layout. Comma. 01:00 Where are we looking? 01:02 We can highlight the cells from B2 downward, but since nothing else is in 01:06 column B, let's use the entire column reference, column B. 01:10 The third argument in a MATCH function is the match type. 01:13 That certainly isn't obvious, but as I press comma, look what happens. 01:18 We want an exact match, so we're going to put in 0. 01:20 Now you notice 1 means less than, -1 means greater than. 01:26 These only make sense when the cells you're trying to look up contains numbers of 01:31 a computational nature, 01:33 perhaps salaries or tax rate--something like that. 01:37 You're trying to find numbers that fall between, above, or below a certain level-- 01:42 in this case 0. 01:43 You can either click this or type the 0. Type it. 01:46 There we go. 01:47 Are we finding this number here? 01:49 Is it in that list in column B? 01:52 Yes, but what does that mean? 17. 01:55 It's in the 17th position of the LOOKUP array location. 02:00 So in this case, since we've chosen column B, it's in B17. There it is! 02:05 Right down there. Duke Mantee 145-69-6388. There it is, right there. We see that two. 02:11 It's in the 17th location. 02:13 Now what if it were not found? 02:15 What if this number were different here? 02:16 Maybe that's 7 on the end there. This is not found. 02:20 So there are many times when the MATCH function's purpose is simply to tell us 02:25 whether something was found in a different location. 02:29 It's certainly falls under that broad spectrum what we call LOOKUPs, but it's 02:32 different than VLOOKUP. 02:33 It can be used in other creative ways as well. 02:37 Moving off to the right here, in columns J, K, and L, we see a list of names, and 02:43 there is also another list out here in columns P and Q. Now again, imagine how 02:48 sometimes you need to match up data from different worksheets, different 02:51 workbooks. Of course, in this case they're on the same worksheet, just for ease of display. 02:57 We want to find out if Yvonne Randall here--or as we see the name here 03:01 Randall Yvonne--is found in this list, but notice how the lists are 03:06 structured differently. 03:08 Over here, we see names: last name, comma, first name. 03:11 Here's Randall, Yvonne. 03:14 Surely, it's the same person, isn't it? 03:16 But how do we provide the match here? 03:18 We actually have to use the MATCH function to construct the concatenation--as 03:24 it's sometimes called--of these two cells. 03:26 So let's start with MATCH here and put these two together by using a technique 03:34 called concatenation. 03:36 We want to take the last name, which is in J2, followed by ampersand. That means and. 03:42 So we want the word Randall and within double quotes here comma space double quote. 03:49 This is the same style of layout we see in column P: the last name, then a comma, 03:54 space, and the first name. 03:57 Where is that found here? K2. Comma. 04:01 So let's take this Randall, comma, space, Yvonne and see if we can find it in 04:07 column P. And we must find an exact match here. Comma, zero. 04:14 Did we find it? Yes, we did. 04:17 Row 13, there is the name, Yvonne Randall. 04:21 Since we again used the entire column references rather than cells, we don't 04:24 have to worry about absolute addressing here. 04:26 We will just double-click to copy this down the column. 04:30 There is no John Gilligan in that list to the right. 04:33 How about Rene Hood? 04:35 Pretty close, right? 04:35 Well, obviously, Rene is not spelled the same way, and of course, that's why 04:39 we don't find this. 04:41 So once again, were using the MATCH function to locate the relative position or 04:46 the nonexistence of a match in a certain list. 04:49 We'll see it in another movie, how we can also use MATCH with other functions to 04:54 take this a step further. But many, many times the purpose of the MATCH function is 04:59 to simply display whether we have found something or not. Collapse this transcript
Retrieving information by location with INDEX
Using MATCH and INDEX together
 00:00 In this worksheet entitled MATCH-INDEX, we want to show you two different ways 00:04 to use the MATCH and INDEX functions together to pull out appropriate 00:09 information from a table. 00:11 In column A, we see the words "April" and "DVD Drives." 00:15 Eventually, we might even want to use data validation here to create a drop list 00:20 to allow us to be a little bit more flexible. 00:22 But let's just imagine we want to be able to change these and then come up with 00:26 the appropriate sales figure for that month and that item. 00:30 Let's first of all focus on April only to recognize its location here. 00:37 It's the fourth entry here. =match. 00:42 April is what we're looking for. Comma. And where are we looking? 00:46 In these cells right here. 00:48 By the way, we might want to choose total out there, so we're including that cell as well. 00:53 We need an exact match. 00:54 So what does this tell us? 00:57 April is in the fourth location of this range right here. 01:02 Similarly, with DVD Drives we also want to use the MATCH function to find that 01:07 bit of information--comma--in these cells. 01:11 Comma, zero. We're looking for an exact match. 01:14 Where is DVD Drives found in this list? 01:18 It's in the third position from the beginning of the list, if we choose exact match. 01:23 Using the INDEX function, we can refer to the cells we're looking at for the values. 01:30 They're right here. Comma. 01:33 And we're trying to get information out of the fourth row for April--comma--and 01:38 the third column--Enter--4565. 01:40 We're looking at April right here, and DVD Drives and where they intersect, 4,565. 01:49 That's the answer we've got. 01:51 Nothing wrong with doing this separately. 01:53 In fact, sometimes that's a good idea to build these separately. 01:57 What we might also do, after being more familiar with this, is essentially turn 02:01 this into one formula by taking this MATCH function, highlighting just this 02:07 portion of it, pressing Ctrl+C, and then Escape. 02:11 Let's plug it into this formula here. 02:14 So instead of this referring to B3, we'll make it refer to--as I press Ctrl+V--the 02:20 function as we saw in B3. 02:22 Obviously, it makes this longer, and you don't necessarily need to do this. 02:26 But if we really do want to turn this into one function, we will again do 02:30 the same thing here. 02:31 Take this information, edit it, copy just this portion of it, Escape, plug 02:37 that into here as well, instead of the B4, and press Ctrl+V to paste that information. 02:43 A much, much longer function, but now I have the complete answer, and we 02:47 don't need these cells. 02:48 So it's just a question of whether you want to do that or not. 02:51 We could put it over here or wherever. 02:53 Eventually put in data validation there if you wish, but it does show how 02:57 we can nest these functions in a creative way to pull out information from this table. 03:03 Now, over in columns L, M, and N imagine this represents a huge table, perhaps 03:10 on another worksheet. 03:12 In a different location, you've got a list of Social Security numbers, and you 03:15 need to look up someone's name. 03:18 You want to know who is associated with this particular Social Security number. 03:23 It so happens that we're trying to look up here--because your first thought 03:26 might be of VLOOKUP-- 03:28 we're trying to find this information, but it's not in the left column of a table. 03:33 Potentially, maybe we could move this to become the left column of the table, 03:37 but you can't always do that. 03:39 Maybe this table is controlled by someone else that's in a different workbook. 03:43 Maybe this table is used by a variety of different functions already, and you 03:47 can't alter the order of the columns here. 03:51 So we need to find out, first of all, where this exists in the list. 03:56 And we use the MATCH function here to find of this value--comma--in this list. 04:05 Now once again, we might be tempted to use the entire column, but it looks like we've 04:09 got an empty row above it. 04:10 Let's still try it anyway. Click here. Comma. 04:14 We need an exact match 0. 04:16 Is this Social Security number found, and if so, where? 04:20 It's in the twelfth position. 04:21 We see it right there. 04:23 We've got that match, but now let's go pick up the name. 04:26 We need to use the INDEX function to pull information out of the first column. 04:32 Here it's column L. 12th row. 04:35 So for the moment, we'll put this in a separate column and then put them back together. 04:40 So armed with this information, we know which row this is coming out of. 04:45 We need to use the INDEX function now. 04:47 So where are we looking? 04:48 Now the first thought might be, we're looking in these three columns. Well, not really. 04:53 All we're looking for is the name, so we're just looking in column L. Comma. 04:58 Which row are we looking for? 05:00 If it were only one case, we could type in a 12. 05:02 But we're looking for the information that we found here. 05:07 So we'll use this for the moment, in the 12th cell downward. And which column? 05:13 There's only one column here, 05:15 so we actually don't need anything, but it would be logically to put in an 1, 05:18 and that's okay, but actually we don't anything. 05:20 But at least this will work, and there is our answer is Thomas Watson. 05:23 If the range that you're using for the INDEX is only one column, we don't even 05:29 need the column reference, as I suggested, 05:31 so we'll take that out. There too. 05:34 Here too as in our previous example, it may make sense if you work with these 05:38 for a while to essentially do this all in one step. 05:41 Take this information here. Copy it with Ctrl+C and escape. Plug it in here 05:48 into this location. 05:49 We will do a paste. Enter. 05:52 We don't need this anymore. 05:54 So this use of INDEX and MATCH together, as in the previous example, allows us to 05:59 find information that we couldn't find by using of a VLOOKUP, the most common 06:04 kind of LOOKUP likely to be used when you have column or information. 06:08 INDEX and MATCH give us this potential to pull together information by using the 06:13 functions in a nested way. Collapse this transcript
5. Power Functions
Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF
 00:00 Sometimes you need to create quick summary totals on a worksheet. 00:04 In this worksheet called SingleCriteria, as we look at data here, you can 00:08 imagine the question popping up: 00:10 how many contract people do we have here? 00:12 How many full time people do we have? 00:14 We want to know this information. 00:15 There are about 300 rows of data here. 00:18 Sorting is not going to help us as that much. 00:19 It will pull them all together, but we want some quick totals. 00:23 There are three interrelated functions: COUNTIF, SUMIF, and AVERAGEIF. 00:28 They are very similar, but work slightly differently. 00:31 To get a quick count, we want to use COUNTIF. 00:33 Now maybe we want to put this information temporarily in column F. The COUNTIF 00:39 function: just based on its wording, 00:42 you can probably sense pulls together the concept of counting, along with an IF function. 00:47 It isn't strictly an IF function, but it uses that kind of logic. 00:52 So a different way to state this might be, in English: if an entry in column B 00:57 equals contract, then let's count it. 01:01 We use that as an example. 01:02 So where we are looking? 01:02 We are looking in column B. Comma. What are we looking for? 01:07 Two ways to do this. 01:08 We might within double quotes put in where we are looking for. 01:11 Now we can do this with numeric information, but first time look here, 01:14 we're looking a contract. 01:16 We are looking to see how many times this appears in column F. 78 times. 01:23 We have got 78 contract people here. 01:25 I have got a list over here of the 4 kinds of statuses that we have here, so 01:30 maybe we'd make this a little bit more expansive. 01:32 Instead of using this, why don't we simply refer to Half-Time over here? 01:37 Then we'll both copy this up and down. 01:39 So we have 36 half-time people. 01:43 We saw earlier it was 78 contract. 20 hourly. Drag this upward. 01:46 So what we have got here is four bits of information. 01:49 Just looking at this one for the moment, we are looking in column B, and how 01:53 often do we see what we currently see in H1--in other words, how many 01:56 full-time people do you see? 162. 01:58 That works out pretty nicely. 02:01 If it is a text entry, refer to it within double quotes as we first saw, or by 02:06 cell references we saw it this way. 02:08 Now, what if it's a numerical entry? 02:10 =COUNTIF. Looking now in column C. I doubt if you really want to find out 02:17 who is here exactly 10 years, but if you did, you could click column C, comma, 10. 02:25 I don't know if there are any, but we will find out. 02:26 16 people have been here 10 years. 02:30 I think more likely what you would want to be able to do here is to pick up a range. 02:34 You notice off to the right I have got this indicated here just for reference. 02:37 Suppose we want to know how many people have been here more than 10 years. 02:42 Here we need to actually embed this within double quotes. Double quote greater 02:48 than 10 double quote. 146. 02:54 So different ways to use this, and I think you can see how fast this is and how 02:58 straightforward it is. 02:59 Now the companion function to this, the first one is called SUMIF. 03:03 We might want to know, after having found out how many contract people there are 03:08 here, how much their salaries add up to? 03:11 The SUMIF and AVERAGEIF functions start with the basic concept of COUNTIF and 03:17 then extend it into different kinds of information. 03:19 For example, we are trying to find not just how many people are contract, we got 03:26 that part nailed already. Now, I am just going to click on cell H3 here to pick 03:32 up the word Contract. 03:33 Now we are about to say, every time we find someone who's contract status--comma-- 03:39 let's go into column D, grab that salary, and keep adding these up. 03:43 Or in other words, how much are we paying these contract people? 03:47 And sure enough, if we wanted to get this for the other list, will give the copy 03:50 that downward or change the reference, and so on. 03:52 So the SUMIF function has three arguments to it. It starts off in the same 03:57 way that COUNTIF does. 03:59 Here is where we were looking. Here's what were looking for--in this case contract. 04:03 Let's go to their salaries and add them up. 04:05 I am just going to copy this downward, make it a little bit simpler. 04:09 Now of course, what's that referring to? 04:10 That's the hourly people. 04:12 What if we want to know the average salary of the hourly people? 04:15 Instead of SUMIF we used AVERAGEIF. 04:21 The average salary of these hourly people here is 28,000. And once again, in 04:25 both cases of course, we could be looking at different criteria, but the 04:29 functions play out in similar ways. 04:31 I actually use AVERAGEIF a lot more than SUMIF. 04:33 AVERAGEIF is relatively new; 04:35 it was introduced in Excel 2007. 04:36 It has been around for a while. So these are good functions for tabulating 04:42 information typically out of a database kind of list of information. COUNTIF, 04:46 SUMIF, and AVERAGEIF. Collapse this transcript
Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS
 00:00 In this worksheet called MultipleCriteria, we want to show you three 00:04 interrelated functions. And just as there is a COUNTIF, SUMIF, and AVERAGEIF 00:11 function, there is also a corresponding set, ending in the letter S: COUNTIFS, 00:17 SUMIFS, and AVERAGE IFS. 00:21 These allow us to search for multiple criteria. 00:24 For example, we want to know the account of how many of our full time people 00:31 have a job rating of 5. 00:34 If we simply wanted to count how many people are full time, we'll use a COUNTIF. 00:39 And notice that I'm typing here we do see the functions play out here. 00:43 We want to use COUNTIFS. 00:44 I'll just click it here and then press tab. Pop that into place. 00:49 You'll notice here it says criteria range 1. Although we can't say the rest of it, 00:53 that's going to be criterion, even though it is plural there. 00:57 And then we can have another set and another set. 00:59 So, let's just start off simply here. What are we looking for? 01:02 We are looking for, in column B--comma--the full time people. 01:08 Double quote, Full Time. 01:12 This possibly could be referred to in a cell off to the right. Comma. What also 01:16 are we looking for? 01:17 We are looking in column C. Comma. What we are looking for here? Job Rating 5. 01:24 So the first pair is we are looking for the full time people and those who are 01:31 full time and have a job rating of 5; the two go together. 01:35 In other words, we are not going to find contract people with a job rating of 01:38 5 or full time people with the job rating of 3. 01:41 The full time people who have a job rating of 5, how many are there here? 32 of them. 01:48 We can have a 3rd pair, a 4th pair. 01:51 We can expand this. 01:52 How many of them have salaries over 60,000? 01:55 Another pair, possibly. Comma. What are we looking for now? 02:00 Column D. And what's the criterion here? 02:04 Comma. Within double quotes, greater than 60,00. No comma after the 60 in 02:10 there. Double quote. 02:11 So we are narrowing the list. We did have 32, because we weren't including the 02:17 salaries. And what have we found now? 02:21 In this list here, we have 16 people who are full time and who have a job rating 02:27 of 5, and they make more than \$60,000. 02:30 The two companion functions, SUMIFS and AVERAGEIFS, actually go a step further, 02:37 and they work a little bit differently. 02:39 Let's suppose that we would like to know the average salaries of our full time 02:46 people who have a job rating of 5. 02:49 =AVERAGEIF. Here too, by the way, I could have popped that into place with the Tab key. 02:55 But AVERAGEIFS, we start by indicating what? 02:58 The field we are trying to tabulate. Maybe we are trying to find the average salary. 03:04 There we go. 03:05 Even this says a range. Now we have pairs of criteria and multiple pairs perhaps. 03:12 So we are looking for the average salary of what, or of whom? 03:16 Those whose status is full time. So column B, comma "Full-Time". That's 1 pair. 03:26 We can stop right there. 03:27 That could be it, but let's put in another pair. 03:31 Job Rating, and then a comma, 5. 03:33 If it is a numerical value like this, you don't have to put it in quotes. 03:38 So we simply want to know the average salary for our full time people who have a 03:42 job rating of 5, and there it is, 59766. 03:47 By the way, if we want to know the total salary here of our full time people who 03:51 have a job rating of 5, we just change the word AVERAGE to SUM. 03:56 We would make it a SUMIFS. 03:57 It too works in the same fashion here, where 04:00 we begin with the field that we are tabulating. But here's a more involved 04:03 situation off to the right. 04:05 We have got a whole bunch of items here, and they need to be shipped. 04:07 They have got different sizes, they go to different zones, and here's the cost here. 04:13 We want to know, in this area right here, how much money we have spent for 04:17 shipping these packages based on the zone that they have gone to and the 04:22 size of the package. 04:24 So this is going to be a little bit more involved, but it is going to be using 04:26 the SUMIFS function. =SUMIFS. And we want to add up the data from column L. Now 04:35 because we would like to copy this function into other cells, and we want to 04:39 make sure that the information that we're adding is always coming out of column 04:43 L, we want to make that in absolute reference, so we press F4. Comma. In pairs. 04:49 What are we first looking for? 04:51 We want to make sure that we are adding up, in this row right here, just the 04:55 data for size 1s. 04:57 So we click column J. That's where the sizes are located. And here too, we want 05:02 this to be an absolute reference. Press F4. 05:05 What are we looking for in column J? 05:08 Those that are size 1. 05:09 I am going to click on P3 here after putting in the comma. Right there. 05:15 Now because we are going to be copying this down the column, we want to make 05:19 sure that it stays in column P, not row 3. So here we'll press F4 a few times, 05:25 using a relative reference, always making sure that this portion of our formula 05:30 is coming out of column P. So this pair right here means we're only trying to 05:35 tabulate information that's coming of the column J for size 1. Comma. 05:40 Now we have got another pair that we need to put in, and this relates to the shipping zone. 05:47 And so we are looking in column K. This too needs to be an absolute reference. 05:51 We will press F4. Comma. And now we need to make a reference to the row where 05:56 the zones or put in, and that's right here in row 3, up above. 06:00 I am going to click on Row 2 Q2, and press F4 in such a way that we indicate we 06:05 always want this to come out of row 2, so I am pressing F4 repeatedly. 06:09 You see the dollar sign here. 06:12 So we are finally finished. 06:13 What are we trying to do here? 06:14 We are trying to tabulate all that information out of column L and picking out 06:20 where the size here-- 06:22 that's column J--is equal to 1, and also out of column K here. The Shipping 06:29 Zone is from zone 1. 06:31 So I'm going to press Enter here and double- click to copy this down the column and 06:37 across the various rows here. 06:39 In another words, what we're saying here is we spent \$460 on various mailings to zone 6, 06:46 if the package was size 4. We might want to totals on the bottom here, and on the side. 06:51 You can certainly do that. 06:52 We did that very quickly here, just by highlighting these cells and pressing 06:55 AutoSum. We're pulling together the data in that way, 06:58 readjusting the column width here, and so on. We've got this information. 07:03 We see this grand total right here, if you highlight these cells here. 07:07 Of course, you're also seeing this down in the status bar at the bottom of the 07:11 screen, and of course, the real final check on this is to click column L. What's 07:15 the total shipping cost of all these items? There it is. 07:19 You see it: \$8,668.54. 07:23 It is the same total we have here. 07:25 So this table--somewhat painstakingly created--uses the SUMIFS function to gather 07:32 data from multiple criteria in columns J, K, and L. Collapse this transcript
6. Statistical Functions
Finding the middle value with MEDIAN
 00:00 In creating summary information from a list of data, it's not uncommon to want 00:04 to know an average, and also a median. 00:07 Median is a common measure. 00:10 I see it frequently used when talking about housing prices. And let's 00:13 contrast it with average. 00:14 What does MEDIAN do? 00:15 We are looking for the median salary here. 00:18 I am going to click column F. As always, when you can click a column instead of 00:22 highlighting cells, it's going to be faster. 00:24 What's the median salary here? 00:25 What does this mean? 00:26 What we will not see is the fact that Excel will take all the numerical data in 00:31 column F and sort it and pick the one out of the middle. 00:35 Now what if it's an even number of entries? It will take the two middle ones 00:38 and average them and come up with the number. 00:40 There's the median salary, the one that's in the in the middle. 00:44 Average, which can sometimes be misleading, particularly in smaller lists 00:48 typically is very close here, but you know the mechanism for average. 00:52 It's also called mean. Click here. 00:55 We are going to take all the salaries, add them up, and divide by the number of entries. 01:00 And an unusually high salary in this list might skew this. 01:03 So I see what's happening here. 01:05 But median is fast and easy, and we can use it for larger lists as well. 01:09 There is a list off to the right here, some 60 entries. 01:12 What's the median value in there? 01:16 Highlight the list. Enter. It's 497. 01:20 So it's from this list here, the 60 entries, it simply sorts them, finds the 01:25 middle two, and averages those to come up with an answer. 01:27 So that's the MEDIAN function, contrasted with AVERAGE: a commonly used Excel 01:32 statistical function. Collapse this transcript
Ranking data without sorting with RANK
 00:00 Here is a worksheet called Rank. And in columns A and B, we see Employee Names and Salary. 00:05 We would like to know the rank of each salary compared to the entire list. 00:09 We can easily do this by sorting, but there are times when you don't want to sort. 00:14 We want to keep this list in alphabetical order, but we do want to know the ranking here. 00:18 In Excel 2010, the RANK function, which has been around for a while, is still 00:24 available, but there are two variations on it. 00:27 For compatibility reasons, Excel has kept--and will keep--the Rank function. 00:32 But as I'm typing this, notice, what also we are seeing here on the pop-up tip. 00:36 There is a choice called RANK.EQ. 00:39 It's the same as RANK. 00:41 You can use either one. 00:43 If, however, you are in an environment where people are using Excel 2007, 00:47 you probably want to continue these RANK. But they work the same way. 00:50 RANK.EQ returns the rank of a number in a list of numbers, its size relative 00:55 to the other values. 00:56 If more than one value has the same rank, the top rank of that set of values is returned. 01:03 I think rather than worrying about what that really means, let's show you what it means. 01:07 So we use for RANK.EQ or RANK. 01:09 In this case, I will just choose RANK. 01:11 What are we looking at first? Cell B2, comma, compared with the entire list in 01:17 column B. Notice the word "order" out here. We are going to ignore that. 01:21 When we do ignore this, you will see how this works. 01:24 It works in what we call a descending order. 01:26 This is the 71st highest salary. Double-click to copy this down the column. 01:32 You can see here, here's the highest salary. 01:35 It has the order of 1. 01:37 Notice that two salaries are identical here. 01:40 They are sharing the second place, and that means that in this list here of 01:44 about 96 entries, we do not have a rank of 3. 01:49 These you could say occupy the 2nd and 3rd slot. 01:52 The next ranking we would see in order here would be 4. 01:56 We might temporarily see how this plays out, just by sort by salary. 02:00 Click here and I will simply click ZA on the Data tab here to sort these 02:05 in descending order. 02:06 Now remember, we don't need to do this, sometimes we cannot do this, but I want 02:11 to expose the salary rank column in a slightly different way. And there I think 02:14 you can see what's happening: 02:16 the 1, two 2s, the 4, and so on, and two 6s as well, but no 7, so they are 02:21 sharing the 6th and 7th position. 02:23 Some people prefer to show this in a different way. 02:25 I am going to use this column here to show you a variation on RANK, and that's 02:31 the one we will saw but didn't talk about: RANK.AVG. 02:35 If more than one value has the same rank, the average rank is returned. 02:39 Let's choose this variation here. 02:42 And the number we are looking at again is the entry out of column B. Comma. 02:47 We'll put B here, and we will just complete that. That's 1. 02:54 Now, we might have to change the display here. 02:56 Let's go the Home tab, choose the comma button here, and perhaps general format. 03:01 I am going to press Ctrl+Shift+Tilde to make this general format even more revealing. 03:06 You see the difference here. 03:07 This is using RANK.AVG. 03:09 Here's the more standard use of RANK. 03:12 So if you want it like this, there we go. 03:14 Since these are sharing the second and third positions, those numbers are 03:18 average. The average of 2 and 3 is 2.5. We see that's being displayed there. 03:22 It happens here too. 03:23 Four of these entries share the 11th position. 03:26 This is how it looks using RANK or RANK.EQ. 03:29 This is how it looks with RANK.AVG. 03:32 You can also reverse the order of these in all cases here by putting in 03:35 the third argument. 03:37 So in this example right here, if we put in comma, you'll notice the prompt here: 03:41 "Rank numbers as if reference were sorted in descending order," 0 or blank that's 03:47 what we saw earlier is Descending. Let's choose Ascending order here. Let me 03:51 just either tab in it or type a non- zero entry and then re-copy this, and you 03:56 see what's happening here. 03:58 And so this which had been first, now is at the very bottom. 04:01 So depending upon the kinds of numbers that you are looking at and the 04:04 information you are looking at, I think you want to experiment a bit with the order here. 04:09 Now again, we emphasize at the beginning here there are certainly times when you 04:13 do not want to sort the data. 04:15 So I am going to put this data back in the order that it had been, and we could 04:18 imagine having done everything that we did here without ever sorting the data. 04:22 But it did bring it up temporarily. 04:24 Sometimes when you're experimenting with this too, it might be easier to--if you 04:28 don't use it very frequently--to kind of get your bearings on this, work with the 04:31 smaller lists say lists like we are seeing over here and again experiment with 04:35 RANK and RANK.AVG. And with the smaller list, I think you'll have a better sense 04:39 of how it might work. 04:41 But there is no question that this is a valuable function for determining the 04:44 rank of numbers from a list, and potentially a huge list as well. Collapse this transcript
Finding the magnitude data with LARGE and SMALL
 00:00 Finding the highest value in the list is simple and straightforward. 00:03 You don't have to sort the list. 00:05 There is a function called MAX. 00:06 Here it's being used in cell I2 in this worksheet called Large. 00:11 What's the maximum value in column F? There it is. 00:15 We don't know where it is. We don't care about that for the moment. 00:18 We just want to know what it is. 00:19 And sure enough, for the lowest value, it's MIN, but what happens if you want to 00:24 know the second highest, or the third, or the fourth? 00:27 You might want to have create a list, possibly. 00:30 I don't think you would necessarily suspect that there is a function that 00:33 handles this, but there is. 00:34 It's called LARGE. And, of course, you could imagine there is a companion 00:40 function called SMALL. 00:42 Where are we looking here? 00:43 We are looking in column F. Comma. What are we looking for? 00:47 We are looking for maybe the second highest. 00:50 We will put in a 2. Enter. 00:52 That's the second highest salary. 00:53 If you needed to share the top five, one way to do this--and there are certainly 00:58 other ways possibly by way of a filter-- 01:00 we could put a 2 in right here, and then holding down the Ctrl key, simply 01:05 create a list down here through 5. And now I will make this reference here, 01:09 to cell J3 instead of the 2. And, of course, on this one, we will get the same answer. 01:14 Then we can simply double-click to copy down here, and now we have the second, 01:19 third, fourth, and fifth salaries. 01:20 So in each case here, we are simply using LARGE and if we use SMALL, of course, 01:24 we can easily reverse this. 01:26 We will get these from the bottom of the list, and those salaries that way. 01:32 So, easy-to-use functions, the kind of function when I first saw this, I thought I 01:36 will never use this, but every so often I seem to need it, and it comes in 01:39 handy. And there's no other way to do this other than by sorting the data. 01:42 So it's a potentially powerful tool. Easy to use, too. Collapse this transcript
Tabulating blank cells with COUNTBLANK
 00:00 In this worksheet called COUNTBLANK, column G has some blank entries. 00:05 Not everybody in this list has benefits. 00:08 We want to be able to tabulate this information. 00:10 Now certainly we can use a filter to show just those people with benefits or 00:15 just those people without benefits; 00:17 there is a built-in capability of filtering to do that for us. But sometimes we 00:21 just need to tabulate the information. 00:23 We want to know how many blanks are there. 00:25 So there is a function called equal COUNTBLANK. 00:27 This is one of those longer functions, so as we type =C, perhaps we will 00:31 find in the list here, scroll down, use the Tab Key, pop it into place there. 00:36 There it is COUNTBLANK. Press Tab. There it is. 00:40 And where are we are looking? 00:41 We are looking right here. 00:42 Now you might be tempted to click column G--and I will, in a bit--but let's just 00:46 highlight the data here. 00:47 It's about a hundred rows, so doesn't take too long. 00:49 How many blanks cells do we have in here? 00:51 Enter. There we go, 28. 00:54 In other words, 28 people here do not have benefits in this list. 00:59 I mentioned that we might possibly want to click column G as we do this, because 01:03 we are looking in column G. But I will point out here--and you'll see 01:07 immediately, of course, what's happening-- 01:08 this is looking through the entire column and all those cells below our data 01:13 here, which are empty, are going to get counted. 01:17 So that's a huge number. 01:19 Now for point of contrast, we see up here how many rows there actually are in 01:24 an Excel worksheet in Excel 2010. And it's pretty close to this, but there's a difference there. 01:30 Now the difference, you can probably quickly see, is 72, and that's not what we want either. 01:34 So if you wanted to use the entire column reference here--say the list were a 01:39 few hundred, a few thousand-- 01:41 instead of dragging over this, you might want to piece together a formula. 01:45 It will be a little tricky at first, but what you want to recognize is that if 01:50 you have got data here where all of the cells within a list are occupied for a 01:55 given column, like column A here, 01:58 we could build a formula that uses that particular capability. 02:01 It might look something like this. 02:03 We start with this idea here--in other words, we need this information--but 02:08 along with it, we would want to subtract the information that we see up here. 02:13 So I am going to put in K1 right there. Minus. 02:16 Now we are still not there, because if we subtract, that's not really the 02:20 answer we are looking for. 02:22 So the other item that we need to put into the mix here is simply a function 02:26 that will count--COUNTA--the number of entries in one of our other columns. 02:32 It can be column A, column B, any of these up here that have complete entries. 02:36 I will just use maybe column B here, 02:39 and then put in a minus. And we are not quite there, but let's see what answer 02:44 we get here, and you know that's not correct. 02:47 So what do we need to do? 02:48 Simply put in left parenthesis here and at the end. 02:53 Now this is a little bit contrived perhaps, a little bit more than you thought 02:55 we needed to do here, 02:57 but if the list that you're trying to use here is quite large, this saves 03:03 dragging across a huge list to come up with the cells that you want to look at. 03:08 That's we did up here, and there it made sense, because it wasn't so large. 03:12 But here, even though this is a little bit involved, it will get us there using 03:16 the COUNTBLANK Function in combination with that total row count and the 03:22 COUNTA Function. Collapse this transcript
7. Date Functions
Understanding Excel date/time capabilities in formulas
Identifying the day of the week with WEEKDAY
 00:00 In this worksheet called Weekday, we want to know the day of the week for the sales. 00:05 Imagine this organization sells heavy equipment. 00:07 They don't have sales every day. 00:09 We're trying to see if there's some kind of pattern about which days of the 00:12 week sales occurred on. 00:14 Excel has a function called WEEKDAY. 00:19 We want to know the weekday of this cell in A2. 00:23 The answer is a little disappointing. 00:24 2 actually means Monday, 1 means Sunday, 3 means Tuesday, and so on. 00:30 And we can certainly get used to using the numbers, possibly. 00:34 Here's how they play out in the rest of the worksheet. 00:36 This is on Friday, here's the Friday, here's the Thursday, and so on. 00:39 You can get used to using this, but it certainly would be better if we could 00:43 format these--and we can--to display the actual day of the week, either as an 00:48 abbreviation or as a full spelling. 00:50 By either right-clicking, pressing Format Cells, or pressing Ctrl+1, we will go 00:56 to the Format Cells dialog box, and the not so obvious choice here is Custom. 01:02 What we want to put into the panel right here is either three Ds, if we'd like 01:07 to see an abbreviation like MON and TUE and so on, or four letters to spell this out. 01:13 Click OK. 01:14 I think most people would prefer this idea. 01:16 It's a lot more readable. 01:18 Now remember, the function is still there, and it's still doing its work 01:21 like before, but instead of showing numbers, we're seeing actual text, which makes sense here. 01:25 So this helps us a bit in understanding what's going on. 01:29 And imagine how this can be applied to other kinds of data that you work with. 01:33 Sometimes you'll see some real surprises here about what's going on. 01:36 Does it have to do with sales? 01:38 Does it have to do with orders? 01:39 Any particular day of the week, that's just kind of jumping out of here in an unusual way? 01:44 And once you are familiar with the WEEKDAY function, you can apply it to other 01:48 Excel features as well. 01:50 In column F are some sale dates, and let's imagine that, manually, shipping dates 01:56 are entered here, but we want to be sure that no shipping date is assigned here, 02:00 let's say Saturday or a Sunday, because you don't do shipping perhaps on those days. 02:06 So, what we'd like to be able to set up here, and we can, is a data validation 02:10 rule that uses the Weekday function. 02:13 So I'm going to click column G here, and on the Data tab in the Ribbon, use Data 02:19 Validation. And the word "Allow" here, every time I see it, I want it to say 02:23 require, because what we're about to do here is to require that the setting in 02:28 column G adheres to a custom formula that we're about to write. 02:33 This formula is certainly not obvious, but once you see it, it will make 02:37 perfect sense. And of course, what we're aiming for here is to prevent Saturday 02:42 and Sunday entries. 02:44 We will start by just actually using the WEEKDAY function. 02:47 Now, you don't have to really capitalize it; 02:49 it will become capitalized eventually anyway. But the core of this is the 02:53 following idea: the weekday of what? 02:57 Strangely enough, G1. 02:59 Now, G1 is currently the active cell, but we have highlighted the entire column. 03:04 By inference, G1 will refer to every cell in the column. 03:09 That's all we need to type. 03:10 So, if the weekday of G1--we want to make sure that this is not equal to, and 03:17 that's the less than followed by the greater than arrow. 03:20 We want to make sure that this is not equal to 7. 03:23 That's Saturday. Comma. 03:25 And so we need to precede all this with =and. Two criteria here. 03:32 The first one is the weekday of G7 not equal to 7. 03:35 Now, I'm just going to highlight this here. Because I'm a slow typist, I'm going 03:39 to press Ctrl+C, click over here, Ctrl+ V. We also want to make sure that the 03:44 weekday here is not equal to 1. That's Sunday. 03:47 Now, of course, you can do these in any order you want. And then a right parenthesis. 03:52 So, even though that's not--as I suggested--not the most obvious formula--this 03:56 is exactly what we want to do here. 03:58 Now, one more slight glitch here. 04:00 As we click OK, we're going to get a message that threatens us a little bit: 04:04 The formula currently evaluates to an error. 04:07 Now, that simply means that literally cell G1 doesn't fit this. It doesn't make any 04:13 sense here, but we're ignoring that. 04:15 It's got text in it. 04:16 We just click Yes and move on. 04:17 All right, so here's the Shipping Date. 04:19 I'm going to put in a date: 1/23/10. 04:20 Is that okay? Yes. 04:25 Oops! No, it's not. 04:26 Now, we don't necessarily know, based on this message, what went wrong. 04:31 If you're the one that created it, you might, but you might want to consider 04:34 rewriting the message here. 04:36 So, for whatever reason, that's wrong. 04:38 Let's try another date. Let's retry. 04:40 Let's try the 22nd. 04:42 Looks like that's okay. 04:43 Now, if we were curious, of course, we could figure this out kind of quickly. That's a Friday. 04:49 It looks like that previous day was a Saturday. 04:51 So, you saw how it works. 04:53 If you'd like to control what the message actually says--and we do this for the 04:58 whole column--revisit Data Validation, and if you want the message to be 05:02 meaningful, you can jump right into Error Alert here, and that's okay. 05:06 You see this prompt again. 05:08 We'll provide the message, and something along the lines. 05:11 I'm being a little cryptic here. 05:12 Data entered is Saturday or Sunday, is Saturday or Sunday - re-enter. 05:19 Now, some people of course get a little gleam in their eye when they realize 05:22 this is going to pop up on the screen in the dialog box. 05:25 Time to get clever or cute or whatever, but this is a direct message, and we 05:29 click OK. And again, we see this, but we'll try this again. 05:32 This time we will put in the 23rd just to see that message. There it is. 05:36 So you do have control over that as well, but the key idea is using this WEEKDAY 05:41 function in this data validation rule, to make sure that we're not putting in 05:45 dates that are on a certain day of the week. And we saw how valuable that 05:49 function was used also in column B here. 05:51 So it's a function to keep an eye on. 05:53 Anytime you're analyzing data--whether it relates to orders, or sales, or even 05:57 in a non-business environment-- knowing day of the week is a valuable tool. Collapse this transcript
Counting working days with NETWORKDAYS
 00:00 In this worksheet called NETWORKDAYS, we're going to use a function called-- 00:05 strangely enough--NETWORKDAYS. 00:07 We want to know how many working days exist between the two dates. 00:11 Now, notice that I've put in these dates twice in two columns here. 00:16 We want to show the basic way of doing this, and a reminder for some of you that 00:19 we simply can subtract two dates to get the number of days. 00:23 This is not ultimately what we want, but for contrast, I'm going to show the difference. 00:27 Equal a later date minus an earlier date. How many days between the two here? 00:32 Counting both the beginning and ending date, 95 days. 00:36 Well, in most environments, we're not talking about a seven-day working 00:41 week, but a five-day. 00:42 So let's use the function and remember on longer functions you might as well 00:48 click here. And by the way, NETWORKDAYS has an addition in Excel 2010. 00:53 You may want to explore a function called NETWORKDAYS.INTL, meaning 00:58 international, because weekends occur on different days in different parts of the world. 01:03 We're going to use NETWORKDAYS. Just click it here and press Tab. 01:07 The starting date is A3, comma, the ending date is here. 01:12 For the moment, we're going to ignore the holidays. 01:14 How many working days between those dates? It's 68, 01:18 not counting Saturdays and Sundays. 01:21 Now, some holidays occurred between these two dates. 01:24 Let's do this again. 01:24 I'm simply going to copy this downward. But this time I'm going to make an 01:28 adjustment, and you'll notice in column E, we have some holidays, and we can 01:31 refer either to the entire column or maybe just the relevant dates, either way. 01:36 What are the work dates overlapping here? 01:37 What do they involve? 01:38 This chunk right here. 01:39 So do it this way or highlight the whole column, either way. 01:42 Make sure we include the ones that are relevant. 01:45 And now, what's going to be the difference? 66 days. 01:48 And so we can see how this will play out over projects, for example, that cross 01:52 holidays here, and so on. 01:54 The difference being sometimes you want to focus on the holidays, sometimes 01:58 not, but it quickly gets us to where we need to get. 02:02 Now, if you're trying this on a short timeframe--for example, I'll just make 02:06 up a date here, 11/8/10 and 11/15/10. 02:14 Now, those are two consecutive Mondays. 02:17 Let me just copy this particular formula down here. 02:20 I'll do this by dragging with the Control key, put the formula right here. 02:24 Now, that's 6 days. 02:25 Do recognize that it's counting the beginning and the ending day. 02:29 I think most of the time that's probably what you want to do, but if this 02:32 project started on noon on one Monday and ended on noon on the following Monday, 02:37 well, that's only five days. 02:39 So, here and there as you use this function, you might want to subtract one. 02:43 But in other respects, it's a great function. 02:45 It gets the job done, it quickly lets us know the amount of working days--not 02:49 counting Saturdays and Sundays--and more appropriate, not counting those holidays as well. 02:53 And one small point here too: if a holiday falls on a weekend, as July 4th did 02:59 in 2010, it does not get discounted twice, but just once, because it's a 03:04 weekend day. Collapse this transcript
Determining a completion date with WORKDAY
 00:00 In tabulating the length of projects in many typical work environments, we don't 00:05 want to include weekend days. 00:07 Let's must remind ourselves that the function called WORKDAY-- 00:10 and we're on a worksheet called WORKDAY-- allows us to tabulate an ending date. And we 00:16 may or may not want to include holidays here, but lets just point out that if we 00:20 simply begin a project on June 10, 2010, and it's 60 days, we could easily make 00:27 the mistake of simply taking these two cells and adding them to come up with an 00:32 answer. But that certainly does include weekend days. 00:35 If you aren't a seven-day work environment and the project starts on June 10th, 00:39 and it's 60 days long, then the last day truly is August 9th. 00:44 But let's talk about the idea that in many environments it's a five-day week. 00:48 So we want to without painstakingly counting those weekend days and not including 00:53 them in our list here, we will choose =WORKDAY. 00:57 Here's that starting date. Comma. Here's the project length. 01:01 I am not counting Saturdays and Sundays this time. 01:03 When will the project end? 01:04 And there we see it. On September 2nd. 01:08 Now there are some holidays involved here, and we have got a list of holidays in 01:12 column A. We can either highlight the relative ones as we use this function-- 01:16 I will just copy this down and re-adjust it-- 01:18 but we want to use the WORKDAY function here to not include those holidays as well. 01:23 Now July 4 in this particular entry here has a weekday. I'll use this function. 01:31 Weekday of 1: it's a Sunday. 01:32 That's not going to get discounted twice, but we do want to make sure that other 01:36 holidays that might occur here are not going to be included--maybe labor day. 01:40 So editing this function, comma, let's not include the holidays here. 01:45 And because I included the A1 there, that didn't work, so we just want to use the relevant days. 01:51 You can highlight all these, even though it includes dates well beyond the range 01:54 we are interested in. There we go. 01:55 So it's only a one-day difference, and that's probably because July 5th, which is 02:00 on Monday, was a holiday in this particular working environment. 02:04 So this plays out very similarly to the NETWORKDAYS function that you might've 02:08 seen in a previous movie. 02:09 But WORKDAY allows us to calculate not only project lengths, but I think you can 02:13 imagine using this with other similar kinds of data as well. 02:17 It doesn't count weekend days and where appropriate also omits holidays 02:22 in the calculation. Collapse this transcript
Tabulating date/time differences with DATEDIF
8. Math Functions
Working with rounding functions
Finding the remainder with MOD and using MOD with conditional formatting
 00:00 If you have that extra moment and you're exploring some of the many functions in 00:03 Excel--most of us don't have the time to do that-- 00:06 but if you went to the Formulas tab and were looking at some of the math 00:09 functions, I don't think you would be initially attracted to, or have any reason 00:14 to go to, a function called MOD. 00:16 If you see the description, it looks a little vague: "Returns the remainder 00:20 after a number is divided by a divisor." 00:23 And why would you ever want to use that? 00:25 That was certainly my rationale for many years. 00:27 Then I saw a situation where it was the perfect function. 00:31 Here's a situation here where in column A we've got the names of some items, and 00:35 we've got so many of them left over. 00:37 There's 162 of these. Maybe this company is consolidating or it's moving its 00:41 distribution center. 00:42 They have got 162 of these items. 00:45 Now these items are all different, and they do have containers that we can put 00:49 them into, but for this particular item we can only get 16 per container. 00:54 If we start using the containers to put these items in here, how many are we 00:58 going to have left over? 01:00 In other words, if we divide this by 16, you know it's not going to come out even. 01:04 We are going to have two of them left over. 01:06 And of course, we want to take care of all these at once. 01:08 So what we are talking about here? 01:09 The function called MOD. 01:11 It calculates the remainder in a division. 01:14 Here's the number we are looking at. Comma. Here's the divisor. 01:18 It's not going to tell us how many boxes, 01:20 how many containers we need; 01:22 it's going to tell us how many are going to be left over: 01:25 two. Of course, some of these you can do the math in your head; others you can't. 01:29 So let's double-click. Copy this down the column. 01:31 Here and there, of course, we have got a perfect fit. 01:33 168 divided by 24 is 7. 01:36 We don't care about that, but how many are left over? None. 01:38 You see what's happening here. So it's an ideal use. 01:42 It's dividing these and then calculating the remainder for us. 01:45 How many items are we going to have left over? 01:48 I guess we have got quite a few. 01:49 One more and we could fill up a container. 01:52 Now there's another application here too, and not so obvious. 01:55 Sometimes you want to format a worksheet so that every other row is of a 02:00 certain color possibly. 02:01 Many of you know that perhaps the better way to do this would be to go to the Insert tab-- 02:06 you can also get the here on a Home tab--and turn this into a table. 02:11 Now you may not want some of the side features, 02:13 but we see what's happening here. 02:14 And if you don't like those colors, well, go for it. 02:16 You got 62 other choices out here. 02:19 Have fun with that. 02:20 Let me press Ctrl+Z to undo this. 02:22 But sometimes you want control of it: Maybe you are going to be printing this. 02:24 You might be using it for display. Maybe you want every fifth row to be a 02:28 certain color, or every tenth row, or whatever. 02:30 Let's select the entire worksheet and use conditional formatting in combination 02:36 with the MOD function. 02:37 This is on the Home tab. 02:39 Conditional formatting is not build in to any of the standard capability here, 02:43 but we do have the ability to create what's called a new rule. 02:48 In this particular dialog box here, choose Use a formula to determine 02:52 which cells to format. 02:54 Now in English, here's what we are about to say. 02:56 If the row number of a cell is evenly divisible by 5--and of course that means 03:01 row 5, row 10, whatever--then we want to make that row yellow, blue, or 03:05 whatever we want equal. 03:07 Now the entire worksheet is selected, but the active cell is in cell A1. 03:11 So that's the cell we will use in the rule. Not an obvious thing, but once you 03:15 use it, you will get comfortable with the idea. 03:17 Equal. And we want to use the MOD function. 03:20 Then row, the row number of the cell A1 comma 5, meaning we want to take the row 03:28 number of every single cell in this worksheet, one by one as it gets 03:33 encountered, and essentially divide it by five. And when the remainder is 0-- 03:38 in other words, that's when it's evenly divisible by 5--we don't put in 03:42 the division symbol. 03:43 When it's equal to zero, then it's evenly divisible by 5. 03:47 Then we want to apply a format. 03:49 Let's just say yellow. Click OK. 03:51 Again, the more you look at this, the more it makes sense. But if you only see 03:55 it occasionally, you have to kind of think it out a little bit. 03:57 Even though we're only using A1, we have selected the entire worksheet. Think of 04:02 it as a surrogate or substitute for all the cells in the worksheet. 04:05 Every time every single cell is evaluated, its row number is divided by 5 and if 04:11 the remainder is 0, we are going to make that cell yellow. And there it is. 04:16 Of course, if you want every fourth row or every third row, you just tweak that 04:20 slightly and you use the number 3 or 4 instead of 5. 04:23 So you see a different use of the MOD function. 04:26 So either in that example, or in the example we used here, sometimes you will 04:30 encounter functions and have no idea where you might want to use them, and yet 04:34 this makes perfect sense. 04:35 If you are a mathematician, you might easily gravitated toward this little 04:39 faster, but nevertheless it works just great. Collapse this transcript
Building random number generators with RAND and RANDBETWEEN
 00:00 There are two interrelated mathematical functions called RAND-- 00:04 that's the name in this worksheet, by the way--and RANDBETWEEN. And although they 00:08 appear to have specialized uses, 00:09 there are two common uses that you might to consider. 00:12 Here's a database-type list. It might be thousands of rows. 00:15 Sometimes with lists of data, we need to do some casual spot-checking. 00:20 We could be a little bit more methodical about that. 00:22 But the point might be that we might want to randomize the order of this 00:26 list: Maybe our idea of quality control is simply to review three or four of 00:31 these every month or so. 00:32 Maybe this has to do with orders. 00:34 Pick a column to the right here, an empty column, and put in the function =RAND. 00:39 This is one of those functions that has parentheses but nothing between them, and 00:42 when you're typing this you need only put in left parenthesis. 00:45 This will generate a random number between 0 and 1. 00:47 Now, mathematicians have all kinds of usage for this. 00:51 It doesn't make any difference how it's displayed. You can use the Comma button if you really care. 00:55 That's not really important, but you could do that. 00:58 As we copy this down the column, we are putting a different random number 01:02 in each one of these. 01:03 Now, we might sort this list based on what's in column I. We do want to give it 01:08 a temporary heading here, but just put in rand or rr or anything, just to make it 01:12 match up with the others. 01:13 By clicking here and sorting this list-- and we can do it really fast off of the 01:17 data tab by choosing AZ or ZA-- 01:20 we are going to rearrange the order of the list based on those entries that are in column I. 01:23 And as you look at the list now, it appears to be in no apparent order. 01:28 It's certainly not in order by Social Security number, nor department, and so on 01:32 as we move across here. 01:34 We want to sort it again? 01:35 We can just click this A all over again. 01:38 Every time we do this, the numbers get regenerated. 01:40 So ultimately, we don't leave them here; we get rid of it. 01:43 So I am going to just click here and press delete. And again, it's the function called RAND. 01:47 Now there is another situation. I perhaps am more likely to use this than some of you, 01:51 but I'm setting up a situation here. 01:53 I need some sample data. 01:54 I need it kind of quickly. 01:55 And the data I'm working with here perhaps has to do with sales or profit. 01:59 It's for group here. 02:00 We might be talking about items or dollars. 02:03 I just want some random numbers here, 02:05 so I can work with the data a bit, maybe turn it into a chart. 02:08 I want a bunch of numbers between say 100 and 900. =RANDBETWEEN, a lower number-- 02:15 let's say its 100--comma, a higher number, 900. 02:19 Now since I highlighted all these data at once, a nice little shortcut here is I 02:23 am going to press Ctrl+Enter, and we have got a bunch of random numbers. 02:27 Now previously, this was formatted in a strange way. I forgot about that cell? Just 02:32 on the Home tab here, click. Probably don't need the decimals. 02:36 There we go, looking like that: 02:38 a bunch of random numbers. 02:40 Now here, too, as with RAND, these are dynamic. If I click down here or anywhere and 02:45 do something else or use another 02:47 command--I am just going to press a number and press enter here--and watch: all 02:50 those numbers changed. 02:51 So if you want to essentially freeze these--because they're still functions and 02:55 every time you make a worksheet change they change, 02:57 if you are going to be using these for a sample or something--simply turn 03:01 them into pure values by using the right mouse button after you've selected this range. 03:06 Drag this elsewhere, and then right back on top of itself with the right mouse 03:09 button, Copy Here as Values Only. 03:12 So I've simply turned them into their pure results. 03:14 So anytime you need sample data, data within a certain range, use the 03:18 RANDBETWEEN function. 03:20 And here's another way to use it as well. 03:22 You can use us for dates. 03:24 I need some sample dates here between these two dates. 03:27 Maybe it's just for these cells =RANDBETWEEN. 03:33 Now in this case here, what I really need to do is make this column wider so we can see it better. 03:37 There we go. =RANDBETWEEN. Here's our starting date. 03:43 Now since I am going to copying this down a column, 03:45 I am going to press F4 here because I want that date to stay there. Comma. Here's the 03:50 ending date. Also press F4 to make that an absolute address. Enter. Maybe copy it down here. 03:56 A random date between the two. 03:58 We can change the format to be anything we want. 04:00 So all these dates are going to be in the year 2010 or the year 2011, 04:05 and possibly even day one of 2012, because that's in the list as well. And for the 04:10 moment, again, this is live. 04:13 It's active, you could say. Its dynamic. If we make other changes anywhere in the 04:17 worksheet, all these will change. 04:19 So possibly you want to leave it that way, but if you want change them, 04:22 just like we did before, highlight these with the right mouse button, drag them 04:26 into another column or row, right back on top of themselves, let go, Copy 04:31 Here as Values Only. 04:32 So using RANDBETWEEN and RAND, two mathematical functions, give us some tools for 04:38 not only creating data, but also adjusting and sorting data as well. Collapse this transcript
Converting a value between measurement systems with CONVERT
 00:00 If you work with certain kinds of engineering or math worksheets, you will need 00:05 to use--and frequently use, perhaps--the CONVERT function. 00:09 On the screen here are some of its capabilities in columns G and H, and it's 00:14 the ideal function for making conversions across different kinds of measuring systems. 00:19 Certainly a common one for those who live in the United States particular near 00:22 Mexico or Canada is this conversion from kilometers to miles. 00:27 In cell B2, for example, we might want to see what 180 km is equal to in miles. =CONVERT. 00:36 Now there is an improvement in this function, too, in Excel 2010. 00:38 For example, after selecting the item that we are trying to convert, comma, then we see 00:45 a rather substantial list of items to pick from. 00:49 Now strangely enough, on this first example, we're not seeing the one we want; 00:53 it's for kilometers. 00:55 However, there's one for meters and all we can do here to make this slightly 00:59 faster--although you certainly could have typed it by now--is to click Meter, 01:03 tab this into place, put a K in front of it. 01:06 In another words, we are taking the 180 and by indicating here, that's 01:10 the kilometer measure. Comma. 01:12 We want to convert this to miles. 01:14 There's miles right there. Click there. Tab. 01:17 So 180 km is 111.8 miles. 01:21 This would be the reverse over here. 01:22 We have got 89 miles. What is that in kilometers? 01:26 Of course, the same general idea. =CONVERT. 01:28 There is the item in question now. Comma. 01:31 Now we are going from miles, and that's right here. 01:35 We could tab that into place. Comma. And now we want to go to kilometers. 01:39 Again, we are not seeing it, but we are seeing meter. 01:42 Same idea, in reverse of course, with the K. 01:45 Certainly another comma, and the same general idea. Of course, Celsius to 01:48 Fahrenheit, that sort of thing. =CONVERT again. 01:52 Here is the number we are trying to convert. 01:55 In this case, that's the Celsius value-- we will see that in here--and Fahrenheit. 02:04 Now many of you, of course myself included, probably typed "F". 02:06 That's going to work just as well. 02:09 This must be capitalized. 02:11 So there we go. 37 degrees Celsius is 98.6 Fahrenheit. 02:16 Again, off to the side, we will see this list. 02:19 Now if you want the full list, I would recommend that at least once maybe click on 02:23 an empty cell, click the Fx button, or perhaps better yet, on the Formulas tab, 02:29 pick More Functions, and it's found under Engineering. 02:32 It's a math-like function, but it's found in the Engineering group, right there. 02:38 If you click Help on this function, and particularly if you are online, you will 02:41 see a huge list, eventually, of all the various measuring systems related to 02:46 weight and mass, distance, time, pressure, force, energy, power, magnetism. 02:53 You see all these here, Temperature, Liquid Measure, and the various prefixes 02:57 that you can use as well. 02:59 So it's quite extensive and ideal for those using mathematical and/or 03:03 engineering applications. The CONVERT function. Collapse this transcript
Using the powerful new AGGREGATE function to bypass errors and hidden data
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 A commonly used statistical measure is what we call a frequency or 00:04 frequency distribution. 00:06 In this list of salaries--we've got hundreds of them--we might want to see a 00:10 breakout based on an arbitrary list, for example, as we see in column D every 10,000. 00:16 In column E, what we would like to get is a count of how many of these salaries 00:20 are below 10,000, up to and including 10,000 in this cell; and in this cell, those 00:27 salaries above 10,000 up to and including 20,000; and so on. 00:31 I'd emphasize again the fact that this list here is arbitrary; 00:35 they don't have to be equally spaced. 00:37 They could be every 5,000, or they could be an unusual interval. 00:41 But we want our results to go into all of these cells, and we've scoped out the 00:46 range of salaries here that indicates the complete entries. =FREQUENCY; 00:51 there it is. 00:52 Tab it into place. And the first entry here is all of these salaries. To make this faster, 00:58 I'm going to click in cell B2, hold down the Shift key, and press Ctrl+Down Arrow. 01:03 It goes because all the way down to row 621. Comma. The cells that we are looking at 01:10 that define the intervals are referred to as a bins array. 01:14 I'm going to highlight these cells right here. 01:17 Since this is an array function, I'm going to press Ctrl+Shift+Enter, and 01:23 we're going to get answers in every one of those highlighted cells. 01:26 And there's our count. 01:27 Four of these salaries are up to and including 10,000. 01:30 Now, just to emphasize how that break occurs, I want to take this salary here 01:36 and make it to be 10,000 exactly. 01:38 Currently, it's in the list below; 01:40 it's counted in those 32, but as I make this 10,000 and press Enter, watch the 01:45 two numbers change into 5 and 31. 01:48 See what's happening here. 01:50 Now, this could be the start of other kinds of analyses. If we simply were to 01:54 highlight this and press Alt+F1, we're going to get a chart here and 01:59 potentially this is a histogram. 02:01 We can alter this, make the columns wider, and label it properly, and so on. 02:05 But what's happening here is that the cells in column E all have the same entry. 02:11 If you start clicking on separate cells here, notice how--and you can see it 02:14 in the Formula bar--they are identical. 02:17 So that looks a little strange at first. 02:18 If you've never seen these, that would throw you a little bit. 02:20 But you saw how easy it was to create the list. 02:24 Now, I wouldn't suggest that you'd be constantly manipulating this, but if, for 02:27 example here, if you jump in and start changing the intervals, you will get 02:31 different answers. But what if you say, well, I want to do this every 15,000, 02:36 something like that? 02:37 So this is going to be maybe 15, 30, 45, 60, 75, 90, 120. Maybe we don't need 02:45 these. Could we just get rid of those? 02:46 You'll get a message like this: 02:48 "You cannot change part of an array." 02:51 So if you want to restructure this and have more or fewer entries here, you'll just 02:55 you have to delete all of this at once and then redesign it. You saw how quickly 03:00 we were able to create this list. 03:02 It's using an array function called FREQUENCY. Collapse this transcript
Flipping row/column orientation with TRANSPOSE
 00:00 We're looking at a worksheet called Transpose, and it's a reminder that there 00:05 are two features in Excel that go by that name. 00:08 One is the ability to actually transpose a list of data, changing its 00:13 row-column orientation into column-row, or vice versa. There's also a 00:17 function called TRANSPOSE. 00:19 Let's look at both of them. 00:21 Here is a list. Perfectly reasonable. 00:24 We might want to continue and alter this a bit by including the remaining six 00:29 months of the year, and that means it's going to be quite wide. And the thought 00:32 occurs, from time to time: would this have been better had we put the months down 00:37 in column A perhaps. 00:39 Well, first of all, without in any way destroying this, let's consider and 00:43 show how we can transpose this data in such a way that we will see it in these 00:50 three columns down here. 00:52 Now, is this going to be a better layout? 00:54 Maybe, maybe not. But it's easy to explore, and I found myself using this over 00:59 the years in a variety of situations, and not necessarily always accepting the 01:04 newer view or the transposed view, but it's easy to get to. 01:07 Let's highlight this data, right-click, and copy. 01:11 We never want to paste this on top of it. 01:14 Say, right here, we'll just right- click and Paste Special. And in 2010, 01:20 we've got these Preview buttons that are going to be helpful, and there's a 01:23 Transpose button right there. 01:25 You see what's happening down below. 01:26 That's exactly the same data we have from above. 01:29 So I want to click this. And not only has the data been transposed, but the 01:34 formula up here in H4, which tabulates or as the data from the left has been 01:40 transposed into the formula here in B15, that takes those same numbers and 01:46 adds them from above. 01:47 Now, you are not going to keep both of these around probably, although you 01:50 could. But they are not linked together in any way. But it does suggest, and I 01:56 think sometimes you want to experiment with this, sometimes the way you lay out 01:59 data, particularly if you done it in a hurry, might not be the best way, consider 02:03 transposing it and then decide which one is better and go with that one. 02:08 I sometimes try this feature on large lists as well, just to get a different 02:12 perspective on things. And you can certainly transpose the data onto a 02:16 different worksheet. 02:17 So that certainly worth exploring. But I am going to press Ctrl+Z here to undo 02:21 this and talk about a function called TRANSPOSE. 02:24 It could be that you would like to have a transposed version of this that is 02:30 linked to this original data. 02:32 It might be on a different worksheet. 02:34 As we set up, maybe we'll just put it down here. 02:36 Now the first thing we do here is to highlight this range, and as we do, if you 02:41 look to the left of the formula bar in the Name box, you will see that it says 02:45 4Rx9C, meaning four rows by nine columns. 02:51 Once we've seen that, we want to highlight the reverse range. Not four rows by 02:57 four columns, what do we want? 02:59 We want nine rows by four columns. 03:03 Now, we're going to type =transpose( and refer to the original range. 03:09 Now this is an array function, meaning we press Ctrl+Shift+Enter, and there we 03:17 have the information. 03:18 Now, it needs to be formatted, but this cell here is not really 1570; 03:24 it's--what is it? 03:25 It's a reference to these cells up here. 03:28 As we look at this though, it doesn't refer to the specific cell. 03:31 It refers to, as I double- click, all of those cells. 03:34 So it looks a little bit strange. 03:36 Now, I have to admit, over the years, I've only used this once or twice, but 03:40 it's the stuff of some creativity, and perhaps this would make sense on a 03:44 different worksheet. 03:45 This date will continue to remain in sync with the data here. 03:49 What I'd probably do if I'm going to be using this is add Dollar signs here. 03:54 Take these and use Comma format. 03:56 That cleans up substantially. And just to show what does happen, if I change this 04:02 150 here, of course, it's going to change here. 04:04 Maybe this was a mistake. I'll make it be 155, and as soon as I press Enter, 04:09 it's going to have impact below her. And it changes the totals here because 04:13 that's dependent upon the cell up in the upper-right in column H, and so on. 04:18 So, this is a reflection of the data here, but it's a transposed version of it. 04:22 So I think you can see the value of that. But equally valuable is what we saw prior to this; 04:27 that's just the ability to transpose data. 04:30 This function that we just saw here is an array function, so you must press 04:35 Ctrl+Shift+Enter to make it work. Collapse this transcript
Building analysis via regression techniques with TREND and GROWTH
 00:00 In this worksheet called TREND, we see a chart off to the right. 00:04 There are two array functions that are going to be valuable if you know 00:09 something about regression analysis. 00:11 Now you don't have to be a statistical genius here, but just have a rough or 00:15 vague familiarity with those concepts. 00:18 Many people who use charting in Excel are familiar with the idea that you can 00:21 easily add a trend line to a chart like this for some analytical reasons, 00:27 right-click on the actual line here, and choose Add Trendline. 00:32 Now it automatically will show you a linear trend line. 00:36 We are seeing this, this so-called straight line. 00:38 I am going to click Exponential over here to show you what that looks like. 00:42 Now the value of this is it's fast, and it's easy, but if you actually want 00:46 to have numbers to go with it, for example, in columns C and D, this isn't 00:51 going to help too much. 00:52 Now you could display R-squared value here, and you have to know something about 00:56 regression analysis to make this work. 00:58 So I am going to take a little bit of time to get there. 01:00 So I am going to close this and undo by pressing Ctrl+Z to go back to 01:05 the regular display. 01:06 I'm also going to click on the chart to emphasize, as you know to the left here, 01:11 that this chart is designed to show data from columns B, C and D. Pick up the 01:16 labels from column A. So columns C and D, although they are empty for the 01:20 moment, will be displayed in the chart as we enter the numbers using an array 01:25 function called TREND. 01:28 In other words, we would like to see numbers that will produce that 01:32 trendline right here in column C, numbers that are loosely and closely 01:37 associated with this list. 01:39 So we highlight this entire group of cells here, and we use the array function 01:43 =TREND, left parenthesis, and we need to only show these cells right here. 01:50 And since this is an array function, I'm going to press Ctrl+Shift+Enter, and 01:56 there we see that line depicted. 01:58 Now we see numbers associated with this trendline. And similarly, if you'd 02:04 like to have an exponential line as well-- and you certainly could have one or the other-- 02:08 we are going to use the same concept here, but use the array function called GROWTH. 02:15 And it, too, will be based upon the values in column B. =GROWTH, left 02:19 parenthesis, highlight all these cells here. 02:23 You could click here, and Shift+Ctrl+ Down Arrow, Ctrl+Shift+Enter to complete 02:29 the entry, and there is that exponential growth line. 02:33 So, valuable tools. You might use them in combination with a trendline concept, 02:38 or as we saw here, avoiding that approach, but simply creating the numbers 02:42 and having those numbers appear in the chart. 02:45 So in either case here, we have got the situation where every one of these cells, 02:49 as we look at it, looks exactly like the one above it in column C; same thing 02:53 true in column D. And just as we saw with the FREQUENCY function, you can't come 02:58 along and delete just one of these. 03:00 So this is an array function throughout the entire set of cells right here 03:05 in this range. Collapse this transcript
Combining multiple functions in arrays
 00:00 If you are trying to use Excel's Lookup capabilities, you're probably going to 00:03 be using the VLOOKUP or HLOOKUP functions, and yet there will be times when these 00:08 functions don't give you everything you need, and you might have to turn to some 00:12 variation using array formulas and functions. 00:16 In column D is a relatively straightforward use of the VLOOKUP Function, but 00:21 doing something maybe you haven't seen. What we are trying to do is to take 00:24 those names in columns A and B and match them up with the entries as we see them in column G. 00:30 Now this might have more importance if-- you can imagine--both of these lists or 00:35 thousands of entries long, and they might have been in separate worksheets 00:38 and workbooks. So all we are doing in this function--and we did this in 00:42 previous movie on VLOOKUP-- 00:44 we are simply concatenating. 00:46 For example, on that first situation there Randall, space Yvonne. 00:50 We are trying to find that in column G and when we do, we go into the second 00:54 column and pick up the answer. And sure enough, we get a correct answer there. 00:58 It's the ID number 891. 00:59 There it is right down there. 01:01 Now the reverse of this is much more difficult, and it will require the use of an array formula. 01:08 And the starting point here is a little tricky. 01:10 We are trying to find Margarita Roy, and you see how the name is entered here. 01:14 But over in columns A and B, it split into last name and first name. 01:19 So let's start with the idea that the MATCH function will allow us to pull 01:24 together information, but you probably have not used it as an array formula. 01:29 We are trying to take the entry right here, Roy, Margarita, and match it up with 01:35 data that we're seeing in columns A and B. And we will look throughout column A, 01:40 and we want to put that together with-- and we have to do this within double 01:44 quotes--a comma and a space and the entry in column B. 01:51 So what we are building here is all the As and Bs. 01:54 We are putting them together separated by a comma and a space. 01:57 We are trying to match Roy, Margarita with that new construction. 02:02 And we got to make sure this is an exact match, so ,0. And if we press Enter here, 02:08 we are not going to find what we want. 02:10 But we are talking about array formulas, array functions. 02:14 We press Ctrl+Shift+Enter, and it's found in the third row, based on our column A and B entries. 02:21 So now that's part of the issue here. 02:24 We have found this. 02:26 But we actually want to get the sales amount. 02:29 So we need to put this together along with the INDEX function, which allows us to 02:35 pull information out of a table. 02:37 The INDEX function begins with the argument that says here is the array that we 02:42 are trying to extract data from. 02:45 We are looking in column C. Comma. We now need to put in the row number that we 02:51 need from column C. Well, we just figured that out with this Match function here, 02:56 and that's going to be the third row. 02:58 Since we're only choosing one column here, we don't need to put in comma one or 03:04 two or anything like that. 03:05 There is only one column here. 03:07 It would not be wrong to put in comma one, but we don't need that, just 03:11 simply right parenthesis. 03:13 But even though the use of the INDEX function here is not an array function, the 03:18 use of MATCH is, so we need to press Ctrl+Shift+Enter to get that sales figure 03:23 for Margarita Roy, and there it is, 77945. 03:28 You will also see it over here. 03:29 And we are pulling together a number of different features of Excel here. 03:32 A lot of you don't necessarily use entire column references. 03:36 I use those wherever possible. 03:38 It simplifies the look of formulas to give you greater flexibility. 03:42 We don't have to constantly adjust this as we add and take out records. 03:46 But the key idea here is that by using an array formula, by pressing 03:49 Ctrl+Shift+Enter, we are allowing ourselves to pull together the data from all 03:55 those column A entries and all the column B entries. 03:58 So once again, Ctrl+Shift+Enter. 04:00 We will double-click here to copy this down the column. And although they look 04:04 the same for a moment, in a second or two they will change. 04:08 Here they are, and we see the others. 04:11 So every one of these entries here doing the same kind of thing, each of these-- 04:15 and you can see it in the formula bar-- embedded in those curly braces that we 04:19 never type. These are array formulas using concatenation techniques. The MATCH 04:25 function and the INDEX function. Collapse this transcript
10. Text Functions
Locating and extracting data with FIND and MID
Extracting specific data with LEFT and RIGHT
 00:00 Excel has two companion text functions called LEFT and RIGHT. 00:05 They allow us to extract data from the left-hand side or the right-hand side of a cell. 00:10 Look at the data in column F. Maybe we need to pull out or isolate the zip codes. 00:15 Now when we say extract or isolate, we don't mean that we are going to be 00:19 altering column F. What we really want to do is that in column G, we simply want 00:24 to show the last 5 characters. 00:27 In other words, from the right- hand side of those cells. =RIGHT. 00:32 So we are looking in cell F2--that's the first example here--and we need to pull out, 00:36 or make a copy of, the five rightmost characters. There they are. 00:41 I'll double-click to copy down the column. 00:44 Now there are certainly are other techniques for doing this on more permanent 00:46 bases, where we might even want to isolate the state. 00:49 That's going to be available off the data tab, where we can actually split 00:52 this text into columns. 00:54 But there certainly will be times when you need to make a copy of data found in 00:59 cells, as we did here. 01:01 Sure enough, sometimes we need to pull data from the left-hand side. 01:05 Maybe on these part numbers the two leftmost characters have something to do 01:09 with either the location or the size or the color of the item, and we need to 01:13 isolate that information maybe for sorting purposes. =LEFT. 01:18 We are looking in cell C2 here, comma 2, and we need only the two 01:24 leftmost characters. 01:25 Once again, we can double- click to copy this down the column. 01:29 Armed with the knowledge of how we use LEFT and RIGHT, we can then use either 01:34 one of these in combination with other text functions that we might be aware of. 01:39 For example, we might want to pull data off the left side of the information in 01:44 column A--and of course that's the last name--but we also have to find where the 01:48 comma is, and that's done with the FIND function. 01:52 So let's start this particular function here by actually putting in FIND. 01:56 We are looking in cell A2, and that by itself will tell us that this is in position 6. 02:05 Then once we know that, we can pull data from the left-hand side of cell A2. 02:11 So the LEFT function, its first argument is where we looking? That's A2. Comma. 02:18 And now since the comma was found in the 6th position here, we want to make a 02:23 copy of the five characters to the left, 02:26 in other words, one less then where we found the comma. 02:30 So there we go, -1). 02:33 Now you certainly can do this in two separate columns, but the more you work 02:37 with these, the easier you will figure out that it's nice to put these together 02:40 to just save some time and make it faster. So there we go. 02:43 There is Baker and by double-clicking here, we will copy this down the columns. 02:47 And we see how this has pulled out, in each case, or isolated, the last names. 02:52 So using either the LEFT or RIGHT functions independently or in combination with 02:57 other functions, we have more tools to extract data from cells. Collapse this transcript
Removing excess spaces with TRIM
Using CONCATENATE with functions
 00:00 In Excel, there is a function called CONCATENATE, as well as a capability 00:05 sometimes referred to as concatenation. 00:08 In both cases of course, some people chuckle a bit at the pronunciation of the 00:12 word and even the use of it. 00:13 It's a bit of a tongue twister. 00:15 But here is a situation in columns A, B, and C, First name, Last name, Middle 00:19 Initial. Nothing wrong with that of course, but you might want to be pulling 00:23 data together either for a mailing label--in which case you might want this to 00:27 be in row 2, for example, Mark S Baker--or possibly you might want to 00:33 reconstruct that list and put everything into one column, so that you can sort 00:37 the data by last name. 00:39 In other words, we might want to see Baker, Mark S. Using either a 00:45 function called CONCATENATE or using concatenation techniques, you can 00:49 achieve these goals. 00:51 Let's just first use this word, CONCATENATE. 00:54 Now, as I start to type this, I will take advantage this time of the fact that 00:58 it will appear in the list. 00:59 There it is, right there. 01:00 I will press Tab, save myself some typing. 01:03 And we can concatenate data from a variety of different locations, as well as 01:08 adding characters of our own, as we wish. 01:11 So suppose we do want Last name first here. What do we need here? We need B2. 01:17 If we want a comma after this--this comma of course, simply separates B2 from 01:22 the rest of what we want to do. 01:24 Now, we want double quote, comma, space, double quote. So we want Baker, comma. Then 01:30 what do we want? The first name is in A2. 01:33 Now we need another comma to indicate the end of that second argument. Click A2, 01:38 comma, and now we want a space after that. Double quote, space, double quote, and 01:44 finally, after another comma, 01:46 we need the middle initial. Enter. 01:49 There we are: Baker, Mark S. 01:53 So simply double-click here to copy this down the column. 01:57 You can see what's happening. 01:58 Take a look at that again. 02:00 Now, I use this a lot, but usually I do it without the word CONCATENATE, which 02:04 we're about to show you. But the main point now is the idea that it's so easy to 02:08 make a typing mistake when you do this. 02:10 So you just have to be a little bit more diligent as you type those commas and 02:13 double quotes to remember what it is you are typing. A little tricky there, but 02:17 generally easy to do. 02:19 Now a variation on this--and I'll just use the other construction--is not to use 02:23 the word CONCATENATE at all, but simply to use the ampersand as a separator. 02:29 So let's say this time around, what we want to do is use first name, middle 02:33 initial, and last name. And do notice, by the way, that some people don't 02:36 have middle initials. 02:37 So what do we need first here? 02:40 =A2. That's going to be Mark in this case. 02:43 What do we want to follow this? 02:45 Now we put in the ampersand and then double quote, space, double quote, and what do we want next? 02:53 The middle initial. That's coming right here. 02:55 Now some people don't have middle initials, so we'll show how this plays out. 02:59 Then we want the middle initial, and then what do we want? 03:03 Double quote, space, double quote--in other words a space. And finally here, what do we want? 03:09 Baker. And since all these cells are highlighted, I am going to press Ctrl+Enter. 03:14 They'll all be taking care of it at once. 03:16 We see how this is being pulled together. 03:18 Now is that clearer then using CONCATENATE? 03:21 For a lot of people, no. 03:22 It's less typing. So either way works, either of the CONCATENATE function or 03:27 the concatenation techniques as we see here. 03:31 But there is some real power in controlling the way you have data stored in a 03:35 database-like environment. 03:36 In the previous example, we saw--and I am going to press Ctrl+Z a few times to get there-- 03:41 this might be a preferred layout for most people because you can, when you have 03:47 a list like this, sort by last name. Of course, keeping the data in separate 03:51 columns, you can do that too. 03:52 Whether we use CONCATENATE or concatenation techniques, here, too, we 03:57 finally reach a stage where we might be saying, let's keep this data, throw 04:01 away the original data. So what do we do? 04:04 With the right mouse button, here, we simply drag this either upward or 04:09 rightward into a different location, and then immediately, right back on top of 04:13 itself, let go of the right mouse button, Copy Here as Values Only. There we are. 04:19 So either way makes sense, CONCATENATE or concatenation techniques. Collapse this transcript
Adjusting case within cells with PROPER, UPPER, and LOWER
 00:00 In Excel, there are three interrelated text functions that allow us to control 00:05 the display of case. 00:06 I am talking about uppercase and lowercase. 00:08 There is also a third case, and that's the one we need here if, as we look at 00:13 column A, we think those names would be more readable and ultimately take up 00:17 less space if only the first letters were capitalized, not the others-- 00:22 the way we see this entry here. 00:23 There is a function called PROPER, =PROPER. 00:28 By definition, PROPER will capitalize only the letters that either begin the 00:33 actual cell or follow space or punctuation. 00:37 So note that the B in O'Brien, since it follows an apostrophe, it's capitalized. 00:42 The D follows a space. 00:43 It's capitalized, and that's what we want. 00:44 Notice at least one of the names here has a middle name. 00:47 So as we double-click to copy this down the column, we see the first letter of 00:52 each name is capitalized. 00:54 And you can try this with book titles and other kinds of entries as well. 00:58 Most commonly, I think it's used with names. 01:01 Once again, as in examples in previous movies, if we want to keep the results 01:05 here, while the results are highlighted, 01:07 we can simply, with the right mouse button, drag this into column A, and as we 01:12 do, let go of the right mouse button, Copy Here as Values Only, and then we 01:17 could get rid of the data in column B. 01:19 Well, I am going to press Ctrl+Z just to remind us that we have done this, and 01:23 you can see how it plays out. 01:24 Notice that the first D in MacDonald, whether it was spelled with Mac or Mc--it 01:29 doesn't make any difference-- doesn't get capitalized. 01:31 One of these years, perhaps they will have that figured out, but that doesn't work yet. 01:36 This does save you a lot of time, though. 01:38 I think for a lot of people, it's is preferable. 01:40 It does take up less space. It tends to be a bit easier to read. 01:43 Now we can also use functions like PROPER along with other techniques, such as concatenation. 01:50 We might want to reconstruct these names here, last name, first name, and at the 01:55 same time use PROPER. 01:57 So the first order of business might be simply to write a concatenation formula 02:01 that takes the last name, & following this, within double quotes, comma, space 02:07 double quote, & the first name. 02:11 So that's taken care of, but along with it--and I could have typed it at the 02:14 same time--precede that with the word PROPER. There we are. 02:20 And if, along the way, another issue came up, if some of these names had leading 02:26 spaces, we assume those would be mistakes, so put them here, put them there, and so on. 02:31 We would have other issues with the results here. So sure enough, in all these 02:36 examples here--and I'll just go to first one. 02:38 We can have the word TRIM. 02:39 Do we put it after PROPER or before? 02:41 It doesn't make any difference. 02:42 We do need an extra set of parentheses, so we can put right here if you wish, 02:49 and one trailing parenthesis as well. And I emphasize we could reverse the order 02:53 of the words TRIM and PROPER here, and this is still going to work. 02:56 Take care of it there, double-click to recopy, and everything is cleaned up all at once. 03:02 So we can use these in combination with one another. 03:05 Now in column H, we see another situation. And here, too, one of those, not the 03:10 worst problem in the world, but these part numbers just look a little strange 03:14 because the letters should have been capitalized here, and they weren't. 03:17 So in a certain sense, they are not wrong, but it doesn't look good, and you 03:20 certainly don't want to retype these. 03:23 We want to make these letters uppercase. 03:26 The numbers we don't want to change, and this function doesn't do anything with numbers. 03:30 It simply uses them as it sees them. But the function UPPER, meaning 03:34 uppercase, simply means take this information here and display it all in 03:38 uppercase, sure enough. 03:41 The reverse of UPPER is not downer. 03:43 It's called LOWER, but you are less likely to use that. But sure enough, it can 03:47 be used as well if you need to turn a bunch of uppercase entries into lowercase, 03:51 or a bunch of proper case entries into all lowercase, you can certainly use that as well. 03:56 So these functions, although we tend to think of them together, are likely to be 03:59 used one by one in different situations where we need to change the case, either 04:04 to lower--rarely used--or upper or in the case that we saw here, proper case, 04:09 which allows us to capitalize just the first letters. 04:11 They are all valuable, and they'll often save you a ton of time in cleaning 04:16 up your data. Collapse this transcript
Adjusting character content with REPLACE and SUBSTITUTE
 00:00 Excel has two companion functions that are very similar in nature called 00:04 REPLACE and SUBSTITUTE. 00:07 One of them is character-position specific. Another one is content-specific. 00:13 We can tell both the descriptions in row one, how to differentiate them. 00:16 Let's talk about this concept first of all. 00:19 We've got some data in these cells here, A2 through A5. 00:22 They're some kind of code numbers, and all the examples on this worksheet are 00:26 actually code numbers. 00:28 But we want to replace the fifth character with nothing. 00:31 The fifth character there is actually a hyphen. 00:33 You can see that there, and over here on the right-hand side, we don't see the hyphen. 00:38 We see QE. 00:40 Now first thought might be, why don't we just do an Edit > Replace, replace 00:44 hyphens with nothing? 00:46 Well, we could do that if we wanted to get rid of all the hyphens, but there are 00:51 other hyphens out here in the third last position that we want to keep. 00:55 So the function we want to use here is called REPLACE. 00:59 REPLACE allows us to replace characters based on their position. 01:04 So where are we looking here? 01:06 The old_text reference means the text that we are looking at, which is in cell 01:11 A2. Comma. Starting at which position do we want to make a replacement? 01:18 In this example here, based on the description, it's the fifth character. 01:22 For the moment, there's a hyphen there. 01:24 We don't care what the character is really, but it is a hyphen here as we see 01:28 it. Comma. And how many characters do we want to replace? Just one. 01:34 It could be two or three or five of course, but in this case just one. Comma. And 01:39 what do we want to replace it with? 01:41 It could be three characters, five characters, six characters, but within double 01:46 quotes, we'll put in what we want replace it with. And if we want to replace it 01:50 with nothing, it's just double quote, double quote, Enter. 01:53 We can see the results. 01:56 We have replaced the fifth character-- 01:57 that's the hyphen between the N and the Q--with nothing, as we see it here. And 02:03 we do this in the rest of the cells as well. Same general idea. 02:07 In all cases here, we are replacing the fifth character, and just one 02:12 character, with nothing. 02:14 Now, we can also replace this with a longer string. And again, this looks 02:19 a little contrived, but there are all kinds of weird things going on when people 02:22 are manipulating product type, codes, project numbers, that sorts of thing. 02:27 People are trying to reorganize and regroup maybe. 02:30 The need here is expressed in cell E1 is to replace the eighth character, so 1, 02:36 2, 3, 4, 5, 6, 7, 8. That's from the left-hand side. 02:40 Here we want to replace that second hyphen with the characters USA, so that 02:45 it looks like this. 02:46 So once again, it's the REPLACE function. And we're looking here. Comma. We want 02:52 to go to the eighth character, comma, and replace that one character there, comma, 02:59 with the string USA, so within double quotes, USA. We are done. 03:05 That's it. 03:06 Now, if we wanted to replace three characters there, of course, we'd put in 3. 03:10 How is that going to look? 03:13 We go to the eighth position. That's hyphen. 03:15 We want to replace three characters with USA. 03:19 So what have we replaced? 03:20 The -87 is now USA. 03:23 So the difference between this and what we did see earlier, this was a one, 03:27 and that means we simply replaced the hyphen. But by putting in 3, we are replacing the -87. 03:35 Now the SUBSTITUTE function is about the actual characters we're trying to 03:40 replace, not their locations. 03:42 In the example here, we want to get rid of the first dashes and 03:46 substitute nothing for them. 03:49 Now here, too, could we be using that Find and Replace capability? 03:54 No, because we don't want to get rid of all of the hyphens here. 03:58 So =SUBSTITUTE, very similar in nature. And every time I use one of these, I'm 04:02 thinking, should I have used the other one? 04:04 So if you don't use them frequently, you have to think out which one it is, but 04:07 they are both capable, yet they have suddenly different needs. 04:10 SUBSTITUTE. Okay we are looking in cell A8 here, comma, but we are looking for a hyphen. 04:17 So double quote, hyphen, double quote, and that's what we want to replace. 04:22 Now there are multiple hyphens, and we have the option of replacing all them, 04:26 just the first one, or just the second one, or just the third one, or whatever. 04:30 Comma. We want to replace this with what? Nothing. Double quote, double quote. 04:36 But only the first occurrence of it, not all of them, so we're about to replace 04:40 that first hyphen with nothing. 04:42 The second hyphen we leave where it is. 04:45 Again, it looks like that. 04:48 Double-click to copy this. 04:50 In the example over here in cell E8--you can certainly imagine what we are about 04:53 to do here--almost the same kind of thing. 04:55 We want to replace that second hyphen with XXX. 05:01 So we are going to start with SUBSTITUTE as before, right there, and I'll use tab 05:05 to pop it into place. And we're looking in cell D8. Comma. Once again, we are 05:12 looking for a hyphen. Comma. We want to replace it with "XXX, but not just any 05:20 hyphen, the second one, not the first one. So there we go. 05:26 We've replaced the second hyphen, or dash--whatever you want to call it--with 05:29 XXX, and there it is again. 05:34 I tend to use these functions three or four times a year. 05:36 I get them confused at times. 05:37 I get them mixed up, but I think you can see their capabilities. 05:41 Again, in brief, the REPLACE function allows you to replace information on the 05:46 basis of positioning. 05:49 The SUBSTITUTE function allows you replace information on the basis of content. 05:53 They are both really valuable in cleaning up data. Collapse this transcript
 00:00 In this worksheet called MoreText, we see two different sets of data, and both of 00:05 them exhibit common problems that you might encounter, particularly if you 00:08 download data from other resources. 00:10 The data in column A here and there has trailing minuses. 00:14 Perhaps it has come from another system where that in the case it's a negative. 00:18 What you'd like that data look like is shown in column B. We want the minus in 00:23 front of the numbers. 00:25 When minuses appear behind numbers the way we see them here, they are 00:29 actually treated as text. 00:30 There are couple ideas and thoughts as to how we might get the data looking like this. 00:36 The problem with situations like this though, tends to be that there's not a single answer. 00:41 We might have to use more than one function. 00:44 Now the good guess would be these are going to be text functions, but which ones. 00:49 I think most of us would recognize that there is a way to identify a trailing 00:54 minus, if we understand how to use a function like RIGHT, which allows us to pull 00:59 off the rightmost character. 01:02 In English, we might be saying the following. 01:04 If the rightmost character is a hyphen, then take the remainder of this and 01:09 multiply it by minus one and then display that so it looks like this. 01:14 If it does not contain a trailing minus-- say like in the second example here-- 01:19 simply display the cell as is. 01:21 That sounds relatively straightforward, and in a certain it is, 01:25 but let's talk about how we are going to build this. 01:27 We need to know something about the If function, which many times is simple 01:31 and straightforward. 01:32 We've got a simple logical test here. 01:34 We use the RIGHT function. 01:37 If the right character of this location, comma, one, equals a minus, and we got to 01:44 put that within double quotes, what do we do? 01:48 We want to pick up the remainder of the cell. 01:50 Well, how do you do that? 01:53 We could say we want to pick up the left 1, 2, 3, 4, 5 characters, but what if 01:58 this is \$45.45 with a minus? 02:01 That's five characters, and how are we are counting the decimal? How is this all 02:05 going to fall into place? 02:07 Another function is going to come in play as one perhaps you're not aware of. 02:10 It's called LEN, meaning length. 02:13 If we know the length of this entry, and it is literally 1, 2, 3, 4, 5, 6, 7 02:19 characters, we want to pull out six characters from the left. 02:24 So if we know the length of this--so let's put on that in, LEN, the length of this. 02:29 And again, as we look at the data here just for the single example, we're talking 02:34 about 1, 2, 3, 4, 5, 6, 7 characters -1, we need to pull out six characters from 02:42 the left of cell A2. 02:44 So we want this LEN to be within the function called LEFT and the LEFT function 02:51 allows us to look here, comma, so we want to pull out six characters from the 02:57 left-hand side of cell A2. 03:01 In other words, right now what this will have done, if it's working properly, 03:06 will pull out 345.45. 03:09 But we want to make sure it gets treated as a negative, so we need to 03:12 multiply this by minus one. *-1. 03:17 That's what we want to do when there's a trailing minus. 03:21 When there's not a trailing minus, we simply want to just pick up whatever is in 03:25 cell A2. Right parenthesis. We are done. Enter. Looks like that. 03:32 Of course, we'd copy it down the column, and we might have thousands of these, but 03:35 next concern is, what happens in this case when we have a number like this? 03:40 Everything is just fine. 03:41 Now that was not the easiest thing in the world to do, but again, if you approach 03:46 this methodically and learn how to use some of the various text functions, you 03:49 can get pretty creative in cleaning up data. 03:52 The payoff, of course, is you've got thousands of entries, and you really write 03:56 one formula and then copy it many, many times. 03:59 So that's how we can pull together the data, correct the data that we see in 04:03 column A, put it the way we want it to be by using in this case a combination of 04:07 the RIGHT function, the LEFT function and the LEN function. 04:12 Now another concern is the data that we see in column E. Now these are code 04:17 numbers, but because they're typed as numbers, they don't show leading zeros. 04:22 And now we do see leading zeros here. And by the way, a common situation here is 04:27 the issue of zip codes. 04:29 Let's put in a New England zip code. I am going to type 00123, and as I press 04:35 Enter, look what happens: we just get the 123. 04:39 Ideally and what I should have done here and probably for the whole column, a 04:43 right-click > Format Cells would be the fastest. 04:46 On the Number tab here, simply choose Special and then Zip Code, or as the 04:52 case may be, Zip Code + 4. 04:54 There are the zeroes and from now on, we are all set. 04:56 A zip code in the West Coast maybe is this. A zip code in New England might be, 05:01 and I will just type 34. 05:02 I won't need to type any zeros even. 05:05 Now take a look at the data in column E. This is a little bit different. 05:08 These perhaps are code numbers or id numbers. 05:11 We want them to look like this. 05:13 We want all of these to be five characters wide. 05:16 Let's get rid of these and use a function called TEXT. 05:23 We want to take this data here, comma, and convert it into text and use the format, 05:31 double quote, and five zeros if we want five positions here. 05:35 It'll fill it in with leading zeros when we have fewer than five characters. Double quote. 05:41 We are done. Recopy it. There we are. 05:45 The data looks like this. 05:47 In this case, as in lot of previous examples, if this is what we want, and we 05:50 have done this through a column, we simply will take this data and with the 05:54 right mouse button drag it into the data in column E to clean it all up, like that. 05:59 Copy Here as Values Only. 06:01 So I want to leave you with the idea, if certainly not the reality, that anytime 06:05 you've got data that's in place, but it isn't looking right, there often is a 06:09 way, using some of the many text functions available to us, to clean up data. 06:14 And even though from time to time it gets a little involved with formulas like the one we saw here, 06:20 have faith, I guess I am saying, in the idea that these text functions do allow 06:25 you to clean up major portions of the data that you sometimes get that looks 06:29 great, but it doesn't quite the shape you want it in. Collapse this transcript
11. Financial Functions
Calculating payments with PMT
Finding future values with FV
 00:00 If you need to calculate the value of money invested on a regular basis, you 00:04 will need to turn to the FV--Future Value--function, one of Excel's many 00:09 financial functions. 00:11 In row1, we see the question, "How much money will I have after five years if I pay 00:16 \$300 per month into an account earning 3% interest per year?" 00:20 The function FV, Future Value, the rate, 3. As in many financial functions, we'll 00:30 need to divide this 12, if it is a monthly payment the most common kind. Comma. We want 00:36 to be doing this for any number of years. I suggested five here. We can either 00:41 put in 60, or to make it perhaps a little clearer, five times 12--obviously the 00:45 same effect. Comma. And how much are we putting in each month? \$300. 00:50 There we go. 00:52 After five years now, if we put this under the proverbial mattress or in 00:56 the cookie jar and nobody touches it, we are talking about what? 60 times 300. 19,394. 01:04 In these situations, you may or may not wish that to appear as a negative. If you 01:08 don't in creating the function itself, simply put minus in front of the amount 01:13 that you are investing here. 01:16 You notice the second question in row 2: How much money will have after five 01:20 years based on \$15,000 deposit into an account earning 3% interest per year. 01:26 Again, we will use FV, but in a different way this time. 01:30 We are not talking about putting in money on a regular basis, but just 01:33 putting in a lump sum. 01:34 Once again, we have got 3%. 01:37 We will not divide this by 12. Comma. And only one payment, but we are doing this for 01:44 three years. And this time PMT is actually left blank, the third argument. 01:50 The present value, in other words the money we are putting in right now, is 15,000. 01:55 And as in the previous example, we don't want our result to be negative, so I 01:59 will put a -15,000, and this is the amount. 02:06 So if we put 15,000 into an account, and it earns 3% interest per year, this is how 02:10 much we will have after five years. 02:13 In either case here, you easily build a table, if we thought about how much 02:17 money we might want to put aside each month, and consider how much money we will 02:21 have after so many years. So in column A, we see different numbers of years 4, 5, 6, 7, et cetera. 02:28 We see different amounts across row 8. 02:30 How about a single formula here? =FV. And in all cases here, we are using a 02:36 table that's going to be using 2%. 02:38 So we will use this rate right here. And we need to make this absolute, so we 02:42 will press F4. And since this is a monthly payment, divide this by 12. Comma. And now the 02:50 number of years is going to be four, for example. We need to multiply that by 12 02:56 because we are making a payment every month. 02:58 And the reference to column A must be absolute also, so clicking in the A9 03:04 address, pressing F4, so that we have the dollar sign in front of the A but not 03:09 the nine. And the third argument in the FV function here is the amount that we 03:15 are borrowing minus, it's going to come out of B8 initially, and then all the 03:20 other cells in row 8. This too needs to be an absolute reference focused on the 03:25 actual row number 8. And to be little bit more precise, it's actually what we call 03:29 a relative reference. In other words a portion of it is absolute, a portion of it 03:33 isn't. As I press Ctrl+Enter, this function will appear in all of the cells, and 03:39 there are the answers. 03:40 We might want to not show the decimals maybe not ensure the dollar signs. But we 03:45 see the various effects here of how much money we deposit each month into an 03:49 account, and this for a constant amount, whether there's 300, 400 and all the 03:53 variations there, across a different number of years, how much money we will have 03:57 at the end of that using the FV, Future Value, function. Collapse this transcript
Determining total amount of future payments with PV
 00:00 Although it would be incorrect to call the PV function the reverse of the PMT function, 00:06 nevertheless, this function does allow us to tackle some of the same issues that 00:11 we saw with PMT in calculating a monthly payment for a car or a house loan. 00:16 Here, the question you might say is reversed, or flipped around: 00:19 "What house value can I afford if I want to pay \$2,000 a month at 5% annual 00:25 interest for the next 360 months?" The PV function. 00:29 And here are some various interest rates. We might consider doing this for a 00:32 variety of entries. =pv (. And the rate that we want here is going to be found 00:40 right here, and we need to divide this by 12. 00:43 If we do anticipate copying this, we don't have to worry this either because 00:46 it's going to be in same relative cell. 00:48 Divide this by 12 as we do with many Excel functions when it comes to rate. Comma. 00:54 And the next item that we need here is the number of periods here. 360 months. 01:00 We can either type that in or put in 30 times 12. 01:02 Some people think it's a little bit more revealing to do this to accentuate the 01:06 idea that it's a monthly payment, but either way it will work. Comma. And the amount 01:12 here that we would be paying each month. 01:15 Now, let's say we've sort of set aside the idea that we will spend 2,000 month. 01:22 The result here is, what house value can I afford? 01:25 Roughly 360,000, 362 here. 01:28 Here too as in other typical examples, here we may wish this result to appear as a 01:33 positive number, simply put a minus before the 2000. 01:36 If we wish to copy this down the column, we certainly can using 01:41 different interest rates. 01:42 Here, I'll simply double-click. Then we'll have the different amounts here. 01:46 So, as the rates go down, of course, the more expensive house you can afford, or 01:52 let's say the more money you can borrow. 01:54 We can also use this function in a different way. 01:57 In row 15 is the question here, how much money do I need to invest now if I want 02:02 to have \$150,000 in 10 years? 02:05 This question also brings to mind the idea that sometimes the interest is 02:09 compounded differently. 02:11 But let's use the PV function here to tackle this issue. And here's the percent 02:18 and we want to divide this by 12. 120 months or once again 10 years we are 02:25 talking about here. 10 times 12. Comma. And this time we are not talking about a payment now. 02:32 So we put in 0 or simply ignore that argument, and then -50,000. 02:41 We want our answer to be a positive number. 02:43 So how much money do we need to invest right now if we want to have \$150,000 in 10 years? 02:50 Notice that the \$150,000 here actually refers to FV. That's the future value. 02:55 How much money do we need to set aside now if we are getting 2% per month interest? 03:00 122,000. And we might want to copy this down the column. 03:05 We'll just double-click. 03:07 Makes sense of course, if we get more interest than we need to put aside 03:10 less money right now. 03:12 If we set aside \$91,074.16 right now invested at 5%, and it's compounded monthly, 03:21 in 10 years, we'll have a \$150,000. 03:24 This time we are going to make a different use of the function by putting in the 03:28 fact that we are compounding this daily. 03:31 So the interest rate will come out a little bit differently. 03:33 So, as we type =PV here, the interest rate of course will be referring to cell B16. 03:40 But now we want to divide this by 365. It's going to be compounded every single day. Comma. 03:47 And so the number of payments is going to be 10 years times 365, 10*365. Comma. And as 03:56 in the previous use of the function to the left here, we have a 0 in this 04:00 argument, and the -150,000. 04:05 So, what's going to be the difference if it's compounded daily versus 04:08 compounded monthly? 04:09 Here, we see the difference. Not huge. 04:14 Perhaps on some of the higher percentages it will be, and we see those amounts. 04:18 The difference here--and I am going to press Ctrl+Tilde here to show these 04:22 differences so we can see both functions together, and there we can see them side by side. 04:26 You see how they are calculated differently because we are talking about 04:31 compounding monthly versus compounding daily. 04:34 Press Ctrl+Tilde again to see the difference. 04:37 So, using the PV function, we can calculate, as we saw in the previous example, 04:42 a sort of a goal or an amount that you're looking for. And you can also use it 04:46 for investment purposes as we saw down on row 16 through 19. The PV function. Collapse this transcript
12. Information Functions
Working with the IS information functions
Using error-checking functions: ISERR, ISERROR, IFERROR
13. Reference Functions
Getting data from remote cells with OFFSET
 00:00 In this worksheet called Offset, we've got data in columns A and B, and in 00:06 columns D and E, we see an indicator for Latest Date and Latest Closing Rate. 00:12 Now imagine that what we are collecting here is information. 00:15 Our list is going to grow and grow and grow, get much deeper. Maybe this has 00:18 something to do with the stock market or some other index. I forgot to put in the 00:22 closing rate here for the following day. That's 256. 00:27 What we want to know at all times though, is the last entry. 00:30 So for the moment, what we would like to see right here is this date, and right 00:35 here in cell E2, we would like to see the number 256. 00:37 We want to do this in such a way that no matter how many entries we have here, 00:43 this will grow and grow and grow. 00:45 We are always picking up the latest date, the latest closing rate. 00:50 If you're exploring for a function like this, it's going to take you a bit 00:53 of time to find it. 00:55 Eventually, you will come across, by doing a search, under Lookup & Reference, a 01:00 function called OFFSET. And the brief description of this when you see this may 01:05 not ring true: "Returns a reference to a range that is a given number of rows and 01:11 columns from a given reference." 01:14 Well, that is exactly what we want to do here. And after using this function a 01:18 bit and reading this, it will make a lot of sense. 01:21 It's exactly what we want to do. 01:23 We need a reference to that range in column A, but we need to know how many 01:28 cells it is from the top. 01:30 It will involve also using a function called COUNTA, which allows us to count the 01:36 number of entries in a range. 01:38 =OFFSET(. The reference that we're talking about is cell A1, the top of the 01:50 reference here. Comma. How many rows away from that cell are we looking at? 01:59 We are looking at this cell right here, which is eight rows away. 02:03 The function called COUNTA--by the way this has nothing to do with column A, 02:08 it's just coincidence we are using this-- 02:10 COUNTA will calculate, as you can see in the description, the number of cells in 02:15 a range that are not empty. 02:17 So by looking in column A and using COUNTA, although we can't see this value 02:24 right now, this portion of the function is giving us the number 9. 02:29 There are nine cells that have content in column A. 02:33 We want to move from cell A1--that's our starting point--downward eight rows, 02:41 whatever the content is, -1. 02:44 That's going to give us an eight. Comma. 02:47 We can move into a different column from here, but we don't need to do that, 02:51 so the column reference is zero, or we can simply ignore it, but you must have 02:55 at least the comma there. So, either way. Perhaps it's best to leave the zero 02:58 in there to suggest 02:59 we are not changing the column reference at all. 03:03 We need to put in the right parenthesis, because we've got multiple parentheses there. 03:06 So if this will work properly, we should we 10/14/2010. 03:09 That's what we are seeing. 03:14 Similarly, for the Latest Closing Rate, we want to do the same sort of thing, 03:18 except we want to be looking in column B, and our starting point is B1. 03:24 Let's copy this entry into cell E2. 03:28 That will throw you, of course. 03:31 That's not really what we want to see, but the format got copied as well. 03:34 We could have done that in a different way. 03:35 Let's simply copy the format, for example, from B2. 03:39 We can right drag this over to here, Copy Here as Formats Only. 03:43 So there we see the 256, and of course we want to test this as well. 03:47 Let's simply make this be the 15th, and as we drag this downward, of course we 03:52 see our adjustment there and maybe the entry here is 260. 03:55 We will adjust that, and we now have the entry here as well, 260. 04:01 So it's the OFFSET function. 04:04 This is our starting point. 04:06 This is how far downward we want to move: -1. 04:10 If you have situations here where maybe this is not in row one or if you've got 04:15 an empty row here, you're going to have to experiment a little bit with whether 04:19 you want to subtract one or two, possibly nothing, depending upon the layout. 04:23 So you have to work with that a little bit. But if we get a situation where 04:27 it's contiguous cells from the top, this is the structure you want to use, 04:31 the OFFSET function in combination with COUNTA to allow us to pick up that 04:37 reference that eventually is going to be pretty remote from where we are up in row two here. 04:41 Remember, this can go on and on and on down in columns A and B. Collapse this transcript
Returning references with INDIRECT
Conclusion
Final thoughts
 00:00 I've enjoyed presenting this course, and I hope it's been beneficial to you. 00:04 If you want to know more about Excel 2010, don't forget to check out the 00:08 web site lynda.com. 00:10 There you'll find lots of different courses on Excel 2010, as well as courses on 00:15 the other versions of Excel. 00:17 You might check out Excel 2010 Power Shortcuts, which has a goodly number of tips 00:21 and shortcuts to increase the efficiency. 00:23 You will get more power out of Excel this way. 00:26 You might check out Working with Dates and Times, and there are certainly other 00:30 courses as well that might gather your interest. 00:33 Visit this site often because from time to time you will see new offerings 00:37 in this courseware. 00:38 Thank you for watching. Collapse this transcript

## Suggested courses to watch next:

Excel 2010: Charts in Depth (3h 38m)
Dennis Taylor

Excel 2010: Macros in Depth (2h 44m)
Dennis Taylor

Excel 2010: Data Validation in Depth (59m 45s)
Dennis Taylor

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 100,984 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,945 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.