Complex Excel Formulas and VBA User Defined Functions | Grant Gamble | Skillshare

Complex Excel Formulas and VBA User Defined Functions

Grant Gamble

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
61 Lessons (8h 38m) View My Notes
    • 1. 1.1. Welcome to Advanced Excel Formulas and VBA User Defined Functions

      3:45
    • 2. 2.1. How this course works

      3:32
    • 3. 2.2. Writing complex Excel formulas

      7:23
    • 4. 3.1. IF statements

      8:45
    • 5. 3.2. Nested IF statements

      9:22
    • 6. 3.3. The IFS function

      7:00
    • 7. 3.4. Using VLOOKUP instead of IF

      3:58
    • 8. 3.5. The SWITCH function

      7:57
    • 9. 3.6. IFERROR

      5:52
    • 10. 4.1. Combining LEFT, SEARCH and VLOOKUP

      9:35
    • 11. 4.2. Combining MID, SEARCH and IF

      8:33
    • 12. 4.3. Combining RIGHT, LEN and SEARCH

      5:39
    • 13. 4.4. Creating a sentence case formula

      5:37
    • 14. 4.5. Combining LEN and SUBSTITUTE

      7:48
    • 15. 4.6. Brute force substitution

      18:17
    • 16. 4.7. Creating a title case formula

      10:19
    • 17. 4.8. Conditional concatenation

      6:25
    • 18. 4.9. Starts with a number

      9:24
    • 19. 5.1. Nesting VLOOKUP functions

      7:26
    • 20. 5.2. VLOOKUP multiple criteria - Basic

      4:48
    • 21. 5.3. Using MATCH & INDEX

      6:26
    • 22. 5.4. Advanced MATCH & INDEX

      7:56
    • 23. 5.5. Optimizing Lookup Operations Part 1

      10:20
    • 24. 5.5. Optimizing Lookup Operations Part 2

      14:32
    • 25. 5.6. OFFSET Function

      7:32
    • 26. 5.7. Using the INDIRECT function

      4:37
    • 27. 5.8. VLOOKUP multiple criteria - Advanced

      11:33
    • 28. 6.1. Controlling rounding sensitivity

      12:53
    • 29. 6.2. Using SUMIF and INDIRECT

      7:19
    • 30. 6.3. Using wildcards with SUMIFS, AVERAGEIFS, MINIFS and MAXIFS

      14:37
    • 31. 6.4. Using the SUBTOTAL function

      8:26
    • 32. 6.5. The RANK function variations

      13:49
    • 33. 6.6. Using LARGE, SMALL and COUNTA Part 1

      7:41
    • 34. 6.6. Using LARGE, SMALL and COUNTA Part 2

      9:01
    • 35. 6.6. Using LARGE, SMALL and COUNTA Part 3

      10:48
    • 36. 7.1. Array formula basics

      8:54
    • 37. 7.2. Using conditionals in array formulas

      11:18
    • 38. 7.3. Using concatenation in array formulas

      6:37
    • 39. 7.4. Array formulas and data tables

      6:04
    • 40. 7.5. The TRANSPOSE function

      5:43
    • 41. 8.1. Using the WEBSERVICE function

      10:05
    • 42. 8.2. XPath overview

      4:46
    • 43. 8.3. Using FILTERXML and ENCODEURL

      12:18
    • 44. 8.4. Using FILTERXML with local XML files

      8:49
    • 45. 8.5. Using XPath predicates

      8:13
    • 46. 8.6. The new CONCAT function

      8:13
    • 47. 8.7. Descendant-or-self axis

      3:06
    • 48. 9.1. Making the Developer tab visible

      1:41
    • 49. 9.2. The Visual Basic Editor

      5:49
    • 50. 9.3. Writing a UDF in a macro-enabled workbook

      10:54
    • 51. 9.4. Using trusted locations

      3:21
    • 52. 9.5. Writing a UDF in the Personal Workbook

      10:25
    • 53. 10.1. VBA syntax

      7:38
    • 54. 10.2. Excel object model

      8:47
    • 55. 10.3. VBA object model

      4:57
    • 56. 10.4. Displaying the user name

      11:59
    • 57. 10.5. Displaying workbook properties

      8:26
    • 58. 10.6. Displaying worksheet properties

      14:20
    • 59. 11.1. Using IF statements

      12:36
    • 60. 11.2. Using Select Case statements

      7:13
    • 61. 11.3. VLOOKUP pro rata function

      16:50

About This Class

10010b31

(Please note that this course is designed for experienced Excel users. If you are still shaky on the basics of Excel formulas, have a look at the SkillShare course "Essential Excel Formulas and Functions". )

The ability to write, understand and maintain complex formulas is the key to mastering Excel. Complex formulas make use of multiple functions to achieve a specific operation; and it is the need to nest one function inside another that can sometimes make Excel formulas seem so impenetrable. 

This course will show you how to create, comprehend and maintain complex Excel formulas; those which use multiple Excel functions in combination.

A key feature of the course is the use of explanatory diagrams. The course features dozens of diagrams saved in PDF format. You will have access to all of these diagrams. You can study them at your leisure. You can print them out; make notes on them; whatever you find useful.

Another key feature of the course is that we write our formulas on multiple lines and make strategic use of indentation. This makes it easier to read complex formulas, since it helps to make it clear when one function is nested inside another.

We’ll also encounter examples of using formulas to build formulas; mega formulas, sometimes dozens of lines long, which would be very hard to write manually.

We begin the course by looking at conditional functions, since these are the most versatile of Excel’s functions.

Then we’ll move on to look at building formulas for manipulating text; for manipulating numbers; formulas for performing complex lookup operations. And we’ll also discuss the use of array formulas; as well as formulas for importing XML-based information into Excel, both from the web and from local XML documents.

And finally, we’ll move on to look at the creation of custom, user-defined functions using VBA. These functions can be included in any workbook and can greatly enhance your Excel solutions.

Many courses which teach Excel user-defined functions will waste your time by showing you how to create VBA functions which replicate functionality which could be created by regular Excel formulas. Not on our course!

We’ll focus on writing VBA functions which provide results that simply can’t be produced using Excel’s own built-in functions: for example displaying the user’s Windows login name, or the names of all the worksheet tabs; or displaying information from the folder in which a workbook is saved.

So, if you’re looking for a challenging course which will provide you with strategies for creating powerful and complex Excel formulas, then this course is exactly the course you need: “Complex Excel Formulas and VBA user-defined functions”.

Transcripts

1. 1.1. Welcome to Advanced Excel Formulas and VBA User Defined Functions: hello. Welcome to this course on Advanced Excel formulas. BB a user defined functions My name got gamble on, Can I say, First of all, that this course is designed for experienced Excel users. So if you need a refresher on Excel formulas, check out the course. Essential Excel formulas and functions on this course will dive straight in and start looking at Advanced Excel formulas. So what makes the Formula Advance Basically the main attributes banned foreigners is the combined use of multiple functions within the same formula. For example, in a basic formula, you might use the left function to extract the 1st 5 characters from strength. What is the number of characters to be extracted? Is not always the same way may need to come up with several functions, which, when combined, will give us the result of the one. And this is what this course is all about. We'll take a systematic approach to building complicated Excel formulas, which involved uses multiplex cell functions in combination. A key feature of this course is the use of explanatory diagrams. Calls features dozens of diagrams saved in pdf format. Naturally, you'll have access to all these diagrams you can study them at your leisure. You can print them out, make notes on them, whatever useful. Another key feature of the course is that, well, right on formula Multiple Alliance Thinks makes it easy to read. Complex Four Meters says it helps to make it clear when one function is messed it inside. Another will also encounter examples of using formulas, cannula, mega formula, sometimes dozens of lines along, which will be very hard to light manually. Way begin, because by looking, it's conditional functions. Since these are the most versatile of Excel functions, then we'll move on to look a building formulas, manipulating text manipulated numbers formulas for performing complex look up operations. I'm all for disgusting use of array formulas as well as formulas for important XML based information into Excel, both from the Web on from local, ex and all documents. And finally, we'll move on to look at the creation of custom functions using the N B. A. Thes functions could be included in any workbook and could greatly enhance Your Excel solutions. We'll focus on White and VB eight functions that provide results that can be produced using excels of built in functions for example, displaying the users with those long gone or the names of all the work she tabs would explain information from the bouldering, which world safe. So if you're looking for a challenge in course, which will provide you with strategies for creating powerful, complex Excel formulas, and this is, of course, that you need. 2. 2.1. How this course works: Once you've downloaded and unzipped the exercise files for this course, it's probably a good idea to delete the ZIP file just to avoid confusion. Amanda is going to the exercise folder on here. You'll find everything that you need to complete the course. The 1st 2 sections are preparatory, so there are no real work files here. You'll see in the Welcome folder there. No work files, a tall on. Then we've just got one work file and getting started. The course proper starts with 03 logical functions, and there are three components inside each of the sub folders. First of all, we have the start files. These are the files that I'll be using as I demonstrate. So when we talk about the first topic, which is if statements you'll need to open this file, which is what I'll be doing on screen and then follow along on what I'd recommend that you do, it's to start from scratch each time and then type the formulas as you watch the demonstration. However, I realize it's not always convenient for people to do this, so if for any reason you find it inconvenient to work in this way, simply go into the folder called Completed, and here you'll find the completed exercise. And here's the formula that will be built in in the very first you tool riel. But that's not the recommended method. Recommended method is to start with a blank each time, and that's what's the tutorial and to the formula. And, of course, if you feel confident that you can solve some of these problems yourself, go ahead and pause the video and have a crack at entering the formula yourself. So those are the tutorial files start in and completed, and then the other sub folder that we have here is a little diagrams. So as you're watching the tutorials, if I use any diagrams, you'll find your copy of that same diagram in this folder. And obviously the names match the tutorials that we do. They're all in PdF format, so when I double quick on a pdf, it opens in Acrobat. But any pdf Rita will be fine. If you are used, an acrobat will record that reader, and remember, you can just go to view full screen mode, but which the short cut is. Control L and then remember that some of these PdF's have multiple pages, So if you are in full screen mode, just use the forward and backward arrows to switch between pages. And, of course, to come out at full screen mode. Just press the escape key or type control L one small. So those are the three areas to look for when you go into each folder, completed diagrams, and then these are the ones that you need to focus on but start in file for each tutorial. 3. 2.2. Writing complex Excel formulas: before we start the cause proper. Let's just briefly discuss the style of writing that will be using throughout this course. So let's go into the Exercises folder on an 02 Getting started. Let's open the Excel File zero to writing Complex Excel Formless. We have a typical Excel formula, it's written, is one long line, and it's quite dense and Impenetrable to look at. It's not mega complicated, but it does take some deciphering. First of all, we can see that there are three functions. Be look up, left and such. And then it's down to our experience to work out the role of each of these three functions within the formula as a whole. So with a bit of investigation, we can deduce that left is acting as one of the arguments or parameters a B Look up with a bit more investigation. We can see that search is acting as one of the arguments of left. Let's compare that with the style of writing that will be using. So the first thing will be doing is to expand the formula bar, and there's a little button on the right here, which does that you click to expand and then click to collapse when you finished. Once you've expanded the formula bomb, the final height can be adjusted by going to the bottom of the formula bar and just dragging Uppal down. So we now click in Cell C two. We have the same formula, but Britain, in the style that will be using on that style, is simply to split the formula onto multiple lines. On the main benefit of doing this is to emphasize the role that each of these three functions is plain, so we can immediately see that be look up is the outer function, which contains everything else. If we click inside the open in parenthesis after the look up, Excel displays the arguments. When we click on the first, it highlights the entire left function. We can therefore see that the left function is supplying the first argument of the look up , and this is why we have written it in this style. Left is clearly inside. We look up and the indentation emphasizes the hierarchy. We look up is the parent. On left is the child. If we click inside the open in parenthesis after the left function, when we click on the first argument, we can see that it's simply a cell reference. But when we played on the second, we can see that it supplied by the search function again. We have this parent child relationship we can clearly see. The left is the parent, and search is the child. So that's the logic of split in the formula onto multiple lines and then in dent in the text to illustrate the hierarchical relationships between the various functions within the formula. So for those of you who haven't actually done this, let's now look at how you spent a formula onto multiple lines. I'll just press cancel to come out of this cell and then moved back to be, too, where we have everything on one line. The key criterion that I'll be using to decide when to spend a formula into multiple lines is as follows whenever there's a parent child relationship within the formula. In other words, whenever there's an outer function for which one of the arguments is supplied by a nested function, and that's clearly the case here. So the way that will write it is that will split the line after the opening parenthesis that follows the parent function. So the parent function is clearly the look up in this case. So we click after the opening parenthesis and we press old and enter to insert a line break within the formula bar. And then we simply use the space bar to invent the text. So we now have the left function inside. We look up if the left function had no Children and we write it along one line. But as we can see, it has the search function as the second argument. So for that reason will do the same thing with the left function. We pressel dente and then use the space bar to invent the arguments. The first argument is 82 the old enter before the second arguments and then space bar along to indented. And of course, what we trying to do here is to align the arguments. So after search, we have a minus one, and then here we've got the clothes in parenthesis to the left function on. What I'll be doing is to press old enter here and align the clothes in parenthesis with the opening premises. So all of that constitutes the first argument of the look up. So we now in certain all enter in front of the second argument and the line, the second argument under the first. And we do the same with the third argument, which is the number two. So Venter Space Bar on the final argument, both mentor space for, um and again all tensor on the line, the clothes in parenthesis off the look up under the opening Prentice's So this is purely stylistic. It makes no difference whether we write it on one line or whether we spent it on several lines when you go to a formula, either for the first time or after a while and having it written in this style where you can clearly see the parent child relationship between the nested functions. Hopefully, I think you'll agree it does make life easier. So that's the style that will be using throughout the course selling this center. And of course, there's no hard and fast rule as to when you invent and how much you in debt and so forth. But the general theme is that if you split a complex formula onto multiple lines, it's easy to see how the form that works and to see the relationship between the various components of the border 4. 3.1. IF statements: we'll begin our discussion of logical functions by talking about the if function, the most frequently used of excels, logical functions That's open up. If statements 01 on what would be doing here is to create a function which will calculate a person's age based on their date of birth. The basic calculation to do in this it's simply to subtract there. Europe ER from the current year that we have a person who was born on 17th of October 1997 on the current day is reporting Bob's over 2017. A person is approximately 20 years old, I say consummately because this is only true once the person has reached his or her birthday. Up until then, what we'd have to do is to subtract the year of birth from the current year events attractive for one. So as of now, this person is actually 19. It's only when we reach the 17th of October that she will actually become 20. So to express this, we use an if statement and the if statement takes three parameters. First, we have a logical test. In other words, a test which has to have trouble holes is the result, then we have a value. True, what would be like to put into the cell containing the formula? If the test proves to be true, a man value is false. What value do we want to put into the cells in the testes? False. The chronological test is we test into things the month on the day we'll need to use the armed function to combine them. The and function is another a common excel logical function, and it simply takes a series of logical tests. So first test, we'll use the month function to extract the month from the date of birth and check to see whether that's greater than the current month. And then we do exactly the same thing for the day. So both of those tests are true. This means that the logical test for the if statement will also be true. So that's how the and works all of the logical tastic contains, have to be true in order for the end to return. True on that causes, we do it all. Only one of the logical tests needs to be true. So in the case of this person about logical test is true the month is 10 in both cases, and in the case of the day, the day of the birth date is 17. That's greater than or equal to 14 which is the current day. So this means that this person, finally true, will kick in. We said practically, you're a birth from the current year and then we subtract the further one. If the logical has proved to be false, Ben for value it force, we would simply subtract the year of birth from the current year. So back in Excel, let's create our formula because we'll be split in this formula onto several lines. Let's begin by increasing the size of the formula bar. They've got plenty of space to work in. So we start with the if function, which contains everything else. And once we type equals. If on the opening for emphasis, Excel duly displays the three arguments that we need logical test on your true and that any false psychological test, we're going to use the and function and then inside it, we put out to logical tests. So let's start by test in the month. So we want to see whether the month of the date of birth, which issued in season two. It's greater than or equal to the month of the current date, which we can pick up using the Today function that today function is one of those rare functions which doesn't take any arguments. But because it is a function you still need to put the opening and closing parentheses after the name of the function. And then we put one further closing parenthesis to finish off the month function. That's the first logical test inside out hand. We put a comma. Now I'm gonna press old enter. To go on to a new line on this is purely to increase the readability of very subtlety in formula. We can then use the space bar to move along, and it's a line the second parameter of the and function under the first. So now we tested day and again we're testing whether the day of F two is greater than or equal to the day off the current date that completes our and statement. And just to emphasize the fact that is nested inside the statement, that's just press fault. Enter one final time, put the clothes in parenthesis aligned with the opening parenthesis from two lines above. So that entire and statement constitutes the first argument all its containing if statement . And then that argument is followed by a comma readability. Let's Pressel center again, and now we're align in underneath the first argument of the if statement. So just with the end, which was the first argument, So for value is true, which is the second parameter of the if statement, we need to say, Take the year off the current date, using today again and subtract from it the year off the date of birth. What is enough to immense attractive? Further one because with our test has proved, is that this person hasn't yet had her birthday. And then we put a common to separate the second argument from the third, and let's then put the third on a separate line. You did or enter on space bar so the value falls will be virtually identical. It's just that we won't be subtracted one at the end, leaving just copy this on paste. It's as I said, and final arguments. I'll just press old enter again and put the clothes in parenthesis of the if statement approximately in line with the open parenthesis, and that completes our formula. So as we have seen, this person hasn't had her birthday. So when we press center, she should be 19. And then, of course, once the 17th comes around, if we opened this worksheet again, this statement will no longer evaluated. True, it will evaluate to false, which means that the third argument will kick in on her age, will be displayed as 20. That completes our formula. That's presenter. Restore the normal science of the formula bar. A man just lived to the auto fill handle in the bottom right of the cell and double click to copy that formula down. That's an example, or using a fairly simple, if statement. But even in this example, in order to get the result that we needed, you've noticed that we've had to nest several functions inside the if statement in the next video, we'll continue exploring this concept of nest in one function inside another by looking at the use of nested ifs. This is where we have one if statement nested inside another 5. 3.2. Nested IF statements: let's now move on to look at nested. If statements what would be doing in this example? It's a sign in a status to each of the accounts listed in this worksheet, and these are the criteria that will be using. I should just explain one quirk that you'll notice in this streak. The days overdue column contains static values on the date. Do Kahlan contains a calculation to display that they do. And obviously, in a really scenario, the reverse would be true. We would have static values here, which indicated when the account was due and then would have a calculation here to work out how many days do each account waas. So just to explain, I've done that for training purposes so that we can both have exactly the same thing on our screens. So to calculate the status we're going to use these criteria 120 days or greater status is bad. Mind your greater aged on DSO four. When you use invested of statements to test numerical values, it's always best to start with the highest value. Work your way down to the low study. This means that you don't need compound testing. Don't need to say between 1920 between 60 and 90 etcetera. The reason for this is the way that Excel will evaluate the X statements. So we take the first road as an example. We've got days over. You have 20 by eliminating the highest value with our first test, what we come to do our second test. We know that the value is less than 120 because if it weren't, the second test would never even take place. That we need to test for each time is the next one down. Our test will simply to say great to them. Regal to it will never need to say both. Greater panel recall two and less than so before we go ahead and write the formula, let's have a brief discussion of how invested its work in Excel when they hear the term nice two days, a lot of people will visualize a structure whereby you have one if statement with several. If statements nested inside that one if and this is not actually the case, this isn't how it works. A better way of visualize invested ifs is to think of a Russian doll arrangement whereby each if statement is nested inside the previous one. So going back to our days overdue example. Let's say that we have a cell that contains 20 days overdue. Let's just run that career unless they've structure. So we start without outta if the one that contains all the others. And like all if statements, it has its street reminisces for the logical test for the outer if is gonna be days overdue , greater than or equal to 120 on the value of true will be the word bad that clearly our cell contains 20 so that's gonna be evaluated to falls. But instead of having a literal value, people's we need to do some further testing. So the value of falls is supplied by another, if statement. And so we have a statement to nested inside if statement one via the value of false parameter. So if statement to becomes the value of false parameter off if statement one. And that's how the structure works on our second if statement were tested to see if the days overdue is greater than or equal to 90 and if it were, we put the word aged into the cell since 20 isn't greater than or equal to 90. We need the value it false and again that is supplied by another, if statement. So we move on to its statement. Number three, which test to see the value of you is greater than or equal to 60. If it were, it would put the word of the do that. Since it's not, we go to Value Falls, which is supplied by the final if statement on the final or innermost. If statement, we test to see what these over here is greater than or equal to 30. If it were, we would supply the man do. But since it's not, and since we've done along the testing that we want, we supply the value, not do the literal value, so you can see that it's only the in the most. If that has a literal value for value falls. In the case of all of the other ifs, the value of falls is being calculated by another nested it statement. So now let's move back to excel and create the formula. So we're in the status column e to let's increase the size on hold, and then we'll split the formula into multiple lines. We start with the outer if statement, and we're testing the highest value. The Selva testing is D to on the highest value is 120 the greater than or equal to 120 coma . And then, as Excel is prompting us here we're onto the value is true. If true, we want the word bad to appear in the self on because that's text. It does, of course, have to go in double quotes. So now we come to 1/3 parameter value in bowls, and this is where we implement our nested if structure, instead of putting a literal value for the value false. We put another it function. Let's put that on a new line. Old enter for a new line Tremendous hit the space bar to move along underneath the first argument. So now we need another If and again we're testing D to. So what I'll do is to just copy this first test and then we can use that as our starting point. Each time I'll do all my paste in now old enter on old enter. So just explain why I've done that. We're testing or values 1 2090 60 on 30. So we're now need to do is to train these values and then change the status associated with each one so aged. What do you and not do? So, Having eliminated 1 2090 60 and 30 we now know that if none of these proved to be true, the status needs to be not do so. We don't need any further test in. We can finally supply a literal value. The value balls value falls has been supplied each time that it's been supplied by another , if statement. So within this structure, each of the statements has as its third argument, value it false. Another if statement. And it's only the innermost statement that has a literal value as it's patently false. So let's put this on the new line as well. Or Cantor space bar across men in double quotes you need not do. Let's have finished the formula. We've used four if statements and put in opening parenthesis after each one. We therefore now need all match in closing parentheses to finish the Formula 1234 and we don't let's restore the normal height. That formula bar and just copy before then. They're down by move into the also feel handle and double click in. That's how nested ifs work. If you're struggling to understand them, just think of Russian dolls. Lester. That's can be quite complex and convoluted. So in the next two videos, what discuss alternatives to use in the next city of structure? 6. 3.3. The IFS function: if you've upgraded to Excel 2016 When working with multiple conditions as well as using nested ifs, you now have the option of using a function called ifs. Let's have a look at how it works that's open 03 the dysfunction. And here we have the same example of unsold in the previous exercise. So before creating our formula that's have a look at the mechanism of the dysfunction. Now it differs from a regular if and nested ifs. This diagram shows the basic structure. Seven. It's formula. The first thing to notice is that there is only one ifs, and this single function takes as its arguments multiple logical test and value it true pairs. So we proceed exactly as we did in the last video. You start with the highest value, so our test is overdue, greater than or equal to 1 20 which is we saw before is false, and then the value true would be bad. It's in states over deuce. 20. This first test proved to be false, so we go into the next one testing for 90 intestine for 60 then testing for 30 now when we were working with nested ifs that was the last test that we needed to do, because when you nest YPF's the third argument of the final statement becomes a cattle. So you simply put the value that you want to appear. If none of your previous tests proved to be true, and it's very important to note that this is not the case with the dysfunction, there is no cattle. You simply continue with that structure up to 127 times. So in this example, we will need to do a final and fifth test to cater for that circumstance. When the days overdue is less than 30 we need to explicitly put a test to cater for it. And this is what you might call the Achilles heel of the dysfunction. If there were a casual, it will be exactly equivalent to invest it if and we could stop using nested lives and use its instead. But because there is no cattle and you have to manually cater for all eventualities you may find in some scenarios, this makes you a little nervous because obviously you have more testing to do just to make sure that you haven't let anything slip through. So let's now right, the function going to need to. And as usual, I'm going to increase the size of the formula bar and split the function onto multiple lines. They're equals gifts. So our first test is going to be whether it be to is greater than or equal to on this time . Instead of putting the literal values like we did last time, let's use the values in this table, so I'll simply click on the cell that contains 1 20 And, of course, since we intend to copy, this formula amounted to press F or hopefully, you know, factual cut so that Excel will insert dollar signs. Hence, when I copy the formula, that cell reference won't change. So that becomes my logical test one. And I put in a coma we now want to. Value is true one and value it true is in this table 86 So I click on the cell and once more press F four and then a comma, and that's the way the its function is structured. I simply repeat this parent logical test by the true logical test on drew up to 127 times So in our case, we need fire again. I'll just call me back, use old enter to move down, um, and control the paste. On this time, I'll copy the space as well. So old Enter control the entire control the control the and delete the final comma. Now all I have to do is to change the number each time. So we're working up that time. Thanks All it's a strain. And finally to on this final test needs a modification, doesn't it? So we're test in G three on. We're making sure that it's less Stan g three and to complete the formula, we've only used one function this time. So we just need one close in parenthesis and that completes our formula. But we present collapse the formula bar back to its normal size and copy down by double clicking on. We also feel handle. But in many ways, using ifs is a neater and easier alternative to using nested IPs. But as I said, it has not this sting in its tail that there is no option for a catch. Alvar al you. When you're working with something as straightforward as numeric values, it's very convenient and it's fairly easy to make sure that you cater for all eventualities . But in some other circumstances, it may need a bit more balls. So for that reason, the dysfunction is perhaps not quite as useful as it should be. I'll just demonstrate the danger to which I'm allude in. If we increase the size of formula bar again and simply take out this equal sign, we're now seeing greater than 30 in less than 30 a symbol era we now make. This found you exactly 30. Our logic hasn't tater for that number, so we get the not available error. Obviously, with numeric values, it's fairly easy to avoid making that error. But just to emphasize that, that's the danger with the assumption there is no capital that you have to cater for every possible outcome within the scenario that your evaluation 7. 3.4. Using VLOOKUP instead of IF: when you use investor tips to test numeric values. What alternative is to use the V look up function? Let's take an example that's open 04 Use envy. Look up instead of it. So here we have the same example that we've used in the last two videos and since the criteria that we want to use are laid out in a table, we could use this as I will look up table and then simply use the look up as an alternative to having the If logic on the only provides er is that we've arranged our table in ascending order. So we have a numeric values in the first column, and they've been sorted in ascending order. So the B look up will automatically take each of the values in column D and compare them to the values in the first column of the look up table. So it will start on this column, and in the case of D two, it's gonna compare 20 to the cell below on because the number below is greater. It wouldn't move down. It will stay on these roads, and we can then move across the column, too, to see what status is appropriate for that value. So the if logic that we implemented manually is implemented automatically by the V Look up function. There's no nesting required on this one, so we can just write it. Alarm online equals B. Look up, and then we have our four parameters. Look up values, of course. E to. If it's difficult to click on the selling one, remember, you can always press the arrow keys to move to the soul. So here I'm gonna press the left era comma for second values table array. We could simply drag across the column. Headings like this G and H and level, of course, include the title, which being text is just equivalent to zero. If we wanted to have just the values he could drag cross posed. But of course we have to remember to press at all to insert the dollar signs so that when we copy the table values, they're still being addressed. My own personal preferences wherever possible to track across the column headings coma. Then we specify the column within the look up table, which contains the answer that we want to display. So it's a two column table and the answer is in the second column off at two column table. So he put it to on not an H that this third parameter is always knew. Married. It's never letter about given column. And then, finally, at Excel promises. Are we looking for an approximate match or an exact match? And in this case, of course, looking for an approximate match. We just want to put each of these numeric values into the appropriate band, and that's it. We don't even need to put the clothes in parenthesis because there's no nest in so we can just presenter on Excel will insert it for us. We can then coming down. So if you do find yourself using a nest, if which has lots of possible values or is purely numeric, as in this case, and you might consider just constructing a look up table could be on a hidden work feet even on then using that to implement the logic rather than having what often becomes a very complex nested. If structure 8. 3.5. The SWITCH function: let's now move on to look at the switch function. And this is another function, which will only work in office 365 and Excel 2016. That's going to see right five. And in this example, we want to create what I've called a formal interview date based on the short date in column G by formal, I simply mean a date, which includes the orginal on the first would want to display the S T on the second, the nd on the third, the RV and for all other days, the th that's first of all, look at how the switch function works and let's say that we use in an interview date of the 24th of October 2017 and we want to find out what orginal is appropriate for that date. So the first parameter of the switch function it's an expression. This is the expression that you want to evaluate and then specify a different series of results, depending on what value is the outcome off this expression. So the expression were evaluating is the day off the interview dates, and of course, we used the day function to obtain that in this case. The result is 24. Then we have a series of pairs value One result one value to result to and so forth. The value is one possible value or the expression that we've specified. And in this case, we want to combine values. So we use the or function to do this. If the day is 1 21 or 31 we want the result to be S t. Then for a second test again, we use all if the days two or 22 we want the results to be nd a man on third and final test again uses a little to combine 3 23 to produce a result of our D remember function as the default option. If none of the tests that you specified proves to be true, what default value you want to use? And of course, in the case of an orginal, it would be t age. And for the date that we're evaluating, the 24 th is the true value said the default would automatically kick in because all of the tests returned falls. So that's now right out formula. As usual, I'm going to expand the formula bar, and the first thing we want to display is the day and I'm going to use the text function. To do this so equals text, and what the text function enables us to do is to apply a format toe, one component within a formula. So here I'm a plane, a format purely to the day the text function takes two parameters. The first is the value to which you want to apply. The formats that this is cause is the interview date in G two comma, and the second is the format that you want to apply, and you'll notice that it's format text, which means that it's gotta go in double quotes. So here I want tohave the day displayed as a number with no leading zeros. And to do that I put a single bee to these would give me a numeric value with a leading 03 days would give me the short name of the day. As in money, M. O N. ANA calls. Four days will give me Monday before name of the day. That's the first part of our for more bait, and then I'm going to use the ampersand or can Cata Nation to tag on the rest. Let's now press all enter to go into a new line and then space bar a couple of times, and now we need our switch function. So switch first argument is expression. What do we want to evaluate? So we use the day function on G two, which contains the interview date, and that's our first argument. Coma Pressel. 10 toe men just aligned the next argument under the first. So now we need the first possible value. Or, in this case, values. We combine the values using the also that only one of them has to be true. So one coma, 21 calm at 31. So that's our first value value. One comma result. One. If the day proves to be any of those numbers, what value do you want to display at this point? And, of course, it's a text value, so it goes in double quotes and it's S t calmer. And then we simply repeat this line, so copy it wrestled. Enter on, just paste it in. So now we can have to on 22 which would give M d a paste three on 23 which would give him a D wall canto. And then finally we have our cattle, which is simply th and then close parentheses to end the switch statement. And it would be a good idea to insert spaces after the orginal soldiers do that. And then we tack on the rest of the date using ampersand again, our press old enter and again, I'll use the text function to achieve the appropriate for that in the value So which were applying the format is again G two comma men in quotes the format that we want. It's first of all, the full name of the month. That's full EMS, then a space on the full year. So that's four wise and then the clothes in parenthesis to end the text function. So that's how the switch function works. You may have encountered the structure in programming languages, so you use it when you're tested more item or lots of different possible outcomes. That's the same item each time, but its value can be different, and you bought the contents of the soul to be determined which of these values is equal to the thing that you're evaluated in this case, the day. All the interview date. That's it. We can enter collapse the formula on double take on the auto he'll handle to copy down. And, of course, what we end up with is not a date. It is a piece of text, so we can't before many date operations on this column. We can only do that on the original column G. 9. 3.6. IFERROR: Let's end our look at excels, logical functions by looking at a very simple but very useful function. If Karen and basically what the if Arab function does is to enable you to substitute potential era values returned by your formulas with something will use a friendly. Let's take an example here we want to create a formula to do some basic email validation, and are we going to do is to test for the presence of the at sign and the dot in each of our email addresses. So, as usual, let's expand our fold in the bar. So this, of course, will be an if statement. So as per usual, we start by specifying a logical test. And since this will be a compound test, we have to use all or And and in this case, we want both the acts and adult to be present. So we needed, and both conditions have to be satisfied. So we have. And now let's start with the outside. So we need a function which will test whether the at sign is present in cell F two on for this weekend. Use find. The first argument is the thing you're looking for, and that has to go in close. Of course, it's the outside comma on. The second argument is to sell that you're examining, which of course, is left to the first email address. Then we can close parentheses, permitting the final optional argument, and then we need to finish this off to make it into a test. If I say, for example, greater than one, this will test ISI that the string contains an ensign and it's not the first character, and then we can do the same for the dots find Don't enough to. And this time, let's say, greater than four. That completes out. And so you put the clothes in parentheses. Coma. That's our logical test completed. Let's press old enter for a new line and move on to the value is true. If the test is successful, we want the word valid to appear in the song coma, cancer and then for value of false. We want the word invalid that completes our If so, we can just put the clothes in parenthesis. So let's presente. So the email address, you know, two is valid. But when we copy down, what we find is that all those email addresses, which are invalid instead of returning the text in value, as we've asked, producing an Arab body. So we know in this example that every time there's an error, what we actually have is an invalid email address. What we want to do here is to replace this arrow value with the word in Balad, and that's exactly what the if error function enables us to do. So Here's a summary of the logic that we have in place using the and function. Have an inside it use the find function twice very fine the position of the at sign on the dots. So because this email address has no out sign, the first find operation produces an era value. Once that happens, the formula itself produces an error value so the value of true and valuable false parts of the if statement are processed. So the solution is to use if era it takes two arguments on the first argument is simply the entire formula that you want to evaluate. So the formula that we currently have will become the first argument, the value argument of the if Eric function and then the second arguments is the value that you want to appear in the cell whenever an error does a result from your formula. So in this case, we want the word invalid to replace the Arab Annie. So all we need to do is after the equal sign to put if error, I'll just press walk enter to emphasize that the if statement is the first argument of the era. And then that first argument is followed by a comma now completed the value arguments of the era. We just need the value, if error argument again on press Old Enter have in to line up with the if statement and put the word invalid and then a single close in parenthesis to end the era function. So let's test it and and then copy down. So now, whenever an error occurs, the word invalid replaces the error of value. 10. 4.1. Combining LEFT, SEARCH and VLOOKUP: let's now turn our attention to the manipulation of text. Let's take a look at some of the typical function combinations that you use when working with text, so we're currently in zero for manipulating text on Let's start with number one combining left search from the look up. Then this workbook We have to work feeds. The first has a record off all the highest of taking place, the second as the names off into the items that can be hired, saying the highest work seat. We have a high of ref column, but the higher ref has three components, and it's only the first of these three components that matches the items. So we need to have a formula which will extract all the text before the first hyphen. On this occasion, we can't simply use the left function because when you use left on its own, you need to specify the number of characters to extract started from the left. And as you can see that number very sometimes it's four on sometimes is free. There is, of course, in the data tab, this very useful feature text to columns. But remember, this is an operation. What we're trying to do here is to create a formula so that, given any higher up with these three components, we can extract the first component and then use the V look up function to find out the name of the correspondent item so that the name of the Iceman's probably all with me. Let's start by simply extract in the code so that we can look at using the to text functions in conjunction. So here what we're interested in doing, extracting all of the characters that precede the first hyphen so we can use the left function, which takes two parameters. First of all, the text from which you want to extract some characters on this, of course, is the text in the cell. Eight to the second argument is the number of characters, and for this we can't give a literal number. We can't say three or all because the number varies, so instead, use the search function, so the search function becomes the second argument of the left function. The search function, in turn, takes two arguments. The fine text, the text that you're looking for, which is we know, is the hyphen and then within text the selling which you're looking for that text. And as we've seen that stay, too, the search function returns a number, which is the character position of the stream that your searching for so in this case in selling to the hyphen is in character position. Fine. But of course, we don't want to extract five characters we want to extract. Or, in other words, we always want to extract one character fewer than the character position of the hyphen. So after the closing parenthesis for the search function, we need to put minus one so there is a lesson going on. So we'll split this onto several lines and for that will increase the size of the formula bar. So it's equals left then all enter to go to a new line, and the first parameter is the text. Which is, you can see is the text in the adjacent column in a two that's our first parameter old enter again and then for the second parameter, we now need to use a function which will identify the character position of the first hyphen. And there two candidates search and find find is case sensitive, and search is not. And for that reason it's probably best to think of searches, the standard function and then only use. Find on those occasions where you definitely need to take the case into account so he will use such the fine text has to go in double quotes, and it's the hyphen. Within text is the cell that we're searching in, which is a two. If we admit the third parameter, which is you can see, is optional. That's the starting. Um, we know it's optional because it's in square brackets. Excel will simply start the search on character Position one. What is fine. So he closed when we found the position of the hyphen. We then need to subtract one. As you can see in this first example, the hyphen is in position. Five. We don't want the 1st 5 characters. We want the 1st 4 so we now need minus one, and that completes our left function. So just press old enter and put the clothes in parenthesis on a separate line. So when we press center, we'll see we have all the characters before the hype in. If I copied down that it works both for three letters on four letters. In reality, we probably wouldn't bother to have a separate column just for the item code. What we really need to know is the name of the item. But in a learning scenario is always useful to have a look at these things in isolation on Just look at the components that go together to make up the whole. So what we can do now is to highlight all of this and copy it Amanda's press escape to come out of formula editing. And now we can use to be look up function to look up the item code that we've extracted in columns A and B, all the adjacent work she called items. So here we using the V look up function to retrieve the name of the item that was hired that we look up function takes four parameters. First of all, the look at value, this is the information that you already have. So in the case of Sally to, we would want to look up as MTBE. Next we have the table of Ray where you looking all that value. So we'll be looking in the adjacent work feed cool items in Collins A and B, and the third argument is the column index number within the look up table. Which column contains the answer, and it's always the Americ. So we put two and not be. And then the final parameter range look up specifies whether you're looking for an exact match or an approximate one because we're looking for an exact match. We put the word falls so the lost three arguments is straight forward. But first we need to use a function which will extract the characters that we want to look up on. The function use is, of course, the left so equals the look up old mentor and paste. I mean, losses press the space, but it's have everything in So all of this becomes the first argument of the look up, which is, you can see is look up value. So we put a comma all 10 and then on to the second argument would be look up, which is the table of Ray. This, of course, is in the adjacent worksheet items. We want the 1st 2 columns A and B calmer Walt enter, and then we simply specify which column in that table contains the answer and this is always numeric. So it's not be it's too calmer or center. And then in the final parameter that the look up we use false to indicate that we want an exact match Old and, uh and in the closing parenthesis for me Look up. So when we press center waken see, that s nbt refers to a Samsung tablet and we can then just copy the formula down. So there we have a very typical combination Well manipulating text, namely left and such. In the next video, we'll move on to look at combine in mid and search No. 11. 4.2. Combining MID, SEARCH and IF: Let's move on to look at another very typical combination when you're working with text at last, the use of the mid and search functions. Zero for manipulating text. Let's open up zero to combining mid search. And if what we want to do in this video is to find out the type of usage, whether it was internal or external, and we can do that by examining the letters in the middle of the higher reference, they'll either be i, N T or E X T. And again because the letters that precede the hyphen very sometimes for sometimes three we'll need to use the search function. But this time we use in in combination with mid rather than left because obviously we started in the middle of the string, not starting from the left. So again, what we really want to have is usage. We want either internal or external in the usage column, but just doesn't learning mechanism. That's first of all, extract the i m. T. Using a combination of minute and such. The mid function takes three parameters Festival. We have text, and this is simply the cell that contains the text you're examining second we have to start number the character position from which you want to start extracting text and thirdly, the number of characters that you want to extract. So, in our example, texted, straightforward, we're looking in Cell eight to, and the number of characters is straightforward. It's always three because the letters are either i nt for internal or txt for external. So the thing that's not straightforward is the start number. Which character position do we start from when we're extracting text? So because of that, we use the search function to supply that parameter. The search function takes two parameters. The fine text is what we're looking for. It is a hyphen, and within text is the cell that you're examining, which is self a two that will tell us the character position off that first hyphen. But what we then need to do is to live one character position to the right. So after we close that parentheses for the search function, we need to put plus one so that we don't include the hyphen in the text that were extracted . So that's right. Our function un. Expand the formula virus per usual, so we want equals mid and then I'll press fold enter because we'll be less than another function inside made. So the first parameter is text. That's the cell that we're examining, which is 82 coma, all tender. And then we, specifying a start position my nest in the search function, have a search function as its two parameters that find text, which, of course, has to go in double quotes. That's the hyphen. Call me within text the cell that you looking in, which is a two. But then the start number will omit because we want to start from character Position one. But then we add one plus one to make sure that we don't include the hyphen itself. We want the character that follows the hyphen. Come on. So all of that is the second parameter about made function. All 10 and in the third parameter is the number of characters which is fixed at three and then old Enter on the closing for emphasis. So when we press end, so we should get I in t. And then as we caught me down alive that I nt or the X t each time, so that gives you a bit of practice on using mid and search in combination. There are very common combination in a similar way to the use of left and search which we saw in the previous video. So now let's copy the formula that we've got so far excluding, of course, the equal sign in his press escape once you've copied and then we can use this as one of the parameters for the if statement that we now need. So what we want to say basically, is that if the mid function gives us, i n t put the word internal into Sally too. Otherwise for the word external. So the it function becomes the outer function and the function takes three arguments. First a logical test, then evaluate true and then a value the false. So for a logical test, well, basically nest the mid formula that we created so far. So we've got the text that start number, number of characters. And then, as we saw, this start number is being supplied by the search function which has iPhone is it's fine text and so a two as the within text. But then we and one to the result in character position now because we want this to be a logical test. What we then have to do is to use a logical operator. And obviously equality is the thing that we're testing all. Does the result produced by the mid function equal the letters I NT? So that would be a logical test. I test that can only be true or false value of truth. We put the string internal into the cell, value it false. Who put the string external. So let's right now, function equals if Walt enter and then I'll just paste in before Mueller that copy. Remember to use the space Barton s this so that was the formula we had before. But as it stands, it's not a logical test. And since it's acting is the first parameter of an if statement, it needs to be converted into a logical test. And we do that simply by Adan equals and then, of course, in quotes, because it's a string e x t. So now we have a logical test. If the result produced, my maid equals txt coma, old enter and then the second parameter which were align in underneath mid, which was the first prime Minister If is the body is true, which is a string. It is the word internal Homa or cancer Valley falls external Walt, enter on the clothes in parenthesis for the if statement And I just noticed that I've got this the wrong way around I should be testing for I N t And then the positive values internal the negative values next time. So let's ends around formula class the full huh? And called me down Whenever we have I nt we get internal on Whenever we have txt, we get X double. 12. 4.3. Combining RIGHT, LEN and SEARCH: Let's continue our local techniques for extracting one string from another by examining the combined use of right Len and Search zero for manipulating tax that's open up 03 So we're working with the same example as in the previous two videos on What we want to do this time is to extract the higher code from eight to on. The high coat is basically either all five numbers at the end off the string. So the fact that we have a variable number of characters, which we're extracting starting from the right, is that we need to nest a function or, in this case, two functions in order to calculate the number of characters to be extracted. So let's have a look at how we combine, right Len in search How else a function will be the right function. This takes two arguments. First of all, we have the text, which is the cell eight to, and then we have the number of characters on. Because this varies, we need to use another couple of functions that calculated So the technique will use is first of all to use the land barge in the target. The final character within the string. So the length of the string is 13 which is the case with the string in A to Len. A two will target the 13 or last character, and then from that we subtract the character position, all that final hyphen. And as we've seen in previous videos to calculate the position of the hyphen, use the search function that takes two arguments. The thing you looking for but find text is the thing you're looking for, which will be our hyphen. And the within text is the cell within which your surgeon, which is sell eight to So given the current contents of a to then a two, will take us to Position 13 whereas search for the hyphen in a two will either take us to position five or to position nine in order to make sure that we end up on the second hyphen , which is character nine. Within the string, we need to use the certain parameter the optional parameter of the search function, and that is starting. Um, in the previous examples, we've admitted starting them since it defaults toe one, which means that the function will start searching on the first character if we sent start numb to six. This means that we're always starting the search beyond the position of the first hype in, since the number of characters that precedes it is a maximum of four, the maximum character position of the first hype and is five. Therefore, by specifying that are starting on the six, we ensure that we miss out the first hype in, which means that the first high phone will find will be hyphen number two. So let's write our function. I expand the formula. Buyers per usual equals right, and since we'll be nest in another function inside it, that's useful. Enter to put the arguments on separate lines. The first argument is the text just simply eight to comma or center, and the second argument is the number of characters. So for this we start with Len as in length, and we want the length of a to And then from that, we want to subtract the character position off the second hyphen. So this is where we use our search. Find text has to go in double quotes, and it's a hyphen comma found within tax where own searching is sell a to comma and then are optional. Start Numb is six because we want to start searching from character Position six. So that calculation is the second argument off our right function on. We finish with Walt enter and then the closing bracket. So now we can answer the formula and let me call me down Begin. Siano too. We have four numbers at the end, which we've extracted, but on Road five, where we have five digits following the hyphen, we've successfully extracted those. And if you're thinking, could we have done that using the mid function? Yes, we could, but I wanted to practice this combination of right Len and Search, since it's a classic example, or a complex formula which uses a classic combination or excel functions. 13. 4.4. Creating a sentence case formula: If you're a user of Microsoft Word, you're almost certainly familiar with sentence case. Seven example. Here I can select all of these lines and go to change case and then choose sentence case and you'll notice that word capitalizes. The first letter on each line, although Excel has functions, will change in the case. It doesn't offer a sentence case option, so we're going to create a formula which does just that. And for good measure, we'll put a full stop at the end as well. So we're working in zero for manipulating text. That's no open up 04 creating a sentence case formula. So here we have a worksheet, which records I t support calls, and we want to tidy up Colin E. We'll notice that the case is inconsistent, I say, for reporting purposes we'd like toe have each Hendry start with a capital letter so and end with a full stop. So to do this will use the concatenation operator M percent, So the first thing we'll do is to extract the first character and converted to uppercase so we'll use the upper function on this takes a single argument, which is referred to as text, and that's the cell that you want to convert to a case. But instead of a cell reference E to which would imply the entire entry, we use the left function to extract. The first letter, as we've seen with a function, requires two arguments the text that you want to operate on, which is me too. I have a number of characters you want to extract, starting from the left, which is of course, one. Then we use the concatenation operator, but essentially means followed by what we then once I have. Is all of the characters within two bar the first letter. So we use the right function to achieve. This takes two parameters. The text that you operating on launch again is E to and then the number of characters. To calculate this, we use the length function as in lens, which has a single argument. The string that you want to know the land called. That of course, is to, but we don't want to tag on the entire string. We want to tag on the string bother first letter. So after the close in parenthesis of the land function we need to put minus one. So let's create a new column. Well, just initially copy everything. So we've got the formats the same and then deletes all of these entries. And as usual, I'm gonna expand the formula bar on. Let's create our formula. So the first component is upper equals upper. We nest in the left function. So I'm gonna use old enter left. The text is too comer on a number of characters is one, and that completes the upper function. Then we need an percent 57 frickin castle nation. Well, and, uh, and now we need the right function on because it will have Len nested inside it on diesel tender again. How text he to call me goaltender. But the number of characters we use, Len E to minus one or cancer on the clothes in parenthesis for the right function. And then finally, I'm going to tag on using ampersand and, of course, quotes. Full stop. So we extract in the first character capitalising eggs, and then can Captain Eytan starting from the right. All the characters inside e to which we pick up using lend me to bother first that we do that simply by saying minus one, and then we take it on the whole. So and that's it. So here we've got a tidied up version off the original entry. Ondas. We copy that down. All of the entries now resemble sentences. 14. 4.5. Combining LEN and SUBSTITUTE: the next text function combination will examine is Len on substitute, So zero for manipulating text that's open 05 Combining Len and Substitute The land function is, of course, used to measure the length of the string within a cell, and substitute is used to replace one string with another within a given cell. In this work feet, we can see a couple of examples of how we can combine these two functions to get some useful results In column A. We have the copy of our advertisements and in column B, we want to count the number of characters within the advert, admit in spaces and in column C. We want to count the number of words within the ad. We can solve both of these requirements by the combined use of Len and Substitute. So let's start with characters without spaces. We start by using the len function, and this takes a single argument the cell containing the text that you want the lens off. And this, of course, is sell a to however, because we want to admit spaces instead of referring directly to sell A to we nest. The substitute function on the substitute function takes three arguments the cell on which you want to perform the replace operation, which is of course, a to the old text, which is the text that you want to replace. And that's a space and the new text. What do you want to replace the old text with? We want to replace a space with nothing. So once we've performed the substitute operation on A to, we have the original string minus all of the spaces. So let's type out. First formula equals land. Then I'll press walk, enter to nest. The substitute function our text parameter is a to comma old text which, of course has to go in quotes is a space comma. And now new tests which also has to get in Khost is nothing. So we simply put double quotes, double quotes with nothing between them, close parentheses and the substitute. Um, I'll just press walt enter on the line. The clothes in parenthesis off the land function underneath the opening parenthesis, and that's our function. So to test it in selling to just temporarily type a space, be space. See when I press enter, I should get three letters and let's undo Let's just copy before me that down and move on to the word count. Imagine that we have three words in cell 82 will have to spaces one space between the first and the second word. One space between the second and the third. If we had four words would have three spaces and so forth. So the number of words is always going to be one more than the number of spaces. That's the logic that we use to calculate the number of words we start by taking the length of the entire string using the lens function, which takes a single argument. I either text that you want to measure and that, of course, is sell me too. Then, from that total figure, we subtract the length not at the original string, but of the original string with spaces removed, and we use this substitute function to perform the removal operation. The substitute function takes three arguments the cell containing the text, which is a to the old text, the text you want to get rid on, which is a space, and the new text, the text you want to replace the old text with, which is nothing So when we perform this calculation, we'll end up with a number of spaces. And as we've just seen, once you have a number of spaces, you just had one, and you then have the number of words so onto our word count function. And since we using this technique account in the number of spaces, it's important that we make sure we only have one space between each pair of words. If that's not the case, we'll obviously get the wrong result. So, for example, of deliberately put a couple of spaces in here, we could build logic into our formula, which ignores these extra spaces. But I think it makes far more sense just to create an extra column here. And let's just call it a copy trimmed and use the trim function to remove those extra spaces. That's just equals trim. Sally, too, and we don't. So now we know that when we have by our word, camp will only have one space between words. This is one of the three things that the trim function does. It removes spaces at the end at the beginning, and when it finds multiple spaces between words, it contracts thumb down to one space well company. The four minute down and then our word count will apply to the trend version of the ad copy , not the original. It's on formula. So equals Len on. We want be to minus now. I use all tender on, but the second land here, all tender again on the nested substitute text is B two comma. Old text is a space. New text is nothing, which, of course, is double quotes, double quotes and the clothes in parenthesis for Len. So so far we've calculated the number of spaces express all kinds of one small men just put plus one so that we now have the number of words that sounds the formula and to test it are going to the original cell eight to just type word space based face, word, space, space word. Just a test that we'll ignore the extra spaces the women present. It currently calculates that we have three words, and let's undo that we could just company to formula down. So there we have a couple of examples of using two very useful text functions. Len on substitute in Conjunction 15. 4.6. Brute force substitution: in the next two videos will examine the intensive use of the substitute culture where you replace in a significant number of values. And this is a technique that I refer to his group Force substitution. Let me show you how it works. So zero or manipulating text. Let's open up 06 rubles substitution. We have a situation similar to one that we've seen in the previous video, where we have a customer code that actually contains three components. We have a series of numbers which we want to extract is the customer I D. That we have two letters which we want to extract is a country code and then we have a series of numbers. What we want to extract is a sector code, but in this example we have a slight problem. The number of digits that preceded the two letters varies, and the numbers that followed the two letters also very and there no convenient characters such as hyphens, which occur consistently in every entry. So the only thing that we have is the fact that we have two letters somewhere in the middle of the customer code. So the brute force substitution solution to This involves combining the left mid and write functions with search, as we've seen in previous videos. And then when we search, we treat any letter as a single character, so I'm going to use the hyphen. But you can use any character you like, so there are a lot of moving parts to this. The first thing we need to do is to get actually training folder. Let's just open up 06 substitution template. So here we want to create a formula which will gradually build the substitute formula that we want. So the way it works is that you put your old text in this column on the new text whatever you want to replace the old text with in the adjacent column. So this example, the thing that we're replacing is every letter in the alphabet. So let's go ahead and find the letters of the alphabet. That's always when in doubt, we go to the Internet and do a search on here. We've got a Wikipedia example that will do the trick service in the copy all of the upper case letters and paste them into our template, just paste them initially as a row I'll call them and use the transposed option to paste them as a column. And I can just delete what I originally pasted and then for the new text. I'm just gonna put a hyphen and copy that all the way down. So as I said, there were quite a few moving parts to this. So that's the set up. Let's now look at how we build formula in this cell. We put this cell that we want the formula to refer to. So, of course, when we use the formula, we're gonna be referring to A. To that, the substitute function is case sensitive. So we have two choices we can either find, then copy all the lower case letters or we can simply transform the contents of a to upper case on. Obviously, the latter is preferable because it's just one operation. So instead of just type in eight to, I'm going to type upper open brackets a too close bracket. In other words, we're applying the substitution to the upper case version of the contents of self A two. So now we create a formula which will generate the formula that we actually want, So we type equals on the formula that we want uses the substitute function. So in quotes it put substitute open parentheses. Let me just remind you of the three parameters that the substitute function news is so First of all, we have text. This is the text that we want a razor which is sell A to which we've converted to uppercase using the other function. Then we have the old text, which will be the column containing the upper case letters and the new text, which will be the hyphen that we've put in the adjacent column. So to get our formula to generate that formula, we now need to close our quotes and use concatenation and percent, and can captain ate whatever is in the cell above. So if I click on the salad bar, which is a two that completes our first argument, all those presents is so you can see what we're building. So that's the start of the formula that we want to end up with substitute Upper A two, which is the first argument of substitute. So now need a comma. The men we can move on to the second argument, the ampersand close so to close the first argument. We put a comma and to open the second argument, we now need quotes. So what we want to do is to put quotes inside, quotes. And whenever you need to do this in Excel, you simply double up. So we type two quotation marks on that means close, and now we put the regular closing quotation mark. So it's difficult beauty on the screen that actually got three double quotes next to each other. Let's to stand her again so you can see what we build in. So we've got substitute first argument, comma and then the quotes for the second argument to put something inside the quotes. We just but ampersand the three and then put the clothes in quotes. Ember send open quotation marks, double up on the quote so instead to double quick. And then we can go into the third argument, said comma. Double quotes around the third argument close clues. So if your lives in count that was ampersand. Three. Quote, coma and three quotes again Finish. We won't hamper Send C three. So that sound. New text arguments and finally to close off. We need double close, open quotation marks, quotes, quotes to indicate double quotes and then the closing quotation mark. So four quotes at this point room and we enter, and the anything I forgot was the clothes in parenthesis. So I'll just put back here just before the final double quote, and that completes our formula. So basically, what we now need to do is to simply copy this down. So I was like, opiate down. You can see that the original formula becomes the argument of a new substitute function, and that's the way that substitute works. You could say it's the reverse of lest in what you're doing each time is your rap in a new substitute function around the existing one. So we start with a single substitute permission, and then that original substitute function becomes the text argument. The first argument off another substitute function any, then replace the second letter. And then all of that becomes the text argument of the third substitute, and you replace the third letter. You do that all the way up until you get to said so we'll eventually have 26 substitutes wrapped around each other in this fashion, so that may sound extremely complex. but because we have this formula that generates the formula, Horace, what we need to do is to copy it down, move to the very bottom and then the final formula that we actually need is this one. So we can copy it into note pad or any text editor. And that's what it looks like because you can see we started with the original Upper A to and then we just applied successive substitution on that initial value. So if you count all the bees still in 26 of them, one replacing these letter of the alphabet and each time the letter is always being replaced with a hyphen. So every time we use this code, whatever letters are in cell eight to will always be replaced by hyphens. And then we use the techniques that we saw in previous videos to track the position of the hyphen. Using the search function on man were in familiar territory. That's no good and waste all functions. Some templates finished. And the great thing about the template is, once you've done this rather complex formula, you can just keep it on. Any time you want to use this technique, you simply replace these two columns. When we look at other examples of using this template during the course, say, for the moment, we can just close it down and save our changes. So now we want the customer by B and this is gonna be used in the left function. So let's just expand that formula. Bar has been usual. So just to remind you of the technique use the left function is the outer function. And it takes two arguments the text from which you are extracting characters, which is a two, and then for the number of characters we used to such function. The fine text is the character that you're looking for and this is where I'm brute force Substitution comes in so that formula that we've generated begin paste in at that point, and it effectively means will be certain for hyphens. But within text, of course, is where we're searching, which of course, is a to. And then you got to remember that when we find the position around hyphen, which will be where the UK is, because the substitute rule in place all letters with hyphens. We don't have to subtract one, because otherwise in this case will be picking up the new as well of the number that precedes the letters. So we type equals left center. That's parameter is text, which is a two Coleman or cancer. And then the second perimeter is supplied by search or enter Find text. What are you searching? Call, which is a hyphen Coma within text, wary. Looking for it. This is where we paste in our code just across the note pad or text editor and copy it repast. It'll then? So in other words, we're not searching inside a two per se. We're searching inside a modified her shin off a 21 which has hyphens replacing the original letters Well then and that completes our search functions as the closing parenthesis for that. And then the search function is in turn the second argument off the left function so old enter again and the final closing parenthesis All the left function Well, we enter. You can see that we've got the number, but because I forgot to put the minus one. We've also got the letter, so we simply need after the search which closes here to insert minus one. So we're not picking up the first letter and that's it. So that's how left and weaken vendors copy this one down and we'll see each time. It's just picking up that variable number of digits. So now on to the country code. This time, when using, admit. So the mid function takes three arguments because for me, the text which will be the text in a to and then for the start number, will use the search function. And then the final text will be out Group for Substitution Code, which I've called mega sub in this diagram on again within text is a to this time there's no adjustment necessary. When we find the hyphen, we're in the right position. So the country code equals made cantor. The text is a to comma and then start number, search, enter or in Texas hyphen. You call may for enter and then we paste in our mega sub code or enter and that completes our search. It was important Asus for that coma and then we're back into the mid function. We've completed 1st 2 arguments, text and starting them. Now we want a number of characters so bold Enter. I have a number of characters is a ones, too. It's a two letter code that complete sound made that all winter on the closure for emphasis that gives us our country code, which we can then cut me down in per sector code. Let's use the right function. So to use the right function, use Len to find the final count the position. And from that you search to find the position of the hyphens. So right itself takes two arguments. The text that you're looking inside, which is 82 then the number of characters we use Len Onley to that. Texas is a final character position minus and then we use such and now mega sub to find the position of the first hyphen within the to that will correspond to the position of the U. So, of course, if we left it at that, we'd end up including the K in the stream that's being extracted. So we need to subtract one from the total to avoid picking up the second letter of the country code. So equals rights. Enter text is a to Coleman enter and the number of characters we have Len off a two minus search well, canto by in Texas the hyphen Call me canto. I mean, for within text you paste in, make a code cancer because his a man minus one and that completes our right. So old enter on an equation. Parenthesis or the right policy. So there we have It seems like 2795 on we can help me down someone who is crabbing the numbers which follow the letters. So, as you can see, even though we've got this fairly complex looking formula, once you have a template that can generate that code, you can just paste it anywhere. And it solves quite a wide range of problems. Here's just one example, and we'll look at another example in the next video. 16. 4.7. Creating a title case formula: in this video, we'll get some more practice on using the brute pool substitution technique that we saw in the last video. So zero for manipulating text that's going to number seven, creating a title case formula. But what we want to do in this example, is to come up with a formula which will transform the text in this color into dramatically correct titles. Now, as you probably know, Excel has a function called proper, which will capitalize the first letter image word. This doesn't always give a viable title. Let's put it to the test. Let's begin by inside in the colon. And then we could copy the title, had her across and then just use equals proper. Press the left. Arum to reference the adjacent a two and enter on the first couple of titles. We have no problems. When we get to titles three and four. We can see that a off and should all be in lower case If I move across to the other worksheet that we have here in lower case list. I've got a list of other words and phrases that should always be in lower case when they occur in the title, and I should add that I've made no great efforts to make this list complete. So you are using this technique in a real life situation. The onus is on you to actually come up with a list they to improve on the proper function. What we can do is having applied the proper function to a to We can then use our fruitful substitution 10 week to replace all of these words with their lower case equivalents whenever they occur in its idle. So let's just undo all of that. But leave the new column in place, and it's now open up our template so that a second template 07 substitution template it is currently blank. Here's the formula that we put together in the last video, so we just need to copy all of those words into the old text column. However, we can't copy the words as they are. For example, if we change every occurrence of a the indefinite article to its lower case equivalent, all words beginning with a would also become lower case and hear the word Andes would also become low case because it starts with a nd so what we need to do is to make sure that we have in place these words when they are recognized as words. Nicole's what makes them recognize the buzzwords is where they're preceded by a space notice I say preceded rather than followed. Because, of course, if one of these words is the first word within a title, it shouldn't be converted to lower case on. The formula for doing this is fairly straightforward because and then the space has to go double quotes and percent. I'll just press the left arrow to reference they won and that sound formula. And then we just company that down So you can see by the slight in dende at the start of each of these entries. We've now been a space inserted before each of these words and phrases. So this is what we copy into our time place. And of course, we don't want to pace the formula. We want to paste values then for the new text. In other words, detects that we want to convert this into we simply use equals lower press the left an arrow to pick up you, Jason itself on enter and then we can copy to fool me that down and then to build our final formula, we just need to specify the cell that were initially applying the substitute to. So we go back into the worksheet titles. That cell, of course, is a to, but what we want a blind, too, is the proper version of A to and I'll template. We need to type proper brackets. A. To that sounds starting formula was certainly a space, followed by other case, a changing into space, followed by love in case A. Then, as I copy that down uses, the Saleh bothers the input, the men does the second substitution the third and so forth. So we simply copy these all the way to the bottom. All of these values then get substituted with the values in column C on the final or villa contains all of their substitution Sylvester one that we copy. And of course, the reason I have to paste this into the text editor that he copious several in excel. You can only paste it into another cell, so we need to get it onto the general clipboard, pasting it into a text. Eggs are like night pad will do the drink. So here's our formula. Unlisted selected all on Copy it. Now we're ready to build our final formula. That's just expand the formula bar. So this time what we have on the clipboard is literally all they'll need. So we just begin with an equal sign and then we based everything. And that's our completed formula. So let's copy it down so we can see it's working off the A etcetera. All of these words are now in lower case. But when they care at the start of the title, such as The Lord of the Rings, you'll see that the upper case initial letter is retained. The only problem I can see is on this line. Gulliver's Travels, where we have Apostrophe s Excel is treated the apostrophe as being the end of one word and therefore s is the beginning of another word. So we obviously need to modify our template to take this into account. Now, this is not to say that every time we have an apostrophe, we now have to look at the following letter and make sure it's lower case. If you think about it, Apostrophe s is the only problem, say, for example, it Swift had called his hero Ohara instead of Gulliver. So the book title was actually Oh, Horace travels. We wouldn't want the age to become lower case. We would want it to stay uppercase. So apostrophe s is really the only thing that we need to change the lower case and the logical way of doing that just to bring back out templates, add it to the bottom and put the lower case substitution. Now, apostrophe has a special meaning in excel, so we can't simply type apostrophe uppercase s. Instead, what we'll have to do is to use a formula to generate that string equals quotes. And then inside the quotes, we put apostrophe uppercase s and then replace that with the equivalent lower case formula equals quotes, apostrophes low case s close quotes. So by doing that with Force in Excel, to treat the apostrophe is a literal string instead of assuming that it has a special function, as it normally does. And of course, that special function is to indicate that we have a number stores text, so we just need to add that into a formula. And we do that by copying the formula down and then we'll just copy that new version. So, really, this one, Just paste it here for safekeeping. A man just copy it into our solution. So what goes? No place is the new fashion, so we can see that we've killed the problem you've now put on Horace travels. Better change that back to Gulliver's Travels. So if you try this in a real scenario, I'm sure you'll also come up with other anomalies. This will be especially true. Perhaps if you're not working in English, you're working with languages that have accents and so forth. You may find that there are anomalies that you need Teoh take into account. But hopefully simply by using this technique of brutal substitution, you could just come up with substitution strings which will solve all of these problems for you. 17. 4.8. Conditional concatenation: We've already seen seven examples of concatenation using the ampersand operator in this video, we'll have a look at making concatenation conditional zero for manipulating text that's open up 08 conditional concatenation on What we want to do in this solution is to have the user choose a company name and have the address of that company each line automatically inserted. To do that, we use be look up and I'll look up Table will be in the customer's worksheet. This will be the look up column, and then we'll call back each of the four lines of the address three of the lines and never empty. But the second line of the address it's sometimes blank, and this is the one that will need to make conditional. So let's begin our formula by pulling across the first line. So equals V. Look up. The thing we're looking up is the cell above 85 comma and the look up table is in customers . Columns 80 e Comma. The column that we need is column, too Coma, then zero or calls for an exact match. So no problems so far. If we change the company name the corresponding first line comes across. So let's now have a think about how we need to approach it when we come to this column. So we'll need an if statement and for the logical test. Well, testes. See whether V look up, returns a blank. So we use exactly the same view the cup that we've just done. And then to make it into a logical test, I will simply say not equal to nothing but the value of true. We don't just want the address. We want the address on a new line so we can use excels, character function or char with the number 10 to achieve that. And then after the new line, we would put the results off. I'll be look up off column three of the look up table on the value of false will only kick in when the villa camp produces a blank value. And in that case we simply insert nothing. And, of course, when I say nothing, I'm repairing to to double quotes, but nothing between land the null string. So let's go back to the invoice form and continue our formula so conditional concatenation . We still use the regular M percent all then toe. And then we deserve our if statement all cancer. Since we're now gonna nest, be look up, which will be essentially the same as the first. So let's just copy the rest of it. And, of course, we now on color three. Then to convert that into a logical test, Use Matti call to been old string quote squints. That's a logical test. Coma old Enter, I meant value. True, We need this preceded by current to 10. So character 10 ampersand we look up that will insert a new line and then put the result of our be look up coma goaltender. And then finally we put the value of false, which is close quotes, a little string, well, Vento and then the clothes in parenthesis to finish the if statement, Let's check with that. All works so back. So leisure do have something in that column. Let's find a customer that doesn't have a day of manufacturing, for example, and we can see that nothing is inserted and we don't get a narrow. So let's just finish out formula. So we've used conditional Comcast a nation to take care of the column, which is sometimes blank the last two columns never blank, so we could just copy this line and then using to put across the last two columns. I m percent and I'll just split. It's on to another line. This one will be calling for ampersand, old enter and column five, and that completes our formula. So the company up it their manufacturing have three lines of address. Where is the backs of leisure? Have all lines? And by using conditional concatenation, we've eliminated the possibility have in blank lines. 18. 4.9. Starts with a number: that's. And now look a text manipulation formulas by creating a formula which test to see whether the contents of ourselves start with a number. So zero for manipulating text that's taken 09 So in this example we have a series of topic headings, and then we've got some paragraphs below each headed, and what we want to do, basically, in the Topic Level column, is to assign a level based on whether the text starts with a number or not. You'll see that each of the topic Allen's begins with a number, and in this example, we want to end up with the appropriate HTML tanks placed around the original entries. So the training folder, if you open up the HTML file what we want to do is to paste our topics below the appropriate headed excellent production for Excel Intermediate. So the level column will tell us which is which. That's the last topic related to Excel introduction. And as I said, the number will tell us whether this is a heading or whether it should be body text. Let's look at the functions that will need to achieve this so naturally the first function we're going to use is if and as we've seen, it has three arguments, the first being logical test a test which has to produce the value, true or false, the test. Whether something America we can use the is number function and this is inherently 1,000,000,000. It can only produce the result true or holes. It takes a single argument which excel calls value and to supply that argument. The logical thing is to assume that we would use the left function, which we've seen several times first thrown into text the cell that you want to operate on , which is due to second parameter number of characters. We want the first character only. However, there's a slight problem in this approach, which is that all text functions, including left, will always produce a text result. So even though the character we're extracting may look like a number by virtue of the fact that that number has been produced, uses a text function. That character will always be treated as a textual character, never numeric. So the value being processed by these number function is supplied by the left function. The result will always be false. We will never have a number. So before evaluating the character that we've extracted, we need to use the value function in order to attend, to convert that character to a number. And if it is a number, that attempt will be successful and the is number. Function will then produce true. So I heard the fact that we've got value value in a diagram hasn't confused you. His number takes a single argument, which excel calls value and then we use in the value function. Just apply that argument. So we have our true or false. And then the second argument idiot is valued true. If the first character is numeric, we want to change Selby to into ahead in three. And if the value is false, In other words, if the first character is not the America, we want to change selfie to into a paragraph. And if you haven't done much with Web development, just a point out that HTML has six headings. The pace that we use in this is headed one h one these ahead in twos. So back in Excel, we want this to be head in three. So let's write our function office expound the formula bar so we start with equals If and then we nest in. So old mentor is number. And again we're next in so old mentor value. And then we lest in left inside values overall, enter left. First argument. What's the text that you're operating on? Its D to coma? A second argument. Number of characters. How many characters are you extracting from B to one character? So that's our left. And that's the only argument off the value function. So all cantor on the clothes in parenthesis. So the value function all cancer on the clothes in parenthesis for his number function. So that is number Statement is the first argument biological test argument off our if function. So no need a coma. And on to the second argument, your true so for value True. Want to convert D to into an HTML age three statement. So to do that in quotes, we put the opening tag age three close quotes and then we use the concatenation operator and percent tag on de too. And then we need the close in time and percent quotation marks because we're doing a literal string and then we put the clothes in H three tag, which in Html looks like this coma. So that completes the value of true on value. False is very similar. So I'm going to copy it all center and then just paste that in. And instead of age three, you know what a paragraph, which is simply he and that completes our if statement on our regular So all mentor and then we close in parenthesis. Is that the if statement? So when we price center D two should be converted into 83 because it starts with a number, so that's working. We can learn, just collapse up on the bar computer formula down. So what Excel has done for us? It's the generate the HTML marca that we need for our page. So we can now open up the HTML page in an editor. So you probably need to right click and choose open with, you know, Pat or similar editor isn't available. You can obviously just shoot another out something. Open it with night pack, so we want the markup for excellent reduction Here. Got a couple of that festival introduction ends here, so we can't be that on a student off page scroll down for the immediate Malcolm, which goes all the way to the bottom. So it's close and same. And then let's open the HTML page, my double quick in and then we can see we have our head in threes. That's the end of the first section and then excellent, and they get it. So complete sound. Look at manipulating text. Hopefully, you're getting used to the use or multiple functions to produce the result that you need, and hopefully you can see why. I prefer to split complex formulas onto several lines using all enter in our next section, we want to look at formulas which used look up in reference functions. 19. 5.1. Nesting VLOOKUP functions: in this section will focus on some of the advanced techniques and structures that you'll encounter when working with look up in reference functions. I will begin by looking at the nest in of the V look up function. So we now in 05 look up in reference and it's open up 01 In this example. We want to find one missing piece of information before we can carry out our analysis. And that's the branch that each of our account handlers works. However, to make things a little tricky, the only information we have to start with is an invoice number on. Therefore, we need to go through a chain before we can find the information we're actually looking for . So starting with our invoice number, we can go across to the invoices, work feet and find out the client i d. Once we've got a client, I d. We can go to clients and find the account handler who services that clients account. Once we have an account handler, we can go to the account handlers, work fees and find the bronze I d. That that account handler works. And then finally, once we've got a branch i d. We can use that in the branches, Work feet to find the name of the branch. So we'll need four B look up functions on this diagram shows the manner in which we need to nest them. The innermost look up will be the one that we're starting with the invoice number. We used the invoice number to find the client I d. And then that client I d becomes the look up value off our second we look up, which is wrapped around the first. When we use that client, I'd be to find the account handler. I d that account handler. I d in turn becomes the input of our third look up, which is wrapped around the 1st 2 And with this look up, we're using the account handle. I'd be to pull back the bronze I d. And then all our final will be Look up, Which will be the outermost one. We used the branch I d to retrieve the Brahms name. Let's go ahead and write out function in F two, but expand formula bar. So we start with the outermost be look up equals we look up all tender because we're now gonna nest inside it. Another V. Look up. Well, Ken, time on the same for the third on one more. So this is our innermost look up and we start by looking for the invoice number. So look at value on his innermost. To be look up is going to be be to the invoice number coma. Then our table array will be in invoices. We need to go from column A to see three columns calmer. And the answer we want to pull back is in the third of those three columns. So a column index number is three comma, and then we're looking for an exact match so we can either put false or zero. So this innermost the look up now becomes but look up, value argument of our second. We look up so we can put a comma old enter and then to save space, I'll put the remaining three arguments along one line. So the table array for our second we look up will be in clients, and again it spans from a to see three columns coma. And the answer we're looking for is again in the third column and common zero for an exact match. Walt enter and then let's put the clothes in parenthesis aligned with the opening parenthesis. Then this second we look up in terms becomes the look of value, the first parameter of our Thirdly, look up. So we put a comma to end that argument. Old enter men will just align the remaining arguments underneath the first argument, which was to be look up. So table array argument is now going to be an account handless, and this time our table array stretches from a right across the e, which is where we have the branch i d. So five columns comma on The answer we're looking for is in the fifth column Comma and zero for an exact match. We'll enter and then we allowing the clothes in parenthesis with the opening one on this, our third we look up now becomes the look at value argument for our final outermost Be look up So comma and the argument old enter and then that sidelined. The remaining arguments with the first, which was the third we look up table array this time is going to be in branches and it spends just the 1st 2 columns, coma. And, of course, the second of those two columns has the branch that we're looking for and common zero toe end cancer. In the final closing, parenthesis aligns with the opening one off our outermost look up on That's it. Let's Angela formula collapse the formula bar and copy down the formula by double clicking on the also feel handle. So that's the technique, or nest in B look ups. It's a fairly common requirement where you want to look something up, but there's no direct route to the information that you're trying to retrieve. So in this scenario, we're probably now copy out data and then paste values rather than leaving the data volatile, constantly recalculated so they have it nested the lookups. 20. 5.2. VLOOKUP multiple criteria - Basic: in this video, we'll take another example of a non standard the look up, one in which you need to evaluate multiple criteria. Zero find Look up in reference that 702 we look up multiple criteria. In this example, we have some sales information, which is simply missing the cost of each item. So to find the cost, we have a purchase I D. And we can use that in the purchases table to pull back a price. But if we try to use that in the purchases table to pull back up price, we'll find that the purchase I d is not unique to each row. And if I sought by purchase, I d. We can see that for the first i d. They're six items that were purchased using that same purchase order number. So what we really need to do in order to uniquely identify a particular cost is to include , in our evaluation the product i d. Because on each purchase I d. We'll only have one road, the reach product that was bought. So if we use envy, look up. Ideally, what we need is a composite colon, one which combines but the products. I d. On the purchase i d. And this is one way of handling this particular situation. You simply create an extra column, which can Katyn eights these two items and you do that in both data sets. So I'd start with purchases and let's inside it as the first column, and then I'll just take the format from the right hand column. And then let's just call the column composite key. And then it's just a simple case. Or in Canton ation. So you can say equals C two ampersand the two. And then we can just copy this down and do the same thing. The sales. So this time, the product ideas G two ampersand purchase I D in D two. So without composite key in place, we can now use a standard. The look up equals the look up. We're looking up the composite key in A to coma and our table array is in. Purchases started in column A and in Colon Age, which is eight columns comma and its the eighth column that contains the unit price coma and zero for an exact match. So that gives us the unit cost to match the unit price. So let's just change the column heading to indicate their unit cost and then making copy on holding it down. So this is the basic solution to the situation where you need to do. If you look up, you have multiple criteria to be evaluated. Now it's not always feasible to develop. Beauty is done. There are several scenarios in which you don't have the ability to go in and create an extra Kahlan in the data set that you're looking up. So once we've got a bit more ammunition, Once we've discussed a few more look up and reference functions and a few more techniques, we'll revisit this example and look at a more sophisticated solution. 21. 5.3. Using MATCH & INDEX: the V look up function is very useful. However, it does have limitations. So this video will move on to look at a couple of functions, which provide a much more flexible alternative. When you're performing, look up operations 05 look up reference that's open up 03 using matched an index. So festival. Let's discuss the key limitation off the bill a cup function. In this example, we have to work seeds, hires and damaged items, items which will return from higher but were damaged. And we simply want to find out the name of the person who hired each of these items. So we have a higher ranch, and in our highest table, we have a higher ref column. However, the simple fact that the column saying in the name of the person who hired is on the left of the column, which contains the hiring, means that we can't use the look up now. A simple solution would be to cut the higher F column and paste it on the left of the higher A column that would solve the problem. But clearly this is not always gonna be feasible. There are going to be situations where you're simply not able to make that kind of change without perhaps messing up part of a solution. Accident. Have the authority is not your workbook. He don't have the authority to sort of cut and paste Collins, so sooner or later you'll find situations where we look up simply can't be used. And in these situations, the best solution is to use a combination of match and index In each of these two. Before was one part of the look up operation. So we started with a particular higher ref. And if we look at our hires data, the higher ed column is calling me. What the match function will do is to identify which row a particular higher F occurs in. Once you've identified that road, the index function could be used on any other column toe. Identify the entry in that column On that road, the index function will be the outer function, and the index function takes three arguments. First we have to you, Ray, which in this case will be calling a of the highest worksheet into the second argument. The road number. We used a match function that match function, in turn, takes three arguments. First of all, the look up value, which is the higher reference in Cell A to the damaged items. Work, please. Then we have to look up array, and this is column e of the highest work feet, the column that contains all the higher references. Finally, we have the match type, which is very similar to the final argument of the V look up option. And as with that argument, we can either use zero or falls to indicate that we want an exact match. So all of that supplied by the match function becomes the second Argument row number, and then index has 1/3 optional argument, which is the column number. In this example. We're working with a single column, said the depo column number, which is one is fine. For that reason, we can omit back there. Optional arguments. Let's write out formula. We start with equals index all tender since we're going to be nest in match inside it First argument is Thean Ray, within which we're looking and that's going to be hires. Call him a calmer all cantor, and then the second parameter is the road number, and this is where we need our match. The match starts with a look up value, which is back in damaged items, and it's eight to since damaged items is the work. See that contains the formula well, believe the name of the work street coma. Now the look up array is back in hires, and it's calling E, which is quite difficult to click on that you can just about manage it coma. And then we specify the match type as zero waffles, although enter on the clothes in parenthesis to finish index, and then we can help you on formula down. So I see you can see use an index in combination with Match gives you much more flexibility than using, be look up and then require in a look up table. You could use match on any column to identify a row number. Then, once you targeted that road, you can use index on any other column to pull back any value on that road. So they're very much partners in crime not last powerful on their own. But when used in combination, they provide a very flexible method of performing look up operations 22. 5.4. Advanced MATCH & INDEX: Let's now move on to look at a more advanced use of the match in index combination, the one in which you need to. Unless the match function twice inside the index function 05 Look up in reference. That's open up zero for that Burns Majin index. And in this example, we have an order for and then in the adjacent work seat, we've got the number of items in stock of each of our products at each of all branches. So on the order form, if we put in the quantity and then choose a particular product, I'm gonna go for this one y a l 50 80 on entry unit price of 75. We want tohave a formula in this stock column which, given the product code and also a branch. So let's choose London will automatically display the number of items in stock. So we've got an idea of whether we can actually supply the items being ordered. So we've seen how we can use the match function so identify which road contains a particular item. But in this example, additionally, we can use mass to identify which Kahlan we should be working in so We want the intersection off this road on this column, and that's what we need are formula to do for us. So we'll have a similar nest into the one that we saw in the previous video index will be out out of function, but this time we'll use all three arguments. The first argument is the ray The last time we had a single column array. This time we'll need a multi call in the rain, so we reference all of the columns in the stock worksheet. Then, for a second argument, the row number. We used a match function and has the look of value. We used the product reference that we've entered on the look up array will be column A of the stock table. That's the column that contains the references on the match type zero for an exact match, then on to the third argument of index the optional one. And that's the colon number. And in our arrangement, what we need to do is identifying the branch that we're actually working at, so we nest the match function a second time. This time to look at value is sell F 10 which contains the branch and for the look up array will then specify Row one of our stock work feet and again, we've got a match type of zero for an exact match. So now let's write out formula Well, just collapsed the ribbon to get a bit more space. So equals index all kinds of since we're gonna be nest in the match function inside it when the first argument is theory A that we're looking inside. This time we need to have a multi column array, so we go away from a across the end, so reference in all of those columns, comma or center lead to calculate the room number, we make our first use of the match function. So for the road, we're taking the product code from the 15. It's into a reference in a cell in the same worksheet is the one that contains the formula . We don't need the name of the worksheet comma, and then the look up array is simply the first column of the stock worksheets, then comma zero to specify that we want an exact match. So this back statement constitutes the second argument of the index function comma all 10 and then we use match a second time to supply the third optional argument. The column number. So this time the thing that we're looking for is the branch, which is in F 10 and again. We don't need to include the name of the worksheets. However, we do need dollar signs, because when we copy this formula down, we don't want F 10 to change. So I'll just press that four to convert this from a relative reference into an absolute reference comma. Now the look up array, Where we gonna find the brunch? Then I stopped. Work Seat will find the Browns on the first row and again when we copy before me that down . We don't want this reference to change so a lot to insert dollar signs and to finish the match. Com a zero for an exact match, and that completes our formula all tender and the closing parentheses to complete the index function and to complete the formula. So when we press enter, we'll just need to choose a branch back to London. We can see the quantity in stock in London, and of course, it would be a good idea to suppress the errors So, for example, we copy this down at the moment until we supply a product code. We will get this error so we can simply use the if era function to suppress that error. So enter, let's put out if error here. So this now becomes the first argument that if our comma or enter so here we need the replacement value, which is double quotes twice, or the null string on the closing parenthesis to finish off. If era now, we don't have a branch recently have nothing until we choose a branch. And then if we copy the formula down, we shouldn't now have any errors. But women choose a product. The quantity and stock is calculated for us. So that's the slightly more advanced use off the index function where the match function is nested twice wants to specify the road and wants to specify the Colin 23. 5.5. Optimizing Lookup Operations Part 1: in this video, we'll look at two techniques which you can use to optimize and speed up your look up operations. 05 Look up in reference. Let's open 05 optimizing. Look up operations. Now there are lots of minor changes that you can make, which will moderately speed up your look up operations. What we're gonna discuss in this video that the two techniques that you can use to make dramatic speed improvements in any look up operations that you're doing on the first technique will look at his use of the approximate match type as opposed to the exact match time. Let me begin by reminding you the difference. First of all, we've got a serious of job candidates and we're looking to complete these three columns based on information in the applications worksheets on the application work seat as an entry of road for every stage reached by each of the candidates. And then the stages work feats as details of what those stages are. So as you can see, there are six of them, starting with having an interest in C V all the way to being hired. So if in our applications work seeds we want to display not just the stage number that the stage description, we can use it be, look up or we could use a combination of index and match. Let's go ahead and do that. We'll call the new column stage description. And since the data were looking up is so insignificant, it doesn't matter whether we use we look up all index and match. But what we really interested in discussing is the use of approximate match. So let's use V look, huh? Equals we look up. The lookout value is the stage number, which isn't too coma. Look up. A raise in the state is worksheets and its columns A and B coma. The result were looking boys in column two of that look up table comma. And it's this final optional argument, which specifies whether we have an approximate match or an exact match. And the first technique, which has a big impact on the performance. The speed of your look up operations is that wherever possible, you should use approximate mash. Let's just insert true one is the alternative both mean the same thing, and then when we end to that, you can see that stage to correspondence to phone screen on we human copy that down. So what makes the approximate match type so much faster than the exact match die? Basically, with an exact match, Excel has to search every single road of the look up table. So, in this example, will again for F two, which contains one with the exact match. Excel goes to the first row of the specified range to look up range and compares it to to the value in that cell, in this case with the element numbers so text evaluates to zero does that to equal zero. No, it doesn't say it moves on to the next cell. Does that to equal one? Yes, it does. So Excel stops and then pulls back the value from Colin, too. So that's no problem. It's all in such a small table. But what if we had half a 1,000,000 entries? Excel would basically have to evaluate all of these rows until it finds something that's equal to what it's looking for. And it's this repeated testing for equality that makes the exact match so slow Excel is forced to perform what's called a linear search going from row to row testing for equality . By contrast, when you specify an approximate match time, Excel perform what's called a binary search. This is far more efficient. The way it works is that Excel goes to the middle value halfway down the date of range and compares that to the look of value. Listen, Ari, we have seven rows Excel goes to road for and compares F two to the value on road full. If that, too, is equal to that value job done, it's found what it's looking for. If it's greater than that value, then itself simply ignores all the roads a bubble and focuses on the roads below. In this case, F two is less than the road that we've evaluated. So excel this guns the bottom half and focuses on the top half. It goes to the middle row of what's remaining. So in this case, we've got three road meaning. It goes to the middle road and compares F two to the value in macro, which happens to be what it's looking for. So at this stage it would stop. So if we had half a 1,000,000 roasted process, you can see how using a binary search is gonna be so much more efficient because with each pass, the number of rows being process is hard and it doesn't take very long for Excel to start whittling down the number of rows, starting with half a 1,000,000 within 20 passes. We're down to one road, and this is what makes approximate match so dramatically faster than exact match. So when you work in with large data sets, you should always try and use the proximate match on. Obviously, you've got to make sure that the data is sorted on the look up Colin sorted in ascending order. However, there is one problem with using approximate match, and that is that it doesn't give you errors. So if I put a stage number that doesn't exist like nine, I really want this to say not available. But instead it simply gives me the highest value. It's where I have an invalid member in stage number. I'm simply going to get any erroneous results instead of getting an error, which I can then pick up on. So whenever you're using approximate match, you need to do a bit of error handling. So the solution is to use an if statement and then for the logical test, we use the look up set in the column in next to No. One and then converting the V look up into a logical test I put in equals. Whatever the look of value is, so we're looking for F two in column one. So, in other words, we're testing to see whether somewhere within that first column the value that we're using to pull back the state description does actually exist. As long as it does. Our family, true, becomes to be look up. But if it doesn't exist, our value of false becomes the function in a on the end, a function is a special era handle in function, which Excel has, which simply generates the n a error value. So let's update our be look up statement algo centre. Now Delta function now becomes if Ben Theological Test. We want Help me look up, John Copy using column one. And then we test whether it's equal to two comma. That's a logical test, all center and for value. True Wilder's paste in When I copied. And this, of course, is column to which is a column we're pulling back from the look up table, comma old enter and then the special function in a And, of course it's a function that takes no arguments. Its sole purpose in life is to generate the end. A caravan on the closing parenthesis, determinate the if so, let's into them. It was fine, but what we've now put in place it's a way of picking up values that don't exist. So if I put that mine back in, there is no nine, so it generates the end. A era value. That's how you use approximate match with the look up. And as I've said, the only provider is you've got to make sure we put some error handling to pick up illegal entries. Let's now move across the outcomes and look at the second major tech me or improving the speed of your look up operations. 24. 5.5. Optimizing Lookup Operations Part 2: Let's now move across the outcomes and look at the second major tech me or improving the speed of your look up operations. Everything we've discussed so far applies both to be look up and to the index and match combination. Both of them have the option to send the match type to truth. The second technique, however, applies specifically to the match and index combination, and you'll use this technique where you're use in one match operation to pull back several different pieces of information from your data set. And the trick, basically, is that you separate the two functions. So instead of using them repeatedly in combination, you use the match function once in a helper column and then all your index functions refer to that one match function. So it's not untypical that you'll be pulling back, say, 10 or 20 bits of information on the same I d. Number the same stuff I D. Number, same national insurance number, and you're just pulling back lots of columns using that one piece of information. So whenever that's the case, if you use this technique, you split in the two functions, so you've got matched only once and then, using Index, you'll see great improvements in speed. So let's have a look at how it works here. We've got our candidates summary. So in this work feet, each name will occur just once. And we want to move across to the applications. Work feeds by the well, just put that back. So one. And we want to move across to the applications worksheet and find out three bits of information. Firstly, how far did each candidate get? What stage off the possible six did they reach? Secondly, what was the first contact date? And thirdly, what was the last contact date on the key to get in a long This information is to sort the data using the candidate I d. On the stage date the men using the approximate match with the match function. So let's begin by sort in, they will need a custom of salt. First of all, by candidates, I d. That level. And secondly, by stage dates on the candidate ideas, you can see it's a number stored is text. So all sorted numerically. So now we have all the roads related to each candidate's sorted next to each other, and I think I must have accidentally placed a nine in here protesting purposes earlier. That should be a two soldiers Revert wanted to. So the first thing we want to do it's to pick up the last entry for each individual. So this person got as far as ST to own screen, and we scroll down to see if someone who got a bit further we've got to Sarah. Hansel, we've got as far as stage. Fine the reference check. So let me do I look up. We're interested in three things. The final stage, the first dates and the last eight. And for all of them, we're going to start by using the match function to match the candidates. So we perform in three operations, all using the same match. So this is an example where we can use this technique of split in the match in the index. So the match is only evaluated once, and then the index operations or we use the same match. So let's begin with the MASH. I'll insert a column which typically you would then hide once you got your formulas in place and let's just call it match row and of course we'll need the same error handling that we put in place for the B Look up. Let's do the basic function first, and then we'll put in the air handling afterwards. Everyone equals match. Look at values. The candidate I d in a to coma on the look up array is in applications, and it's calling a comma and then important and then importantly, the match type. True or false? One or zero, we want one. True, you'll also notice that we have minus one as an option, which we don't have would be. Look up, and this applies if it's more convenient for you to sort your data in descending order. And then if you have repetitions of the look of value, Excel will take the first entry rather than the last. So in our case, we want Excel to take the last. So you true one on when we enter? I've got this format. It is a dates on things. A format. It's a number on remained the decimals safer James Woody within the specified range. The last row is Road three, so no one road to run three. So we still have this potential problem that we can put erroneous values in here which aren't picked up his errors and simply give us wrong results. We'll need to add some era Handley, since the match function simply gives us of row rather than retrieving a value to do our test in, we'll need to use the index function. So again we've got an if statement and for a logical test, we use index. The array is the first column of the applications work treat, and then for row number we use match the same match function that we intend to use. So the look up valuing too Array Applications column A and match type one approximate. And then we convert that whole index match combination into a logical test by put in equals to a to So we're testing to see whether the value looking for does actually exist. If it does, value attributed statement becomes the mass function. And if it doesn't as we saw in the last example, we used the n A function to generate the A era body. So let's put that in place. Bolt, enter Hey, we need if Old Santa and we invest in index now a raise applications call him a comma in a row. Number is supplied by match The Chargers copy all hands, sir. Closing next and then make it into a logical test by saying equal to hey soon, comma. So all of that is the logical test from, if statement or cancer. True, is our manager coma on value? It false is the n a function, and that completes our formula, so that now makes it a bit more robust and ensures it's gonna pick up all those candidate ideas that don't actually exist. So now we can copy this more robust formula down. Are we ready to use the index function to pick up the three bits of information that we need? So first of all, we want the outcome, which is in column G because we've got our data sorted and we'll be using approximate mash for each tender that i d. It goes through all the repetitions and always picks up the last one so we can collapse the formula bar because we're now gonna do a single function with no nesting equals index. The array is Colin G. Coma And then I wrote Number is supplied by the match function, which we've got in C two. And because our formulas and outcomes we don't need to specify outcomes as the lane of the work it that's our outcome on it. We coming down and just tested. Remember Sarah Hutzell got us faras reference check. Let's leave first day till last. That's the most complex Let's now do last state. So last state is in column h. We want exactly the same formula, but we want to use H instead of G. Everything else is the same. The First States is slightly more tricky. We can start by using what we've got here for the last eight. So that takes us here and then we need to move up. But how many rows do we need to live up? And the answer is basically we can use the stage number. So we've move up by this number of rows. So in this case, too, we went on the cell above the first road. So we then just need to down one. Let's try that with Sarah Hustles Road five stages. Move up. 512345 and we're on the cell above her first road. We then lived down one, So let's build out logic into our formula. We'll start with this one, but we're now gonna be nest in an index inside an index. So all kinds of and I'll just lose the closing parenthesis for a moment. We now want to subtract from sea to all hands up index on our array is column F. That's where the stage number is. Coma on our row number is the value supplied by sea to and the closing parenthesis for index. And then we have to remember to add on one so that we moved down one road to put us on the first row. That matches that candidate I be and Walt enter on the closing around consists for the index function. Let's test it that we coming down. So for James Woody, we have a society faster The AIDS um em for Maduro. Yeah, the 29th of this six. Let's just do one more So Hansel 30 that the eighth. No, she is the But those are the two key techniques to take into account. Firstly, when used in either natural, look up. Always said your match time to approximate by using one. What true? And secondly, if you're retrieve in several pieces of information using the same match. Always use the index and match combination. But the match in one Kahlan and then for all the other columns, you can simply use index on its own and refer back to that one match, Colin. So those are the two things to bear in mind if you want to make your lookups as fast as possible. 25. 5.6. OFFSET Function: in this video, we'll take a look of the use of excels offset function, which could be used to isolate a range of data within a larger range. The 05 look up reference Let's open up 06 offset function. So here we have to work sheets. First of all, we have call centre averages has information every day of the week. But those are the seven days of week one, and we've just got some summaries, average time, things of answer, call, duration, etcetera. And in the summary worksheet, what we're looking to do is to get the weekly averages. So we want to take each of those seven rows and just get the average figure. But in each of the columns, so naturally, for this to work, we have to have the data sorted chronologically. So we've got all the Week one information or the week to information, etcetera. But in this case, I think you'll agree that is the logical way to lay out this information. So it's logical that it would be sorted in this fashion. The way that the offset function works is that you specify a reference cell, which is the starting point So in the case of column see the time taken to answer a logical reference. Elwood BC to the first cell in week one. Then we specify the robe offset and column offset How many rows down? How many Collins across Solwezi? In this case, we don't want any columns across, and the number of rows down depends on the week saying We won. We want an offset a zero rose because we're in the right cell in Week two. We want an offset of seven. So it's starting from here. 1234567 And that takes us to Monday of the following week. So the relationship between the week number on the offset is that we need to subtract one from the week number and then multiply the result by seven saying we won, we subtract one from one, leaving zero multiplied by seven gives us zero, so we've got no upset. Then we to tu minus one, gives us 11 Time. Seven gives this seven. That gives us an offset of seven starting from the initial reference point so that initial reference point C two will be fixed, or at least a two will be fixed with a dollar sign. So we're always started from that point. And then as the weeks progress, the road offset just continually increases. So this is the formula we need to create. The out of function is average, and this can take a series of arguments separated by commas. Refer to his number one number two etcetera. Each of these is normally a cell reference molders need one block of cells which will be retrieved by the offset function. The offset function takes five arguments, only the 1st 3 or obligatory. Firstly, we have the reference. So for the first piece of information, we're looking up. That's gonna be cell C two and we'll have to remember to put a dollar in front of the two sides. We copy the formula down, we stay stuck on that road. Then we've got the two offsets. How maney of Rose he wants, well, sat down. How many columns do you want to offset across? So for the rose, we use the week number, subtract one from it, then multiplied by seven and the columns We haven't all set of zero because we're in the right column. And then because We're doing an average for the week. The height that we want to retreat is seven. So the offset takes us to Monday. A man from there. We have a height of seven, which encompasses the entire week because we want the with to be one on because one is the default. We can venom it. The fifth argument, which is the with that we've got a matrix, which is seven rows high on one Colin wide. So now let's write out formula. So the outer function is average old Santa and then inside it me. Next hour, offset reference is call centre averages C two and then I'll just press that four until I got a dollar sign in front of the two but no dollar sign in front of the sea and then for the number of rows that we want to offset in parentheses. Well, first of all, pick up the week and I'll just deletes the name of the worksheets since the Formula Asian summary. And then we just press that for until we've got a dollar sign in front of the A that no dollar sign in front of the two. That way, when we call me to the right reference will remain stuck on the week color, but when we call you down, we'll pick up each of the different leaks from that figure. We need to subtract one before multiplying it by seven. That's how many rows down we want to offset. Now we need to specify how many columns we want to offset. We're in the right column so we don't need or not said it's all. But we have to put zero because Collins is not an optional argument comma that we specify the height we want the average of seven days. So we put a height of seven. And then for our wits, we can either put comma one or because, as you can tell by this, where brackets it's optional, we can simply admit the with, and then we'll have a default of one. So the upset function is returning. The matrix of seven rose by one column. That's our formula. So all times on the closing practices to him, the average function. So because of our strategic use of dollar signs, we can now call me to the right, pick up the average reach of you, Jason Columns and then we could just double click. So you copy down and pick up the averages for each week. So that's how the offset function works very useful function or isolated and analyze in small sections of data within a larger data set. 26. 5.7. Using the INDIRECT function: that's our move on to look at excels in direct function. 05 Let's open up 07 The indirect function enables you to construct a cell reference by using concatenation. So in this example, we have a series of worksheets. We want to analyze the data in each of those work treats. We want to come up with a total amount claim total amount reimbursed on the average percentage reimbursed. So obviously, when we come to analyze the Birmingham work, Treat will be using the word Birmingham. When we come to analyze, cardio will be using the word Cardiff and so four on what the indirect function enables you to do is to incorporate that word into your cell reference. And a good way to use indirect is to start with a literal reference and then incorporate indirect within a literal reference. So for Birmingham, we would say equal some. I live across the Birmingham and highlight this cell reference that we want to analyze. So for amount claim that's column C and then we enter the formula, and what indirect now enables us to do is to substitute the word Birmingham with a reference to sell a to that way, when we copy the formula down will have a reference to sell a three a four, which will effectively enable us to target a different work feet. So let's just expand the formula bar because we're nest in indirect inside. Some are used old enter. Yeah, we'll need indirect come in here, we'll need old enter. And that will be the clothing parenthesis all the sun. So the argument that indirect taste, the obligatory argument is referenced text and the key word here is text. So in other words, what goes inside the parentheses is a string. If I were simply to put what we have already as a string and close parentheses, we'll get the same results. But because it's a string, what we can now do, it's the use can cast a nation so we can delete the word Birmingham and proceed it with Cell 82. So if I put in reference to eat too, and then use the ampersand African cattle nation, I'm now saying, Take the word in cell a to and then can Katyn ate the rest of this string to produce the spinal cell reference and when I enter, get exactly the same result, but I now have a flexibility, a copy in this formula down and then the total reimbursed will be exactly the same as this formula. But with colon be instead, that's just verify that so that we could see amount playing see amount. We invest the so if instantly copy the original press escape and then paste it in and changer C's two D's was coming that one down, and then finally, or the amount reimbursed. We analyze column E, but we use the average function again. I'll just paste in change something average on chain C T E. On. I've already formatted this column as a percentage. So that's the basic usage off the indirect function. It provides a great deal of power, a great deal of flexibility. We'll get some further practice on using it in the next video. 27. 5.8. VLOOKUP multiple criteria - Advanced: Let's end this section on. Look up in reference formulas I revisit in the topic of multi criteria. Look up! That's open up the final word book zero late. Let me first of all remind you off the issue that we had. We want to find out the cost, the beat of the items that were sold. But in order to do so when we look up data in the purchases table, we need to identify not only the purchase I t, but also the product i d. And just to remind you the solution that me used early. Yeah, we simply created a competent key in both of the worksheets. And then we were able to use this using a simple the look up statement. So we'll now give ourselves more challenge and say that we've got a situation in which it simply isn't practical to create a composite key in both worksheets. One such scenario might be the one in which the purchases worksheet is constantly updated. So as soon as you create your composite key, a new version of the worksheet comes along, which doesn't have the composite key. So the solution on proposing is fairly complicated So what we'll do is to take it one step at a time. And to make things clearer will create some temporary helper columns, which we can then either delete or hide once we've got the final formula. So let's begin by inserting three columns for the left of the cost column on the approach that we're going to take is to exploit the fact that the data is sorted by purchase i d. On. What we'll do is to try and identify but first and last row for each of the purchase I ds. Once we've identified that we can then to look up only within those rose in the product i d column. And since there will be no repetitions of product, I D. Within that small range we can therefore identity by which of these rows contains the Majin products I D. And that will be the one from which we take back the unit cost price. So are three help of columns will be top row bottom row and then the product I d wrote to find the top and bottom row will use the match function and then we'll exploit the fact that the match function has a match type argument, which, when dealing with multiple results, will either find the first along the last. So to find the top row, the use equals match and, of course, were looking in purchase I D. C. To because this is the one that has the repetitions comma. And then I'll look up array in purchases. Will be column see the purchase i d column and just remind you that this will only work if the data is sorted by that column in ascending order comma. And then we simply use exact match because when we have repetitions, use an exact match will always target the first of those repetitions. And is this implies this solution is not going to be highly optimized solution. You're using it with lots of data. It may actually be quite slow. That's our function. And then the formula for the bottom row is identical, apart from the fact that the final argument becomes one. So we've got an approximate match, which will then pick up the last of our repeated rose so you can see this is going from 3492357 Misters, check that it's accurate So we've got a purchase idea of 269711 on the road 349 And there's the last of our rose. 357 The fact that match enables us to isolate the matrix that applies to each purchase I d buy. Tell anus the top and bottom row numbers. It means that we can now use our new best friend from the last video the indirect function to target the adjacent colon Be and do look up the product i d. But only within those rose. So we'll know there'll be no repetition of the product i d. Within the Rose applying to a particular purchase i d. So when we specify the look up range, we simply can. Katyn ate the match functions which will calculate the top and bottom rose because we know that regardless of the role numbers, it will always be column B. So we're doing another match, but this time will messed the indirect function inside it equals match old enter and I look out Value is the product i d comma all counter. So to supply the look up array we use indirect and then we need to assemble a string using concatenation, and we know we'll be working in the purchases work Street exclamation mark and we know we'll be working in column B. Then we can. Captain ate the top row, which we've calculated in H two and percent, and we need Colon Be and we can. Katyn ate the bottom road that completes our string. That completes indirect, which just to remind you is the second look up array argument of match so common to end that argument, Altanta and then the match type would use one because it's faster Walt enter and the closing parenthesis. So within that small matrix product, I D 43 occurs on the seventh row. So it's not the seventh row. The worksheet. It's 1/7 row of the Matrix, which contains that particular party, Saidi. That's copy all about four minutes down and then let's assemble them into one main formula . So equals index old enter. We start by specifying the array, which will be column G using the same technique that we used here. So in direct quotes, name of work feet, exclamation mark and then call it G and percent tank on the top row Empress end back into quotes. Colon G and percent. The bottom right? So that's our indirect. So that's the Matrix within which we're looking Walt enter. And then to specify the road within that matrix, we use the formula that we have in the product I The row column, Altanta and the clothes in parenthesis. There we have our cost. So let's just test that it's working. So this is product idea 43 and it's in Row three for nine plus seven. There's our Matrix and there's product 43 on. We can see that we have a price of 42.7 So to finish off, we can dispense without help of columns. Let's do everything in turn. First of all, we need the code for H two. So old enter on hte to goes here. Then I two let's do another old enter on replace I two with its formula and then finally J two. Let's align the indentation and there we have it. We now have a choice of either deleting these three help the columns What? We could just hide them and to finish we can just copy the formula down 28. 6.1. Controlling rounding sensitivity: in this section will take some examples of working with numeric values. Let's begin by looking at rounded 06 manipulated numbers. Let's open 01 Controlling round insensitivity Excel has a number of functions which will perform round in operations on the American bodies, but these are not to be confused with all matin. So, for example, here I've got a recommended retail price. Colin, if I highlight it and use the increase or decrease decimal buttons, I'm in no way changing the number. So simply by changing the corner so that the decimals aren't displayed doesn't imply that the number has changed. When I click on the cells, we'll see that any decibels that were there are still there. By contrast, when we use around in function, we're actually changing the number in some way. So the most basic round in functions will change the number of decimals, which displayed perhaps the most useful round in functions of those which round the number in more sophisticated ways. These are the functions will look at in this video and round, ceiling and floor. All three of these have the ability to around the number to whatever significant digits you desire in this example. Well, look at calculating discounted prices, but since they on prices, we want them to be fairly user friendly and easy to remember. We've got all out discounts set up in columns. I threw a hammer, and here we have a couple of drop downs, but which the user can choose the significance or sensitivity. So, for example, if they choose five, we'll be round in the number to the nearest five to the nearest 2.5, right down to decimals, to the nearest nor going to five, and then the user can specify the direction. Do you want to do normal rounding up with no 0.5 and above, down for anything below, nor point fine? Or do we always, once around, up all always round down? So let's begin by putting our basic formula in place. One which calculates the prices based on these discounts so we would want equals recommended retail in the three minus because with this county the recommended retail multiplied by the discount so that will take care of this count. A Now who wants to copy this formula? We'll have to think about dollar signs. First of all the to be threes. When we copy to the right, we won't want the beater change, but when we copy down, we will want the three to change. So we press at all. And so we've got a dollar in front of the bees, but no dollar in front of the three. And then for I three, we have the reverse when we copy. So the right we do want the I to change to a J. K etcetera. So it picks up all the other discounts. But when we copy down, we go with the three to change. So this time we use that for to get toe I dollar through. That's our basic formula. Now let's make out solution a bit more flexible By taking the significance on the direction into account. Let's start by name in these two cells also alone, this one to the right. I think he looks a lot better that way. And then to name the cells, we could just highlight this matrix, which has potential names on the left, have an automatically generated the names by using formulas create from selection and left colon, so the name significance will apply to J by on the name direction well, applying to J six and then our created names available here with my choose direction, even see Stay six and significance is j fine. I believe the next statement. Test the direction that's chosen and use the appropriate function based on the current selection. So the outer function will be if and then a logical test. Well, we weather direction equals around and then for value is true. We would use the M around function, which is normal. Rounding the number that we want around is the formula that we have so far that simple calculation. And then we specify in the case of em around the second row mentor call multiple. And this will basically be our significance. And that's a number between North 0.25 and five. That's what we do in a logical test proves to be true. And when the logical test is false, value it falls is a nested if statement. A logical test here is for the second puzzle of their action direction. People's ground up. If it does, value true is to use instead of the M around function, the ceiling function, which always rounds up and like em around seeing it has two arguments. The number two which it applies. It is our calculation, and then the second perimeter. Instead of the import multiple. It's called significance, and that will be this cell that we've called signage because which has to drop down with the different numerical values between 4.5 and fine, having tested but two possible directions. It neither of those is true catchall value, if all still kicking. And in that scenario, we know that we must be around it down. So we'll be using law and then the first parameter, which should say number like so present calculation and then ask before the significance is the choice that's currently in play in the cell that we've made its significance. So now let's create our final formula. Let's expand the formula bar. Let's also collapsed the ribbon to get ourselves a bit more room, so we'll need this So this press old into a couple of times and leave it in place. But to start the final formula, we need if all hands a neurological test and we test in the direction, so you'll notice that when I click on J six. Because it has a name, the name is automatically picked up. Direction equals ground. That sound test, all hands are and the value true used the M around function, and then the number that we around in is the calculation that we did earlier. Coma. And then the second argument, which Excel holds more people will be the cell that we've called significance. So when I click on it, the name of the sound is automatically inserted. Close friend persist in the M round and comma to end the second on Vince the value true or our if statement, and to supply the third argument on its walls, we put in a nest it if or enter at this time, we're testing to see if the direction equals round up calmer. I meant to supply the value of true arguments of this nest. It is. You would use ceiling. We can ignore sealing off man for two reasons. First reason. It's only available in Excel 2013 and 2060. So not everyone doing this tools for actually have it available, and secondly, the promise of it it offers which ceiling doesn't. Pam is to specify the direction or around in negative numbers. So since we're not dealing with negative numbers, we can ignore seeing that man, you just insert ceiling. And then, of course, the number that we're analyzing is the one supplied by our initial calculation. Common and then significance is this cell that called significance. Let's just call Colmer and then for the value of false not just of the nested. If, but they're both ISS will become floor and then we just paste in rest off the function. So it has to see the user has specified that we want around all round up. If they want around, we use them around if they want to round up when you see them. Neither of those two is true when you small old enter closing parenthesis for the nested it on the closing parenthesis for the outer. And when I pasted, I managed to end up with two open parentheses. There sort of lose that one. That's our basic formula, which because of our strategic use of dollar signs, we can come across on coming down. So with the significant sets of five, you can see that all our prices are multiples of five and Then, as we re buying this significance, we get more granular prices all the way down to 4.25 where we had personal accuracy to our prices. And if you want the user to see the consequence of the choice of around in this chosen, you can just insert a formula here to calculate the average vehicles, average all these cells on. Then we can copy that across and then to see the most dramatic difference, we can set the significance toe fine. If it is candidate, we have a price of about 48. If we round up, that becomes 50. I think we round down. That becomes fully fine. So as you can see, rounding in Excel is not just a case off the decimals. It can apply to the significant digits as well, using one of these three very useful functions em around ceiling or law 29. 6.2. Using SUMIF and INDIRECT: let's now move on to look at one of excels power functions, this Summit function. To make things more interested, we'll use it in combination within the ranked 706 manipulating numbers. Let's open zero to So here we have a serious of worksheets, which will have an identical torment. And in our summary worksheet from sales, we want to calculate all the different categories of expenditure across Bulla branches. So what we need is conditional. So nation, when we examine the data you need of our branch work feeds, we're interested in Kahlan D, the amount reimbursed. But we want a breakdown of the amount reimbursed category. How much did we spend on travel, public transport and so forth? And this is exactly what some if enables us to do and then to complete our model. We have the names of all our branches, which correspond to the worksheet that contains the figures for each of the branches. And that's where indirect will come in when you intend to use the indirect function in a formula. It's usually a good idea to start by creating a literal reference and then including the indirect function afterwards. So let's expand our formula bar so equals some if all cancer, since we'll be nest in the indirect funding inside it. But for the moment, let's ignore indirect. Let's create a literal reference using the first of our work feats. Birmingham. So we're supplying the first argument of the summit function, and that's the criteria range the range in which the summit function will look for whatever criteria you specified. And that's going to be the category column calling F. And since we intend to copy this formula to the right well, now press the F four key to convert the reference into an absolute reference. Coma and old enter. Now we specify the criteria. So back to the bronze sales work feet and the criteria is in row one. So we click on B one now. Then we've got to think about dollar signs. If we copy across, we want the be to change so we can pick up all the different categories. But if we can't beat down, we have one with one to change. We want to stay on row one, so we just press that for and so we've got B dollar one coma or enter, and then the final argument is optional. It's the some range. This is the range of cells that you want to total. If it submitted, the function will attempt to some the Chrissie rearrange. In our case, the some range is different. How some range is calling d the amount we invest. And again, we need to press that fool to fix the range old enter and then the closes for emphasis to finish our literal formula. So to finish our formula, we used the indirect function to compose both of ourselves. References. So, first of all, the criteria range. Instead of having a literal reference to the Birmingham worksheets using indirect, we can now say Take the contents of Cell A to which is Birmingham and then can Katyn eight . An exclamation mark, which excelled requires for a worksheet reference, followed by the criteria column column F, which contains all their expense categories. Then we got on criteria. No changes there, but for the some range, we again using the ranks. We take the worksheet name from a to, and this time after the exclamation mark, we tag on a reference to Colin D. And that's the amount reimbursed collar. So for our criteria range. We could delete the word Birmingham and convert the remainder of the reference into a string. And then we can use indirect click on A to press that full until we've got a dollar sign in front of the A. But not in front of that, too, and percent the remainder of this string and the clothes in parenthesis. And then we could just copy the stomachs of that and paste it to compose the some range open quotation mark closing quotation marks close in parenthesis. So if you use an indirect, that's what I'd recommend. Always start with a literal string as an example, and then look to see where you can use indirect to introduce flexibility into your model. So when we enter, we should get the same results. But what we can now do is to copy both across and down. Let's just check one of our figures, so I'm gonna check comedy travel 1159.2. Let's just going to filter on, actually travel and then highlight the amount reinvest. And if you look on the right hand side of the status bone, you'll see we've got out 1159.20 So that's the summit function where you're using a single criteria. In the next video, we'll move on to look at summits, which allows you to specify multiple criteria. 30. 6.3. Using wildcards with SUMIFS, AVERAGEIFS, MINIFS and MAXIFS: in this video, we'll move on to look at the summit's function, which differs from the sun. If function that we saw in the last video in that you can specify multiple criteria and at the same time we'll discuss the use of wildcard matches. 06 manipulated numbers that's open up 03 using wild cards with some apes. Let's first of all, have a look at the model that we're building and I'll item sales worksheet. We have a record of all the items that were soul, the account handler, the product and the total price on what we're interested in doing in our summary worksheets is focusing on the performance of our account handlers and then get in there total sales average transaction, minimum transaction, maximum transaction. But at the same time, we want to offer the user the possibility, focusing on a particular product range so they can either specify figures for all ranges or for a particular range. And finally, to get ourselves that challenge how item sales figures have no product range. Colin. They know that's a match. The product we need to focus on the 1st 2 letters off the product reference because The 1st 2 letters of the product reference are the same as the 1st 2 letters of the name of the product range, and this will give us a chance to see how wildcard matches have done when you're working with any of excels, conditional number crunching functions such as some if averages etcetera. To add clarity to the formulas that will build, it's also used main ranges. Hopefully, you're familiar with named Rangers. Let's go across the item sales, and we're really interested in account, handler product and total price. But in the interests of completeness, let's name all of the columns, and we've got the choice of either attaching the name to the entire column or simply to the cells that are filled with data. Since these figures are historical, let's use the latter technique and just focus on the cells that have data. So to do that, we click in cell, a one press control ship right arrow, followed by control ship Down Arrow that's elects all the data and then to create the named ranges. We can go to formulas and then the defined names group click on Create from Selection, and we then just tell excel to create the names using the column headings in the first room . So we leave Taco selected Okay and Excel automatically creates the names for us. We can now use these names in our formulas rather than using the raw column references. Let's do one final name. Let's name the cell a one he created. Name manually. We click in the Name box, which is just here and type of name, which, of course, can't have any spaces. So all underscore ranges and always remember to press enter. Otherwise the name will register. The summit's function takes three obligatory arguments on up to 126 optional arguments. The first parameter is this some range? This is the range of cells from which you want to create your conditional total. In our case is the range of cells that we've now called Total price, the final colon, and then the second argument is your first criteria range. And that's gonna be the column containing the names of the account handlers. And then finally, we have the criteria that will look in for within that range. That's gonna be the contents of cell A full, which, if you remember is Brandon Jackson, and then you complete the summits function. I, especially if I ng pairs Chrissie arrange criteria, and you could do that up to 126 more times. In our case, of course, we only need one additional pair, and this time we're trying to match the product. So criteria range to will be the column that is now called product and for criteria to will need to use an if statement to test the value in the cell that we now call range the one that contains a drop down, enabling the user to choose a product range. Let's begin by building the basic formula use in just the 1st 3 obligatory arguments. Let's begin by expanding the formula so any equal summits or enter and we start with the some range. This, of course, is in item sales. The final column column J. Instead of using the rule reference because we've assigned the name to those cells, we can use the name in our formula. One way of doing that is in the formulas tab in the defined names group News in formula and here we've got total price comma old, enter And now we're into the first of our criteria range stroke criteria, pairs. So the criteria range, First of all, is gonna be account handler and again, we haven't named range. A second way of entering the name range is simply to type it. So if I type a cc Excel obligingly displays the name I command, just press the tab key to insert it into my formula, comma old enter and then to complete the pair, we specify the criteria that we want to match within the criteria range. On this, of course, is the name of the account Ambler, which is in cell able as always because my formula is in the summary work Street. I'm going to delete the name of the worksheet so formula is not complete. But let's leave it at that for the moment, Ault enter closing parenthesis. So we left it like that. First of all, we wouldn't have needed the s we could have disused summit on. Secondly, whatever choice the user makes up here is going to be ignored. So now let's think about the second criteria pairing that we need to insert into our formula. The pair always begins with the criteria range. That, of course, is going to be the product column when we haven't named Range called Product to specify this. But then, when we come to specify the criteria that were matching within this range, we'll need to test the value that the user has chosen in cell a one which we've named product range. And the first thing we need to distinguish between is whether they've chosen all ranges or whether they've chosen a particular product range. We can do that with an if statement. We can say, if our product ranges, cell equals all Rangers do one thing, otherwise do another. So what do we want to do if they choose all ranges? Basically, we want to say match every single row within product that miss any rose out. So the only criteria that's being applied, the only limitation is that we're matching a particular account handler. But we want no additional filter in to occur on product on weaken. Do this by using the wildcard character, which is asterisk so to complete our formula. We specify criteria range, too, which will be the product column and then for the matching cry interior we use an excitement biological test will be whether the cell, called range contains the selection ball. If it does, value true will be an asterisk and if it doesn't value of false, will be the first to characters followed by an asteroid. So to extract, the 1st 2 characters will need the left function. The text will be the cell called Range a one, and then the number of characters will be to then will refuse 1% asterisks to tag on the wildcard character. Let's now complete our formula. So now I'm gonna add on to more arguments, comma or cancer. And in our parents, the criteria range always comes first. On the range we're evaluating is the product column. Since it has a name, we can use that name cycle to start to type it. And when it appears in the list inserted by pressing tab old enter and now we come to specify the criteria that will use within the product. Colin. So here were interested in examining this cell and testing to see whether the user has chosen away ranges, so he will need an if statement old and how logical test is whether a one that is called product range, contains the words or ranges so we can insert product ranges. I've just noticed that I've called it all ranges rather than product ranges. So when we finished the formula, we'll rename that car. That range would be a better name than war rangers. So it all ranges equals quotes, all space ranges, which is the entry that's actually gonna be displayed in the cell comma. All came so and value it true will be our wildcard character. But of course, goes in quotes. So quotes, asterisk, close quotes, comma all Danto and then on value it false. It's going to be the first to characters of the product ranges cell full of by the asterisk . So he used a left function to extract the 1st 2 characters. The text will be all ranges comma. Number of characters will be, too the closing premises to end the left function and then I m percent to tag on the wildcard character and that completes our if statement alone enter and then the clothes in parenthesis. All the if statement that completes our formula before we test it. Let's change the name all ranges to product range, which is a much better name saying formulas. We go to name manager and we can just add it all ranges and cool. It's on the school range. And the great thing is, of course, that Excel automatically ate the name fours, so you can see we've now the product range instead of all ranges. So let's just collapse on Formula bar on. Let's test it so we can now choose a particular range, and we now see in the total figures for that range. Brandon Jackson Brain stuff, 52,711 0.8 unless didn't filter to test but brain star, this time beyond the search criteria se I like all visible cells and then in the bottom right, We can see that we've got the correct total. So let's complete our model and, of course, average minimum maximum, working exactly the same way that we can simply copy the original formula pasted into each of these cells and change the name of the function on that, we can copy all of these formers down 31. 6.4. Using the SUBTOTAL function: Let's now move on to look at a very simple but very useful function. Sub Total. 06 Manipulating numbers. Let's open zero fall sub total gumption. The sub total function is used automatically by Microsoft. Excel whenever you use the sum total command. However, Additionally, you can use the sub total function in your own formulas and solutions, and thats we'll see it provides a couple of very unique attributes, which you can take advantage of. Let's begin by reviewing the sub total command, so we'll switch across to the sub total command work feet and here we have some sales data . The subtitle Command is basically a poor man's pivot table. It allows you to group data and to insert subtitles either before each group or after each group. So, for example, if we wanted to show stop totals by sales person, our first step would be to sort the data by the salesperson column. I mean, it's just a case of going across to the data time and on the right hand side here, we've got sub total, so each changing sales person we use the some function on the sales. Kahlan. When we click OK, excel automatically inserts extra rose after each group, and it uses the sock total function to generate the subtitles. We'll discuss the syntax of the sub total function shortly, but for the moment, I just like to point out to very useful and unique attributes possessed by the subtitle function. The first is that the sub total function ignores any cells that contain sub totals. So, for example, at the bottom of our data, Excel has automatically generated a grand total road, and it's using the sub total function to calculate the grand total along the data. In doing so, it creates a reference to the entire column, starting from road to the first data road and ending on 1000 and 84 the last state of Rhode between those two cells. There are lots of cells that contain sub total formulas, but these are automatically ignored, and that wouldn't be the case if we were using this some function, for example, the total is generated by all these analysis cells would be included in the calculation if we use the sun function instead of the sub Total function on the second key attributes is that when you filter data the subtitle will analyze only the currently visible cells. So, for example, if I activate Filter In and let's say I only want to see sales greater than or equal to 75,000 So if I make a selection, we can see that the total in the status bar matches the total generated by the sub total function, and this is the attributes that will be taken advantage off in our solution. So let's clear our filter and let's remove our subtitles by using the subtitle Command Again and clicking on removed or on before we create our formulas. Let's have a look at the syntax off the subtitle function. The subtitle function is basically a wrapper function, which allows you to perform a variety of different calculations, depending on the first argument. But function argument is used to specify whether you want sub total to give you the average count. Count a max etcetera, and the argument is numeric. You'll notice that each of these possible values occurs twice. This allows you to specify how you want the sub total function toe handle manually. Hidden rose if you choose a single digit valley, manually hitting rose are included in the final calculation. If you choose one of those three digit equivalence, manually hitting Rose are excluded, and it's important to remember that we are talking about manually hidden rose. Only any rooms which are hidden as a result of film three I never included on the second arguments of the subtitle function is the reference that you want to apply this function, too. So, in our case will be working on the sales. Told him. Ask the name of the arguments implies Breath one. You can either have a single reference or a series of comma separated references. So in our case, we just want one column, which is the sales column. So now let's create our formulas. It's a very simple formula with no nesting, so we don't need to expand our formula bar. You just need equals sub total, and then excel very obligingly displays all of the available functions on For our purposes . We can either use nine. A one on nine is the user will be filtering data rather than manually high in rows. And of course, both nine and 109 will only analyze visible cells when filtering is used as opposed to manually hiding Rose. Someone used nine coma. And then the only reference that we need to specify is rep one, which will be the sales column. So I'll reference the entire column by clicking on the column heading If I could just about reach and that's our formula. So we enter, and we can then copy this formula to save a bit of time when we created the others. So we simply need to paste each one in and replace the number. And to get Excel to duel the work. We just reinsert the opening parenthesis to bring on the list. So everyone number one everyone number full. And here we want under five. So using the simple formulas yuk unbilled solutions, which are very similar to the ones that you build using functions like some if averages. But the difference is that you have far more flexibility and interactivity. You're leaving it to the user to decide how they want to filter the data, and then your formulas give them a result for the currently visible cells. So let's test that it's working if we filter the bronze Colin. So we're only viewing the figures for the Cardiff branch. And then let's reduce the quarter down to court a one only if we now select all of the sales figures by clicking on the first and press in control ship Down Arrow, we can see from the figures displayed in the status bar without total. On average, figures have been updated to reflect only the currently visible rose. 32. 6.5. The RANK function variations: Let's now move on to look at the two functions used to rank data in Excel ranked body Q and drank, not TVG 06 Manipulating numbers that's open zero. Find the rank function variations. One simple way of Rankin data is to sort the data in descending order. But what the rank functions enable you to do is to display the rank permanently, regardless of the sort order. In this example, we're analyzing how good each of our account handlers is. It close in sales. We want to rank them by what this company calls the clothes factor. So let's begin by calculating the clothes factor. And it's simply the number of hours taken to close sales divided by the revenue. And because this will be a fairly small number, let's have five significant digits. So to do that, we'll use the round. Function around function allows you to specify the number of decimals to which in number is rounded. It takes two arguments. Festival the number which in this case will be see two divided by G two and secondly, the number of digits. And let's say, because this number is very small, we'll have five decimal places says a calculator on clothes factor. It's going to be equals round see to divided by G two coma number of digits. Fine, and I've already formatted this column to display by personal places. On That, of course, is done. Using these two buttons increased decimal on decrease decibel, so we can now copy this down. We now have a thing that we want to rank before we create our final two formulas. Let's create some named ranges, which we can then refer to in our formulas. We'll highlight the three columns that we're interested in using control. Click and then in formulas. I mean years in the defined names group we use create from selection talk right and these around you names. So now let's discuss the rank function. The rank function takes three arguments. First of all, you specify the number that you want to rang, which in our case will be G two because I'm formula is on road to will be referenced in the second row of the Revenue column. The second perimeter reference allows you to specify the range in which your position in the number than use pacified. And that's the column that we've named revenue. The sad argument is the order will the highest item the standing. The third argument is the orders. The third argument is optional, and that's the order. This allows you to specify whether you want to treat The items is being sorted in descending order, which is the default on which is easy in descending order, which is the default what is indicated by zero or ascend in order. Lowest first. That's indicated by one you know, scenario. We want one because the lower in our scenario we need one ascending order because the lower the closed time, the better because the lower the close time, the better has moved across to the other worksheet we have in this workbook e que verses A B G. And let's look at the two flavors off the rank function. I'm gonna put some figures in the first column, which is what we'll be ranking and let's just copy those down so clearly 10 is the highest figure it will have a rank of one. One is the lowest. It will have a rank of 10 equals rank que. The number is a two common on the reference is that whole columns from A to bounce 11 and then I'll press at Ford to make that into an absolute reference. We get the anticipated results, and then we can just copy this formula and paste it for the A V G version and we get exactly the same formula. So the difference between these two comes into play when we have ties. So if we put in two sevens, for example, we can now see the difference with rank e que. We now have two items. Time for fourth place with rank A V G. We have an average figure, so both of the force place items were indicated is 4.5 rather than fall. And this difference really comes into play when you're performing operations on the rank Colin itself. So we put a total for these two columns. You'll see that with Frankie Que. Whenever you have ties, the total number within the Rank column will decrease by one. Where's with rain? Cave E G. That total number will never change. So if you perform in operations on the Rank column, it's usually better to use rank a VG, and that's what we'll need. In this particular example, have been calculated the close rank. We intend to go on to calculate the clothes status, which will make fast, medium or slow. And although they can't be very much in it, we should get slightly more accurate results that we use ranked. What a BCG, as opposed to run buddy here so close rank will simply be equals ranked on a beauty and our number parameter will be the clothes factor indeed to comma. And then from reference, we can use the named range close factor. Coleman and then for our purposes will need ascending or one because the lower the clothes factor the better. Because obviously a low close factor indicates fewer sales hours and therefore less time taking to close the deal. And that's our formula. So we can see immediately that no point normal 150 is not unique. Let's calm me down. So wherever you see a decimal, that means we have a tie in. Close factor column on, By the way, we can change the clothes factor 2%. I think it probably will look better as a percentage and then increase the number of decimals. So, of course, if we now sort by the close factor column. Small is the largest we can now see which company takes the few. It's ours too close relative to the revenue that we generate from them. We can see that because we use in rank believe e g. Wherever there's a tie, we get the average position. So now let's move on to the close status. And here we just want the words fast, medium or slow. And rather than basing this on the clothes factory sold were simply going to base it on the rank. So I will say that the top third will have passed the middle third medium and the Bolton said, slow to calculate the clothes status. We use an if statement, and for a logical test, we can check to see whether the close rank in E to is greater than or equal to the maximum close rank divided by three multiplied by two. This means that the close rank is in the bottom three. Obviously, the highest number is the poorest rank. If that's the case, the value true will be the word slow. And if it's no, the value of false will be supplied by a nested. If statement. This time we'll test to see, but they close rank in E to is greater than or equal to the maximum close rank divided by three. If it is, since we know that it's not in the highest ranking numbers, this puts it in the middle and therefore will assign it value media it both our first and second logical tests proved to be false. Then the cattle value it falls will kick in. And that means that our ranks are in the low numbers. The bottom third, which means that we want the word fast. So let's create our final function. And for this we can expand the formula bar, so we need equals. If the first test is to see whether the clothes ranging E to is greater than or equal to the maximum close rank and it seems I start time, it comes up in the list and we can just select it in press tab to insert it divided by three multiplied by two to target the tops. Third, if it is high number in the case, a slow closed time. So although enter and we put the word slowed coma well, Santa and then the value false is supplied by our nested. If old enter a logical test, we can copy from the first, permitting the multiplied by two, which means that we're now very fine, that the close rank is greater than or equal to the top, said Coma. And thinking about it equal to isn't really required. You just want greater than that. So there's take these equal signs out, although enter if they're in the middle, said we won't medium comma old hands, huh? And then our cattle value false will be fast indicating there in the bottom, said the lowest numbers, or enter close in parenthesis for the next. It if enclosed in parenthesis for the outer is so when we can't be down because I still have the data sorted by close factor. I can see the top. There is fast. The middle of that is medium animals of that is slow 33. 6.6. Using LARGE, SMALL and COUNTA Part 1: Let's end. I'll look at manipulating numbers by looking at XL's large and small functions. 06 manipulating numbers that's open 06 using large, small and count A. In this example, we have a series of sales figures, ruled out claims and, in our summary work leads, were interested in displaying the top 10 spenders on the bottom 10 spenders. And this is exactly what XL's large and small functions will enable you to do and for a bit of further practice will do three versions about summary and with each new version, will introduce another level of complexity. Begin with you had a bit of charity to the formulas will create. Let's create some named Ranges, since we've only got whole columns, I'll see next hole and then go across to the Formulas tab and in the defined names group, click on Create From Selection. Yeah, especially find that the names to be used in the top row only now let me kick Okay, Excel creates our names, and we can then use these names in our formulas in preference to the rule cell references. So let's begin by having a look at the syntax of the large and small functions. The syntax for both functions is identical. Each function takes two arguments. Festival. You have the array, and this is normally a single column containing numbers or sometimes dates within which you want to find the largest second largest, etcetera. So in our case, it's going to be the total. Spend a column. The second argument K allows you to specify the nature of the value that you want to retreat. How close to being the largely shouldn't be second, largest, third largest or if you were using the small function second, smallest third smallest, etcetera. So let's complete summary version one the simplest of the three. And to make this one really simple, what we've done is to insert a new married column, which essentially has the K number. The second argument of the large ins all functions. So the large will be working in ascending order, iced first and then for small will work in descending order 10 from smallest at the top on the very smallest at the bottom, so the large function will be used to calculate the total, and then once we've got that total, we can use index and match to find out the client you spend that amount formula is going to be equals large and for our Arabian Goto formulas using formula on, pick up total spend coma and then for the K argument, you just click on Self a two and then we can just copy that down on the K argument is always being picked up from the first column. So we get largest, second largest, etcetera, and we can copy this formula to save a bit of time for the small equivalent on. Firstly, changed, the function names a small and secondly, we change the K reference to call in half. And then we can call me this one down, 10 smallest all the way down to smallest. So now that we've extracted these figures, we can simply look in sales. Find the client who spent that month, and then we can use index and match to pull back the name of the client. Let's just have a quick review of how in Nexen match work when index and matter used together to look up. A value index is always the outer function, and it takes two arguments. First of all, we have the array within which you're looking for. A particular value in our case is going to be the client column, and secondly, you have the road number. This is supplied by Ness in the match function. The match function needs a look up value. In this case, it's going to be D to That's the total Spend column in our summary worksheet, and we specially find the look up Ray. And that will be the total Spend column within our sales work. She's and then finally, we have the optional argument match type, and we wouldn't want zero for an exact match. So let's expand the formula. So any equals, index enter. And the first argument the array will be the client Cullen, which we have named the client coma. All enter and then for the road number. The second argument we nest, the match function Look up. Value will be D to coma and the look up array within which we certain for D to will be the total Spend column coma. We're looking for an exact match. Closed parenthesis to end match old enter on the closing premises to end index and then we can just double quick to copy this down and copy the formula to the bottom 10 spenders. And all we have to change is D two, which now becomes I to. So when we test it, we should see Kobe at the top on Ashton Western at the very bottom. Then they all the high spenders on the list. So that's the basic version of how summary. In the next video, we'll do a slightly more complex example where we haven't got that convenient column with number, which can then be referenced by the large and small functions. 34. 6.6. Using LARGE, SMALL and COUNTA Part 2: in the last video, we created our first version of our summary, and we allow ourselves the luxury of having an extra column. It contained numbers. These numbers could then be referenced by the large and small functions as the second or K argument. When you're building a model that uses the large function, it's not always convenient to have an extra column containing the number, which you can men reference, and it's usually preferable if you could come up with a calculation that will generate the number automatically. Naturally, you could put the number and literally, there's nothing to stop us type in a one in here, then on the second road to and so full. But what if you wanted a top 100 on the bottom 100? It will be far preferable to come up with a calculation that generates the number automatically as we copy the formula down. So that's what we'll be doing in version two of our summary. Let's move across the summary B two. Let's first of all, look a technique for generating the number that we need. We can use account a function, and the trick that we use is as follows equals counting, and we now need to reference his cell close to the cell containing the formula. You obviously don't want to reference the cell that contains the formula itself. That would be a circular reference. So we referenced this celeb. Um, if we left the formula like that, it would always return one. But if we now put Colon, we now have C one colon. See one. If we count the number of cells in that reference, we still have a one. But the trick that we use is to make the first part of the reference absolute. I can do that by pressing that fall on the keyboard. So now we have Dolisie one fixed and C one not fixed. So obviously, on this first cell, the count will be one. But when we copy it down because the first part of the reference days fixed will be going from C one to C two. So the count will be two and three, then four. And so? So that's the trick. If we enter the formula as we copy down, you'll see that the number increases by one each time. So all we have to do is to use exactly the same formula that we use last time. But instead of reference in a cell that contains the number, we used this count a technique to generate the number. So this is what the new version about formula will look like again we use in the large function on the array that we're looking inside is the Total Spend Column. But for the K argument, that is the number. How large, largest, second largest, etcetera. We messed in the count a function, and this takes either a single argument. Refer to his value one or a comma separated list of arguments. So which was used in one. Then we using this trick of having the original formula going from C one to C one, thereby generating one. But because we have a dollar sign in front of the first part of that reference, as we copy it down, the first part stays fixed on the second part increases. And as the second part increases, so does the range. Being referenced on would generate the numbers that we want. So by using the county function with about this little engine inside the former there itself, which generates a number that increases by one as the formula is copied down from row to row. And now let's use another trick that you should have in your armory, which is a trick that you use whenever you want to create a formula, which is similar to an existing one. So here's the trick. Will copy this live across the summary version one, and we know that what we want to do is to replace this A to with our account A. So we actually build the new formula in our be one cell. And the simple trick is, when we've got the formula, we copy it and then we just cancel. So in other words, we don't commit the changes that we've made to this. So we're using this cell for convenience. So here we wrestled, enter I live across so that we could nest on account, a function we paste that in. I'll just put another all tensor in here just to be consistent. That's the way we've been doing it so far. On all 10 to here is well and of course, under in this purely irritability. Everything's nice needs untidy. That's it. So that's the new version of the formula that we need for our B two, but we're building it here for convenience. And now all we have to do is to copy the final formula and make sure that we don't press enter because press escape or click on the cancel button on the left of the formula bar. Then we can live across, get rid of all these on pay star formula into C two, and that's it. So we copy that down. You get exactly the same results as we had before, but we don't need to have this extra column just that that we have somewhere to pick up the number. The number is now being generated automatically that in our corner. So it's much more efficient. And just to finish off some reversion to, let's copy this formula to generate the small formula. So the changes that we need to make our, of course the function itself that becomes small and then the references. So now we're referencing column G type colon, so you have G one DRI one, and then we put dollar signs on the 1st 1 and I was just about to copy that down on then. I've remembered that we actually want this to be in descending order, so we leave it as it is. We'll have ascending order. We'll go from 1 to 10. We want to go down from 10 to 1 on. The simple answer to this is that we prefix the count A with 11 minus. So if you think about it, 11 minus one, which is what this county generates will give us the 10 when we copy it down. Account increases to two. So we have 11 minus two, which is nine copy down again. The count increases to 3 11 Minus three is eight and salt. So we enter that and as we call me down the K argument of small, which determines how small sounds a 10 on ends on one. So we should have exactly the same numbers as we had last time. And finally, let's copy across our second formula. And the only difference is we no longer have column A containing numbers that we've got one column fewer, so be too needs to be changed to see to Yeah, we can copy this down, then do the same thing for the bottom 10 on again. D two needs to be updated this time to G two. So that sound, new, improved version of the summary and the improvement is that we don't have to have this extra column with the numbers we used in the count a function to generate the required numbers within the formula itself. 35. 6.6. Using LARGE, SMALL and COUNTA Part 3: in the last video, we improved our summary by incorporating the count function to automatically generate the K number required by the large and small functions. Let's move on to version three of our summary and introduce a bit more sophistication in this version. We want the user to be able to choose whether they want to focus on total spend or total units. So, so far are formulas have unconditionally been analyzing the Total Spend column. We now want the user to be able to choose between total spend and total units. And of course, we've named both of these columns. So we've got total underscore spend and total underscored units as the automatically generated names for these columns. So the first thing we'll do is to name the choice Cell a one instead of reference in a one . Let's call this cell total choice to manually name a cell. We go to the name box in the top, left here on type the name Total under school choice, and always remember to press enter. Otherwise, the name doesn't actually register. So now we want to move from this formula, which unconditionally uses the total Spend column as the array argument of the large function to a conditional structure. And that, of course, means an if statement so logical test will check to see the value that's currently in place in the cell that we've now called total choice. So we can say the total choice equals total spend Been. The value of true is the large formula that we had in version two of our summary on the value of falls. It's the same large formula, but for the array argument instead of total spend, we have total units. So let's create our formula, and the first thing we can do is to copy that formula that we have already. So let's copy the large formula. Let's just make this a bit bigger, and we can also collapse the ribbon to give ourselves a bit more room. So we need equals. If, well, Penta and then our logical tests is going to test the value that's been chosen in cell a one. So total choice equals double quotes, total spend, comma Walt enter, and then the value of true. He paced in the formula from version two of our summary, and I'll just put a few spaces into invented a bit further coma. And then we just need to modify this cell reference This version of the summary we've introduced two new rose. So the rose reference by the count a function have also increased by two instead of C one. We simply need C three on. Then we can copy a lot of this and paste it as the third argument about it function. So all tender on based. And the only change we need to make is that this now becomes total units and that completes our if statement so old enter and the clothes in parenthesis. So you can copy this down and then use it to generate this small version. So first large become small. Then we modify the cell reference. So all our seas replaced by G's and finally so that we can have descending order we prefix the count A function with 11 minus 11 minus one equals 10. You copy it down. You haven't minus two equals nine on so forth and we do that here as well and that's it. We can then answer on copy this one down as well. And now we need to use index and match to pick up the names of the clients, so we need a very similar if structure to the one that we've just used. We'll need exactly the same logical test, checking to see what they use is chosen in cell a one which we've called total choice. If they've chosen total spend, then for value is true. We use index and match in. Next be in the outer function on our array is the clients. That's what we're trying to look for to calculate the room number used match. Look out. Value is now in C four because we've got expert rows of the top. So all is our first data road within our summary data on because the user has chosen total spend, I'll Look up Array is the total Spend Column, and the match type is zero Bren exact MASH. Let the user hasn't chosen total spend. That means they've chosen total units, so the value false. We use index pulling back a client we met in the value in seed for, but we're matching it in the Total Units column because as a result of the user's choice, the formula in column hall will now be Eritrean units sold rather than total spend. So to get the company name, we can start my copying the first part of our if statement, it's gonna be exactly the same. There we go back into our summary version two. You can use this convenient trick of building the formula in a convenient location, copying our final version, pressing the cancel button on, then move in that code to its destination. So here we need a lot Kenta on here, we paste in the start about it. This is the logical test part of the if statement and index becomes the value of truth. So let's just invent it and then terminated with a comma. Then we can copy the second arguments old enter and paste it as the third argument. And of course, the enemy changes. If total sorties doesn't equal total spend, it will equal total units. And then the only other change is that si two will become C four. Because, of course, on summary version three, we've introduced two new rose at the top so that the user can make that choice so seem to now becomes a sea pool on to finish out of statement. Let's just do old enter and put the clothes in parentheses, and when I pasted, I managed to end up with two equal signs of Let's get rid of this extra one so we can can't be all events. So we built it here for convenience because it handsome, useful code that we could use as a starting point. But we've only been using it as a work area. We don't actually want to change this cell. So once we sure we've got that on the clipboard, we can just press cancel. No harm has been done to this cell, but we now have on the clipboard the formula that we need for this cell. So we just paste it in and it's good to go. We can then copy that down and then paste another version in the bottom 10 section on. The only change that we need to make here is that instead of reference in column C, we now reference in column G so that to see falls need to be changed to G fools. That's the only change I can see. That's just test it on. Help me down. That completes version three of our summary this is the version that really had in mind or this section. I thought it might be useful to us to do it in three stages and introduce a bit more complexity at each stage. 36. 7.1. Array formula basics: in this section will move on to look at another type of Advanced Excel formula called an array formula, and to start the ball rolling in this video. Well, look at exactly what in a rape Bolnar is. So in 07 rate formulas, let's open 01 array formula basics. The classic implementation of formula in Excel is to create an initial formula and then to copy that formula into adjacent cells. So, for example, in this worksheet, which contains rows of data, each of which recorded sales transaction, if we wanted to have a total price on each row, we would create a simple formula which multiplied the quantity by the unit price. And then we'd copy this four minute down for every road, having added the total formula for every road we could then aggregate that Total column. But what if we were only interested in the aggregation? What if the row by row D sale was purely an overhead, a calculation overhead which had to be tolerated every time we opened the workbook. For example, in this work feet, we have 34,000 rows. What if we had half, um, Indian, so clearly place in a calculation on every single road when all you want is toe aggregate, that calculation is quite an overhead, and it's in this kind of scenario that you can use what's called an array formula. Before we get into the nuts and bolts of how rate formulas work, let's create a couple of useful named ranges for ourselves, which we can then reference in our formula. The two columns were interested in analysing our quantity in Munich price when working with a Wraith formulas because they're so process or intensive, Excel doesn't allow you to reference an entire column, so for that reason will need to create a reference purely to the data. So the unit price we click in J one and press control ship Down Arrow to select all the way to the bottom of the data and then to name the selected data. We can get across to the Formulas tab and in the defined names group, click on Create from Selection and we ask Excel to use the text in the top row as the name of the range, and then let's do the same for quantity. You're gonna one press control shift down arrow and then create from selection, extracting the name from the top row. And if we click on the name box, we can see the two names which have been created. No space is allowed, so unit underscore Price is automatically applied to the original text unit price. Let's now move across to the summary work feeds and look at the aggregation that we want to perform the 1st 2 white into straightforward because they only require reference to one color to find the unit. So we simply need to refer to the quantity column so we can say equal some and then use the quantity name that Excel has just created for us. And then similarly, the average equals average and again quantity, so the average quantity per transaction is 11. And if we wanted a bit more precision, you could just increase the number of decimals. However, when we come to do the total on average revenue, it's not quite so straightforward, because what we want to do now is to multiply the quantity by the unit price on. We have to do this on a row by row basis. So the very thing that we're trying to escape doing individual calculations. We still have to find a way of achieving on the way that it's achieved in an array formula is that you can perform calculations on an entire array. Honore is simply a collection of values, so in this case we have an array, which has a width of one Colin on a height of 34,000 rows. The values within that range constitute the array and what you're permitted to do in an array formula. What you can't do in an ordinary formula is to carry out operations on entire arrays, And the thing that makes it work is the fact that the arrays in this case both have exactly the same shape. They've got the same number of columns in the same number of rows, and therefore, when we perform a calculation on the to raise, Excel simply marries up each of the rose within the two arrays, so formula will still need to use the some function and will only need a single argument. But that argument is a calculation on the calculation involves to a raise the array that we've called quantity, which is all the data in the quantity column needs to be multiplied by the array, called Unit Price. And because both the rays have the same shape, Excel is able to multiply the quantity on the first row of the array by the first unit price, second quantity by second unit price all the way down to the bottom on the result of calculating, each row is stored in a virtual array, an array that exists only in memory. And, of course, battery has the same shape. One column, 34,000 rows. So the sum function will simply be the total off this result in virtual array. So now that we have the luxury of named Ranges, the function is very simple. It's all right. We need equal, some quantity multiplied by unit price as a normal excel formula. It doesn't make any sense. A normal Excel formula would require us to multiply a single value by a mother single value , for example, see to my D to so we simply press enter. The result that we get will never be the correct one. So what we have to tell Excel is that this is not an ordinary formula. We want the entire A raise to be included in The calculation on the signal that Microsoft came up with is that you press control shift enter to indicate that this is an array for regular instead of precedence, I press control ship Bento. Then we get the result that we're looking for, and we can do the same thing for the average equals average quantity multiplied by unit price. But the important thing here is that we then press control shift, enter, and we probably want to show the decimals on this one. That's the basic introduction to how array formulas work. So the key thing it's performing a calculation on rays of data rather than on individual cells. And to signal this to excel, you press control shift enter. And that's often abbreviated to C. S E. So when you're searching on Google, if you search for CSE formulas, you're talking about a ray formulas, which require that you press control shift, enter whenever you create or edit the formula 37. 7.2. Using conditionals in array formulas: in this video will extend the basic array formula that we created in the last video by making it conditional 07 right formulas that's open zero to using condition ALS in array formulas. So this is a far as we called in the last video. We have an array formula which multiplies quantity by unit price and then returns the total rural rose. What we now want to do is to split this by both branch and product range. So we want a matrix of conditional co tal's and each time we want to conditions satisfied, the branch name and the product range name be to the branch name has to be Aberdeen on the entry in product range has to be Jamia and so forth on the normal circumstances, we would use some ifs, but the problem here is that some ifs requires a reference to two ranges. But some range on the criteria range, and what we need to do is not to supply. Arrange to supply a calculation which multiplies one array by another. And that calculation is not an acceptable argument for the summits function. So what we have to do instead is to find a way of using the calculation that we've currently got, but making it conditional so that as we go from row to row quantity, times unit price will only apply when the two conditions are met. When the bronze is what it should be on the product range is what it simply before we move on to look at our formula, let's create two more named Ranges. We go across the formulas and click on name manager. We can see the two names that we created in the last video quantity in unit price. Let's go across the item sales, and it's now adding the two criteria ranges that were interested in branch and range. Now you remember we can't reference the entire column when we working with the Wraith formulas, we have to reference the individual cells. So click on the one press control shit down, and then we can use this convenient create from selection and use top row. Then let's do the same range. Cleveland. I won control ship down, create from selection, so we now have four names that we can reference in our regular. So we look into creative formula in Selby, too, which we can then copy to the right or all the other product ranges and copy down for all the other branches on the bronze that applies to Selby to Is Aberdeen on the product range is called Jamia, so we want to have a formula which performs a logical test, and we can do this by nest in an if statement, the first parameter if is a course, a logical test, and we need to combine two tests. We want to test every row in the brown Colin the branch array to see whether it matters the value in cell eight to, and we then want to perform a similar test on every road in the Range column to see whether it matches the value and sell be one. The normal way of combining two logical tests is to use the and function, and then we have a logical test one a logical test, too. But unfortunately, with our array formula, this one work, the problem is that the and function only returns true or holes, and what we need to have is a logical test but returns an array of true or false values. Se ro one is true. We go ahead and multiply the quantity by the unit price. Otherwise, we put a zero. And of course, we know that the cell B two, if we compare the branch and the range, none of the 1st 4 items will return. True because we don't have Aberdeen and Jamia as the combination. And it's only when both of these are in play that we get a true So the formula in Selby, too, will return zero for the 1st 4 rows of the virtual array. So the solution is to use exactly the same approach for the logical test that we used in the last example, multiplying one matrix by another. The first Matrix is created by using the statement. Brown equals a two. Ron's applies to the entire Bronze column, which is an array. So if we compare an array to a single value and Excel will simply go through the array and compare every row to that single value, it will then create a virtual array in which every road contains either true or holes. True, of course, equates to one and false equates to zero. So we have one array with exactly the same size as the branch array the quantity array unit price containing either true or false on every single run. And then we do the same for the other evaluation we have. The statement range equals B one on Excel will then compare value and be one to the value in every row of the range column. Wherever it finds a match, it puts one wherever it doesn't put zero. So we end up with two virtual tables, all containing true or holes on every road, and we simply multiply boasts to virtual arrays. That gives us the final virtual array, which itself would likely contain one or zero on every road. Because, of course, only one multiplied by one true multiplied by true will give us the output one or true. Any other combination will give us false. So by taking this approach, we end up with exactly what we need, which is an array of true or false values. And then Excel evaluates every road that array wherever it finds a one. It goes ahead on that road to multiply the quantity by the unit price. Wherever it finds a zero, it simply puts zero in the result in a rain does he said. The formula in Selby, too, will produce all zeros as the 1st 4 items in the result in virtual array. And of course, the final part of the puzzle is that the sum function returns the total of all the items in our virtual array. So that's the approach. Let's now Bryant out formula. So we want to write a formula, and Selby, too, which we can then copy across and copy down. So we'll need to think about the placement of dollar signs. Let's begin by expanding on a formula bar. So we doing equal some all kinds of and then we missed in the bunker. And even though there's no further nesting, I split the parameters on two separate lines for readability. So old enter first argument is our logical test. I say we want brunch equals Have a dean, which is in a to on when we copy the formula across. We don't want the A to change to a B to A C etcetera. We wanted to stay as a but when we copy down, we do want the to to change to a 34 etcetera. So we just press at all until we have a dollar in front of the A and then we'll multiply in that test by the test range equals 31 And this time it's the reverse. When we copy across, we do want the letters that change when we copy down. We don't want the road number to change, so we need B dollar one. Then we've got to think about operator precedence. So what Excel will do is to evaluate this multiplication first. And of course, what we wanted to do is to evaluate branch equals a two evaluates range, equals B one and then multiply the to result in the rays. So we'll need parentheses around both of these tests. Comma to end The first argument value it true will be the calculation that we saw in the previous video Bold enter, which was, of course, quantity multiplied by unit price, coma or pencil on the value false is simply zero or cancer. The closing parenthesis to the if old answer on the closing parenthesis for some and then very importantly, we don't presenter. We press CSE control shift enter. So now, because of our strategic placement of dollar signs, we can go ahead and call me to the right and then double click to Kobe down 38. 7.3. Using concatenation in array formulas: in this video, we'll look at how we can use in a rape formula to perform a look up operation using multiple criteria. Let's open up 03 using Can Captain ation in a rate for Mueller's. So this is an example that we've seen in previous videos. We have a list of sales transactions, and we want to find out the unit cost for every road. But in order to do so, we needed to use to cry interior the purchase ideas not sufficient, because when we look in the purchases data, every item that was purchased is listed on a separate road. So to find the price that was paid for a particular product, we need to combine product I D and purchase I t. It will use an array formula. We can create a combined column virtually simply by concoct innate into a raise. We don't physically have to create an extra column before we discuss the formula that creates unnamed ranges, which we can use in the formula. So in the product idea, hurt society, and we can't reference the entire column. When you're working with the race, you need to reference just the data cells not the anti column. They control ship down to select all the way to the bottom and to create the names formulas create from selection. We want the name speaks up from the top row when we click. OK, then we do the same for you. That price that Excel creates our three named Ranges to perform the look up we use. A combination of index and Match index is the outer function. On its first argument is the array. That's to say, the array that contains the item that you're looking for. So, of course, in our case, it's going to be the unit price column within the purchases. Work feet, then to calculate the road that will contain the answer. We messed the match function as the row number arguments of the index function her match. Our first argument is the look up value, so we're placing our formula in road to column F of the sales worksheet contains the product i D. And column C contains the purchase i d. So look at value is the can Katyn ation off these two values F 2% C two. Then we specify the array within which were looking all that can caffeinated value. And because we're creating in the rape will get. What we do is to can Katyn ate the two columns, so we use product i d. Ampersand purchase I d. That's to say the entire products i D. Column can captain ated with the entire Purchase i D column and when contaminated to a race , Excel of automatically in Katyn eight Road one of product i D with Road one of Purchase I. D and place that into a virtual results Ray. And it's within this virtual array. But Excel will be searching for the lookout value when it finds a match. The row number is returned to the index function on the unit. Price on that road will be the result of the function. So let's write out formula we use in a nested function. So let's expand the formula bar. So the outer function people's index enter. And for the array argument, we simply specify the unit price column, which we've got a name for. Coma old enter and then for the road number weakness, the match function. But look up. Value is a concatenation off product. I d purchase i D. So to Empress end. See to coma. I went to the look up array. We can captain eight to a race, and we do them in exactly the same order. Product. I d. Empress end purchase I. D. Coleman. And then the match type is zero. Because we want an exact match closing parenthesis to end match all 10 sir, and the closing parenthesis to end index on. Finally, because this is an array formula, we press control shift enter rather than just enter. Now there are 30,000 rows also in this work fleet and when we can't be down will be reminded of one of the limitations off working with the rate formulas, they are quite process an intensive, so they can take a long time to calculate. You do have lots of rose in the work treat, so that's what 30,000 rose on average machine. So obviously you're either you're working with a slow machine or you have lots of rose. Then there's gonna be a trade off between the sophistication provided by using the rate formulas on the performance hit that you can expect if you end up pushing your computer system to its physical limits. 39. 7.4. Array formulas and data tables: So far in this section, we've looked at a rate formulas which perform operations on the raise, such a multiplication and concatenation. Let's now move on to look at another type of every Formula One that uses an array function . We'll start by looking at the table function 07 or eight formulas that's open 04 ray formulas and data tables. A data table is a special Excel construct, not to be confused with pivot tables or Excel tables, which live in the insert tam. There's nothing to do with pivots, nothing to do with tables, data tables he used in what if analysis. They allow you to create a matrix of possible values when you're doing projections or looking at best and worst case scenarios. So in this example, we're doing that projection to calculate gross profit for the year, and we've got 3/4 of actual information, and then we have some budget information or quarter for, and we want to use our data table to give us a bit more flexibility to look at a range of possible profit values. The way that the data table works is that you have to follow a certain structure in the top left of the table. You must have a formula and then below the cells that contain the formula. You can put a range of substitute values. These values can be substituted for any cell referenced by the formula, either directly or indirectly have very birth to as the road input values and then optionally on the right of the cell that contains the formula. You can put a second set of substitute values, and these refer to as the column input values. And again they can act as substitutes that any single fell referenced directly or indirectly buying the formula. So in this example, the two variables that we're using is the number of days boot for our banquet. Swedes and the number of days boot her out conference weeds the really input values with actor substitutes for the banquet. Sweet Days on the column. Input values well, actor substitutes for the conference suite bookings. And to add a bit of flexibility to the model, we allow the user to change the stunt in value for both the column and road input values. So if we want to be pessimistic, we could say go from 60 for the conference suite. Bookings on. We've simply got a formula in here which says on one, as you go to the rights and again, if we being very pessimistic, we can say what he found. Banquet suite bookings are also quite low, and if you want to be optimistic, weaken started 70 80. So those are the variables that we're working with. Let's now look at now. Excel implements the rape formula. First of all, you select the entire matrix. The top left of your selection will always be the cell that contains the formula. If you're using row input, only, then you'll have a matrix of cells, which is one column white. And obviously, in our case, we use in both row input values and Colin input values. So in selecting the entire Matrix and then you don't actually type any formulas the command that you need lives in the data town and it's in the forecast group. You click on what if analysis and shoes data table and then all you need to specify is which cells are being substituted begin by special. If I in this cell, which your row input values the values arranged in rows are going to be substitutes for. So, as we've said there substitutes for our banquet suite bookings if he's on road to and cause a ball so e to And then we specify the cell for which the values we've got arranged in Collins with actors a substitute. And in this case, it's the conference suite Bookings. I'm not saying 85 and that's all that Excel needs to know when you click. OK, it populates the table, and it does so by using a single formula. So when you click on any cell within that matrix, you'll see exactly the same formula displayed, and it uses the table function. So here we've got an optimistic matrix, and then we could go in change the start in values two medium figure out or to a pessimistic figure, and that single array formula updates the entire table 40. 7.5. The TRANSPOSE function: Let's move on to look at another array function this time one that you can use yourself rather than one that's created automatically for you. 07 Array formulas. That's 705 transpose function. The transpose operation simply means to change the orientation of your data. So, for example, if we have the days of the week in this orientation, we can can't be. And when we paste, we can transpose. So that's the transposed operation. The transpose function, by contrast, enables you to achieve this switch in orientation in a dynamic fashion. And it's particularly useful when you're summarising data, and it's more convenient to see the summarized data in the opposite orientation to the original data. So in this example, we look at daily sales. Let's say in our summary work seat, we simply want to keep track of the total for each branch, and that's displayed at the bottom of the table using the total row. So if you haven't used this before in table tools design, we'll see there's an option here for Total Road. When that switched on, Excel allows you to automatically aggregate each of the columns so all we want in our summary is the headings and the total road, but the convenience. We want them in columns. So the first thing we need to do is to measure the Matrix so you don't highlight from a one t. J. One very conveniently in the name box. Excel is blazed by mentions of my current selection so we can see it one row by 10 Collins . And to use the transpose function, you simply select the Matrix, which has the opposite. I mention in this case, let's do headings. First you need to select the Matrix, which is 10 rose by one column. So, as you can see, like the table function, which we saw in the last video, the transpose function is a function that populates multiple cells simultaneously that this function you enter manually. There's no command which generates it. As we saw in the table function, they simply equals transpose. And then you specify the array, which you want to transpose within the currently selected cells. So you go across the daily sales and we selectable that the head is, and you probably know that you don't need to put a close in parenthesis when you only have a single function in your formula. But what we need to do is to make sure that we press CSE control, shift and enter. So we now have a dynamically transposed version off the original data. So let's say for some reason we wanted to abbreviate the headings, it would change Birmingham to be apostrophe. Um, when we go back to the summary, of course, we'll see the updated version there. But now let's do the same for the totals. So I'm including the heading in. My selection equals transfers, and we strolled bottom and drank across all of the total cells. So you always have to make sure that the range that you specify is the exact opposite. In terms of rows and columns or the selected range, you're transposed formula and then ask before we make sure that we press control ship enter . So we've got a summarized version off the table, and just to emphasize that it's dynamic, we go across the more data. Select all the data here, missing out the headings, of course, so controls it writes. Control it down, copy all of that data and paste it into the table. We just need to click in the bottom. Later sell just about the total road press the tab key, and that creates a blank grow into which we can then paste. And I should have done that before the copy because I've now canceled the copy. Soccer go back could be again and then based into my blank right. Of course, when we come back to our summary, we have the new updated total. That's all there is to the transposed function. Very simple, but sometimes very convenient feature. 41. 8.1. Using the WEBSERVICE function: Microsoft excels. Web service function is used to get an external response from a Web service. A Web service is an application designed to deliver a particular type of information to any client who requested when the request is made. Parameter values are normally supply, which determine the result generated. It's very similar to simply visit in a Web U R L and having a Web application which supplies content to you. The big difference is that a Web service is designed to supply content not to humans, but to another application. So in this case, that other application is excel on. The mechanism that we're using is the Web service function. We specify the Earl of the Web service application, and normally that girl has embedded in it. One mobile parameters on the Web service as a result of the request, produces an XML response and then delivers that into the cell, which contains the Web service function. Web services can deliver other types of content apart from XML. But in the case of Microsoft Excel, XML is the only response which is understood. So in using excels Web service function, you can only work with Web services capable of producing an XML response. So let's take an example in the training folder will now working in zero for Web service filled, exhale exposed. And that's going to fold one using the Web service function. Now let's examine our example international pricing that's open that. And in this example, we have a series of prices using GDP as the base currency. We want to be able to translate those prices into all of these different currencies, using up to date conversion rates. So what we want to do with the Web service function is defined a Web service which will give us the conversion rate from GBP. $2 euros, Canadian dollars, etcetera. So let's now return to the training folder. And if you open up the text vocal Wandell software for this demonstration, we're going to use Mondor dot walk as our Web service supplier. And the reason to do in this is that they have a 14 day triable service, which allows you to make a limited number of requests to their Web service. So the first thing we'll need to do is to sign up, so just copy this sign up. You are L and Paste it into your browser using paste and go and then just fill out the information and click on request demo key now. And if all is well, within a few minutes, you should receive an email like this one which contains an A p I key. So I'm just gonna copy this a p i and paste it into note pad so that would just need to know the u r L that we need to access when we use in Excels Web service function. So we go across to the website, click on the link on the left here, currency exchange rates, XML Web service and then just click on this web ap I access link on the first. You are role that we see here gives us all the information that we need and it gives us a breakdown of the six components to that You are l on. What we have to remember basically is that the base currency comes first. So in our example, obviously the base currency will be UK price. Then comes the target currency and then components five and six. You'll notice that both optional So in our case it will simply omit them the amount of convert defaults toe one and the date of conversion defaults to the current date. So that's exactly what we want. So we can just copy the first part of the URL admitting the last two sections. I'll just face that here as well. That and save my changes. So now we're ready to use the Web service function in Excel. Let's start with USD. I have the u. R l still on the clipboard, so I can just tie equals Web service and the girl is a string. So it has to go in double quotes, double quotes, and I can just paste it in close quotes, close brackets and presenter. And of course, the thing that I forgot to do was to put in my keys. I left this sort of default key in there. I obviously need to replace that with the A p I key that they supply to me. So let's just go back on, grab that, just paste it in here. It's now we should be good to go and we have a price. Let me just widen this soul so that we can see the result on then see what we need to do before we can actually use this value so we can see that in this case the Web service actually returns the value that we want. The only problem is that the value is quoted. So all we need to do to get our calculations to work is to remove the quotation marks on. We can do that using excels, substitute function. So around our Web service, let's put the substitute function and use that to remove the double quotes so equal substitute. The Web service function is the first argument. In other words, the text. We put a comma at the end. Now we supply the old text in other words, the text we want to remove and that text has to go in double quotes. Which raises the question. How do you put double quotes inside double quotes? I don't know if you've ever had to do this, but basically you double up. So you put two double quotes to represent double close. Hence we put open quotation marks, two double quotes, close quotation marks, so four double quotes in total coma. What do we want to replace them with? Nothing. So we simply put open and close double quotes to indicate the mold stream close brackets. And, as you can see once the quotation marks have gone even though, strictly speaking, we still have a text value because the text value consists of numbers only Excel automatically converted to a numeric value and uses it to perform the calculation. So I'm just gonna reinstate the Marrow column with so you can see all the different currency rates. And then the final thing we need to do is to modify the default formula. So it's not using euro and us deep, but it's using G V P as the source and then picking up the conversion currency from the cell above the cell containing the formula. Simple way of doing that is to festival, delete euro and replace it with GBP. Then we delete USD. But this time we need to replace USD with the contents of this cell above the cell containing the formula. So to do that, we close quotation marks, then put an empress end but can cast a nation and click on the cell above which in this case is J two, so that picks up the currency in that cell Then we put another empress end and then open quotation marks. So you just insert in that variable string into the URL. So when we press enter, our calculation should now actually be the correct one. And what we now do is just to copy that formula. It's of the rights, and because there are no dollar signs were always picking up whatever currency is in the cell above the cell that contains our formula. So that's an example of using excels Web service function. And on this occasion, the result that was produced was, in fact, the result that we needed. In most cases, however, the Web service function returns an XML document containing a hierarchy of elements and attributes, and you then need to use a second function, filter XML to navigate within that structure and extract the data that you need. So in the next video, we'll move on to look at working with the Filter XML function 42. 8.2. XPath overview: in the last video we used excels Web service function to retrieve information from a Web service, and the information that we retrieved was exactly the information that we wanted. This is not normally the case. What's normally returned by the Web service is a complete XML document, and you then need to navigate your way through this XML document to find the data that you actually want. The function that enables you to do this is filter XML, and it takes two parameters. The first is the XML that you want to work with, and this is normally supplied by using the Web service function. The second parameter filled XML is an expert statement. So what is expert expert is an XML technology, which is used to retrieve information from within an egg seminal structure, and the expanse statements used with filled XML are what unknown is location steps. So a typical experts statement will consist of one or more location steps separated by the forward slash character. The use of location steps in experts is very similar to working with a directory structure . You start on the C drive and you're going to fold a one inside folder one you find folder to and so forth. So in this illustration, we're working with the branches. But XML structure that we saw before and we use in three locations steps the first take sister branches, which is the root element. The second takes us to the child element of branches, which is branch, and the third takes us to a child of the branch element, which is branch manager. There are three parts to a location step statement. First of all, you specify the access that you want to travel along, and there are quite a few to choose from the default. Is the child access. In other words, you start with a parent and you look inside for one of the child elements. The second component is called a no test, and this allows you to specify the node or nodes that you want to retrieve. Traveling along the specified access. The third part of a location step is optional. It's called a predicate, and it allows you to filter out any knows that you don't need A from the note seven you've retrieved by traveling along a given access and looking for a particular set of nos. so he returns while branches. Example. We have three locations. Steps, branches, branch, branch manager in locations. Step one. We trouble along the child access to get to the branches element in location. Step two. We again travel along the child axes and this time our no test his branch and then in locations Step three were again traveling along the child access and this time the no test , its branch manager. And, of course, in none of the three location steps, do we use a predicate? As I said, the predicated is optional on. We will encounter its use later. So just to recap, location steps are separated by the forward slash character. Whenever you see a location step, which appears to consist purely of a no test, this means that the child access is being used because it's the default. You don't actually need to specify the child access you could if you wanted to, and this is how it would be written child fathered by two Coghlan's branches child branch child branch manager. But of course, because it could be omitted, everyone makes it so. It's just written branches branch branch manager. So that's a quick overview off X Path location steps in the next video. We look at the use of expert statements in conjunction with the Filter XML function and then in the next section, when we discussed the use of xsl style sheets, will see how expert is used to enable you to home in on the various parts of an XML structure as you're performing transformations. 43. 8.3. Using FILTERXML and ENCODEURL: in this video will move on to look at the other two functions, which are available when you're working with acceptable in Excel, filter XML and encode U R L So in zero fall Web service Filter XML the next part that's now going to number three years in built XML and encode u R l To see how these two functions work, we're going to be using a Web service from a free website called Geo Names or Award. Let's begin by opening up geo names dot txt And here we've got all the information that we need to work with this free site, and the first thing that you'll need to do is to sign up for a free account. Has the U. R L, which you can do this. So let's just copy that you are. L just move into your favorite browser and use the paste and go feature to get to that you are l. So the first time you visit the website, we'll need to create a new user account. Just choose a user name. No space is allowed, and then just put your email, confirm your email, choose a password and confirm your password and then go ahead and create an account, and the confirmation email will be sent through to the email address that you supplied. The email will look something like this, and it will include an activation link so you simply click on the activation link to activate your account. Once he validated, your account will automatically be logged in and thereafter. Future. You obviously can just click on the log in button, put in your credentials and log in. And then there's just one final step you need to perform on. That is to going to manage account, and you get to that by clicking on your user name in the top, right, you'll see you've got the two wings there. Want the logging out on the other, which consists off your user name is the managed account link. So you click on that, and here you have the opportunity to change your user email and to change your password. The thing that's most important for our purposes is to be able to use the free Web services , and to do that, you have to click on this link at the bottom, says you can see it, says the account is not yet enabled to use three Web services. Click here to enable, So we click on that and then we good to go. So from now on, we can use that free Web services. There is a limit as to how many requests you can send that day, but beyond that, it's all free. So we now click on Web service. A Van Web service overview. These were all the different Web services. And of course, the XML column is the one that we're interested in. Because Excel doesn't offer an option to use Jason, it only uses XML using the Web service function that we've seen. So the Web service that we're interested in using it's postal code search, which is you can see, is available in both XML and Jason. So let's click on that. And here we have all the possible parameters that you can use with this Web service, and if we go to the bottom, we can find an example which we can use in Excel with the Web service function. So that's just coping that, and I'll add that to the text file. And, of course, when using this girl, you simply need to replace that user name demo with the user name that you chose when you signed up to this service. And, of course, the other thing will need to change is this. We want to have different postal codes when we actually use this facility. So let's leave the text file. It's no open up the Excel file. We're working with postal codes. Example, and inside it you'll find hotel information. So you've got the address of each hotel and then the rating, the average rating given by our start members. And let's say to completely solution, we need tohave the latitude and longitude off each of these hotels, and we're going to use a Web service to obtain this information. Select at three new columns. The first will contain the XML that we retreat from the Web service. Then we want the latitude, longitude and it's copied formats. The logic of having a separate column for the XML is that both the latitude and longitude can then be extracted from that one cell instead of embedding to Web service calls in each of these two columns, and this way of working becomes quite important. If you're paying for every single call that you make to a given Web service. So now let's put the Web service function equals Web service. And as he saw in a previous video, the only parameter is the URL, which is a string on which we can just can't be from text file. And before we do that, I'm just gonna change the maximum number of rows toe one. We don't really need that much detail, since we're only interested in the latitude and longitude. So we called you all of that, paste it in, and then we need to make a couple of changes. First of all, the postal code that we're looking up is the postal code from cell F two. So we need to tag on to. And this is where we use encode. U R l What it does, basically, is to take your data on converted into a format which is Earl Legal. In other words, any characters which were illegal in a URL will be converted to their equivalents. So, for example, spaces will be converted to percentage 20 so just not enough to we closed quotes, then use ampersand. Then we use encode u R L click on it to close parentheses to end the function. Now we need another ampersand on. We reopen the quotation marks, So the whole thing looks rather strange because, of course, we can an M percent inside this string. But as you probably know, 1% a very common in you RL's where you're supplying parameter values. Then, of course, the other change we need to make is to replace Demo with our user name. Let me close quotes, close parentheses and we're done. We can then copy that formula down and each of the cells in column h will now contain a slightly different XML document. What I always like to do at this point is to copy one of these XML documents into a text file so we can take a good look at the external structure. So let's go back into the folder and create a text document, and it's just rename it results dot xml. Yes, we do want to change the fine extension, and then we can just open it and paste in our XML. So here's our X terminal document. We can lose the double quotes so you can see we have a Roose element of geo names. And then it's got to child elements, total result, count and code. So let's just select all of these and tap them in, and then code has child elements that contain the data that we actually want. So again, let's just tap those in on the two bits of information we want their Agassi lacked on LNG. So in terms of our experts statement, we're gonna have three locations steps g, other names code and then l a T or L N dri. And in each case, we maybe along the default child access so they can from parent to child each time. So that's saved this document. And now let's get back to excel so we can hide column H That's Desprez escaped to coming out a copy mode and then create a formula. So we've got equals Filter XML. The first parameter is the XML, which is in our hidden cell H two on the second parameter is our expert statement which goes inside double quotes. For an absolute experts statement, you always start with a forward slash, which represents the root of the XML file and then you put your location steps so first, as we said, was geo names. Then we had code and then the latitude is L. A T close quotes clothes for embassies on. We can just copy this entire formula because the longitude is virtually the same. So let's just paste it in for longitude a man change L A t soup LNG and we could to get So we can now coffee this formula down for every road on because basically, you know, hidden call him. We return in a slightly different XML result on each road, the experts statement is retrieving a different value each time. So that's our first look at using expert statements with the filter XML function on. Just remind you, we have these three locations steps in each case we use in two off the three possible components we use in the access on the no test. We're not using the optional predicated. It will come to that later. And as I said, because the child access is the default, it's normally admitted. So if you did want to write it in, it would be child colon, colon in each of these three cases, so we'll see you get exactly the same result. But if course because child is the default axis, it's always omitted. So let's just undo. Although the Filter XML function is normally used in conjunction with the Web service function, it's also possible to use it on local XML files. I will take a look at doing just that in the next video. 44. 8.4. Using FILTERXML with local XML files: Although the Filter XML function is designed to work with Web services, you may occasionally find it convenient to use it with your local XML files in this video. We'll look at a work around for doing that. It's going to sub folder for using Filter XML with local XML files. Let's open up. The Excel file will be working with expense claims. So here we've got a bunch of expense claims by staff members. We want to do the equivalent of a V look up to find out the I. D first name and last name off the head of department. But we want to do this from an XML file. Obviously, in some scenarios you'll be able to simply import the XML file into a table on then USVI look up index match as appropriate to pull across the data that you need. But as we've seen, the whole XML files lend themselves to being imported into an Excel table. So there will be times where it's more convenient to connect to the XML file externally and then to use the Filter XML to pull in data from different parts of that XML structure. There are a couple of limitations. So let's have a look of the work around that we're gonna use. Let's create a new work Feet called XML Connections. And the first thing I need to tell you is that this work around has one key limitation. And that is because we use in Filter XML, which takes us its first parameter. An XML document. The XML file that we connect to can't have more than 32,767 characters because that's the maximum number of characters which will fit within a cell in Microsoft. Excel on for the Filter XML function to be used. We have to have the entire XML document in one cell. So we go across the data, the feature that we use it, get external data and we simply connects to the XML file as a text file on this operational course, excel to import the XML, and under normal circumstances, it will take up several rows. What we want to do is to have it all imported into a single cell. So the first work around is to use what's called a mini fide XML file. Let's go back into the train in folder so you can see what this means. Here's the XML file that will be connecting to Head Start XML. But as I've said, if we import this, it's gonna be imported on lots of different rose rather than into a single cell. So what we've done is to convert it into what's called a min ified XML File one with all the spaces taken out. So we open up heads, many fight. It's exactly the same document. But as you can see, all the returns, tabs and spaces between the elements have been removed. So it's just one long string. This means that when you connect to a text file in this format, it all comes in into a single cell. So it's close. Both of these let's get back to excel and connect to the mini fine version. So in the data tab of the ribbon, we go sue, get external data from text against this workout, waiting to fold it. Number four view files a man connect to heads many find because we treating it as a text file. The text import was it appears so in step one. We specify the Limited instead to will say that the delimit er is other without specifying what it is. And just in case, let's say that the text qualifier is none, and then instead, three we can choose. Text is the full amount, then we click on finish before clicking. OK, it's a good idea to have a look at the properties because there's one property which is rather irritation, and that is that every time you refresh the Data York wanted to specify the location of the file. No idea why that's the default, but let's switch it off. And then, as you can see, you can specify the frequency of the refresh, and you can specify to repress the data every time you open the file. The data formats and, of course, won't supply because we're not bringing this in in a tabular format. We're bringing it into a single cell, so it's like, OK, when we click OK again. The entire document is now being brought into a single cell. If I go out to the home tab and speech onward ramp, we can see our many fine XML format. There are several websites available which will create a unified version off your XML. We go into the training folder. I have the Earl of one of them XML many fire. So if you want to experiment with this feature, just visit this u R L Paste in the original XML. So I got many fine and you'll have a unifying version off your file toe work with. So now that we have our XML in a single cell, we can go back to expenses and use Filter XML to navigate our way through it. So let's open the armed men if I inversion so we can have a look at the structure so we can see that we need to navigate from heads to head and then to stop i. D. Last name first name on we using underscore to split words. So let's put the basic formula in equals. Phil Drink Samil. The XML document is in our XML connections. Work feet. When we click on the cell that contains the linked document, you'll notice that it's the name of the link document that gets put in because that name has been applied to the cell that we put our expand statement or was slashed to indicate an absolute path. Heads slash head slash stuff on the school. I d. Close quotation marks. Can the clothes in parenthesis when we enter what we've actually targeted, we'd go back to the XML structure is the staff i d of the first of our heads of department . In other words, out formulas not quite finished because we know hoping in on a particular department or a particular branch. But we'll leave it like that for this video. And then in the next video, we'll finish it off when we talk about X path predicates. But for the moment, let's just copy the formula and paste it in so we can pick up the first name and last name . Since I've said, these three formulas are purely picking up the details of the first person. And if we were to copy it down, we simply get that person's details replicated for every room. So let's just don't do save out changes. And in the next video, we'll discuss the use of expert predicated, which will enable us to complete these three formulas 45. 8.5. Using XPath predicates: as we've seen the Web service function can't be used on local XML files. On the work around, I've suggested, is to use a unified XML file. What if many find your XML isn't really an option. If, for example, the XML file is over written fairly frequently, you'll have to manually updated each time and get a unifying version. So in this video, we'll look at using the new contract function in Excel to enable you to import on ordinary XML file. I non magnified. And then can Katyn ate all the roads that it occupies, so it can then be treated as being inserted into a single cell. So let's go into number six. Then you can cat function and open up expense claims. Let's move across to Excel connections on, then connect to the UNMIN ified XML file that we've got here called Head Start XML. And, as you can see, because it's not many fight, it will take up 363 rows within an Excel worksheet so they can Katyn eight function can't be used because it has a maximum of 255. Paramus is, however, in Excel 2016 there's a new function called Can Cat, and it offers much more powerful. Can cast a nation options. Let's have a look at how it works, so we go across the data, get external data from text. Now we're working our way into Folder six, View a lot files and then import hence dot xml. We important is the limited. The limiter is other and then unspecified XT qualify and none. And we form in the colon past text on before clicking. OK, that's just going to properties and switch off that pesky option problems for final name on refresh. Let's say we want to refresh every time we get in the file, so we've now connected to our XML file. But when we go back to our other worksheet, we won't now be able to use Filter XML because our XML is distributed across 300 rose. So what we need to do is to assemble the XML into a single cell. So let's just merge a whole bunch of cells. I think we need to go all the way down. I'm emergent center, vertically aligned to the top. And then in that cell we use Excel 20 sixteens new can cat function equals Can cat. And what's new is that I can now simply click on column A. Have the function will automatically can. Katyn ate the values in each row, and the fact that there are 363 rose is not a problem. I don't have 363 arguments. I only have one argument so I could put if I wanted to another 254 arguments within the capital option so you can see how much more powerful it becomes than the classic can Katyn eight function that we've had for some years. So when I enter my function and special lead wrap, you'll see that I have my XML file sitting in a single cell. I won't bother repeating the creation off the formulas. What I wanted to show you, basically, is that if the trick of many find your XML isn't really possible, you can still assemble all the lines of your external document into a single cell. Use in that very useful can can't function. But what if you don't have Excel 2016? Unfortunately, you'll have to do it the hard way, but it's still not super difficult. Let's have a look at how it would work. I'm gonna make this column narrower. And then here I'm just gonna put in a column of numbers. We know that there are 363 rows, but let's say we want to go all the way down to 500 in case the XML ends up growing. So I go down to about 500 on. What I can now do is to get Excel to build a really complex formula automatically. So I'm using a series of formulas to build another formula. You'll see what I mean in a moment. So we start with equals and we want to pick up a one. So we put the A in quotes, and then we say ampersand and click on the one which contains one. So that gives us a one. And then we want an M percent. So we say Ampersand quotes, ampersand close, close. When I press enter, you'll see we now have a 1%. In other words, are formula needs to say a one M percent 8 to 10% a three etcetera. So in the second road, we can now say equals the cell above, followed by Okay. Ampersand de to Anderson quotes Empress end. Hopefully you can see what I'm going with this as we copy it down, it's gonna take the cell above and then just add on the reference for that road so I can just double click to copy it down Myth to the bottom on. This is now gonna be my completed formula, so I can just copy that into note pad time equals, paste it in there, lose the trailing and percent, and we've got ourselves a formula, So not a huge amount of work to create. It likened a little of this. Let's just make another one of these big cells. So let's win this a bit. Select a bunch of cells and then Myrdal of these cells left the line top aligned. Hopefully my clipboard is still intact, suddenly placed all of that in and so and would run. And we've got exactly the same result. Very untidy formula. But as you can see, not so much work to create. So if you haven't got Excel 2016 this is the approach you can take. But if you have 2016 and I'm sure that they can cat function is one that you'll be very keen to start using 46. 8.6. The new CONCAT function: as we've seen the Web service function can't be used on local XML files. On the work around, I've suggested, is to use a unified XML file. What if many find your XML isn't really an option. If, for example, the XML file is over written fairly frequently, you'll have to manually updated each time and get a unifying version. So in this video, we'll look at using the new contract function in Excel to enable you to import on ordinary XML file. I non magnified. And then can Katyn ate all the roads that it occupies, so it can then be treated as being inserted into a single cell. So let's go into number six. Then you can cat function and open up expense claims. Let's move across to Excel connections on, then connect to the UNMIN ified XML file that we've got here called Head Start XML. And, as you can see, because it's not many fight, it will take up 363 rows within an Excel worksheet so they can Katyn eight function can't be used because it has a maximum of 255. Paramus is, however, in Excel 2016 there's a new function called Can Cat, and it offers much more powerful. Can cast a nation options. Let's have a look at how it works, so we go across the data, get external data from text. Now we're working our way into Folder six, View a lot files and then import hence dot xml. We important is the limited. The limiter is other and then unspecified XT qualify and none. And we form in the colon past text on before clicking. OK, that's just going to properties and switch off that pesky option problems for final name on refresh. Let's say we want to refresh every time we get in the file, so we've now connected to our XML file. But when we go back to our other worksheet, we won't now be able to use Filter XML because our XML is distributed across 300 rose. So what we need to do is to assemble the XML into a single cell. So let's just merge a whole bunch of cells. I think we need to go all the way down. I'm emergent center, vertically aligned to the top. And then in that cell we use Excel 20 sixteens new can cat function equals Can cat. And what's new is that I can now simply click on column A. Have the function will automatically can. Katyn ate the values in each row, and the fact that there are 363 rose is not a problem. I don't have 363 arguments. I only have one argument so I could put if I wanted to another 254 arguments within the capital option so you can see how much more powerful it becomes than the classic can Katyn eight function that we've had for some years. So when I enter my function and special lead wrap, you'll see that I have my XML file sitting in a single cell. I won't bother repeating the creation off the formulas. What I wanted to show you, basically, is that if the trick of many find your XML isn't really possible, you can still assemble all the lines of your external document into a single cell. Use in that very useful can can't function. But what if you don't have Excel 2016? Unfortunately, you'll have to do it the hard way, but it's still not super difficult. Let's have a look at how it would work. I'm gonna make this column narrower. And then here I'm just gonna put in a column of numbers. We know that there are 363 rows, but let's say we want to go all the way down to 500 in case the XML ends up growing. So I go down to about 500 on. What I can now do is to get Excel to build a really complex formula automatically. So I'm using a series of formulas to build another formula. You'll see what I mean in a moment. So we start with equals and we want to pick up a one. So we put the A in quotes, and then we say ampersand and click on the one which contains one. So that gives us a one. And then we want an M percent. So we say Ampersand quotes, ampersand close, close. When I press enter, you'll see we now have a 1%. In other words, are formula needs to say a one M percent 8 to 10% a three etcetera. So in the second road, we can now say equals the cell above, followed by Okay. Ampersand de to Anderson quotes Empress end. Hopefully you can see what I'm going with this as we copy it down, it's gonna take the cell above and then just add on the reference for that road so I can just double click to copy it down Myth to the bottom on. This is now gonna be my completed formula, so I can just copy that into note pad time equals, paste it in there, lose the trailing and percent, and we've got ourselves a formula, So not a huge amount of work to create. It likened a little of this. Let's just make another one of these big cells. So let's win this a bit. Select a bunch of cells and then Myrdal of these cells left the line top aligned. Hopefully my clipboard is still intact, suddenly placed all of that in and so and would run. And we've got exactly the same result. Very untidy formula. But as you can see, not so much work to create. So if you haven't got Excel 2016 this is the approach you can take. But if you have 2016 and I'm sure that they can cat function is one that you'll be very keen to start using 47. 8.7. Descendant-or-self axis: in all the examples with views over, we've only been working with one axis the child access, which is the default and therefore doesn't need to be specified. In this video. We'll talk about another access that you'll encounter, which is the descendant or self access. So that's going toe hold of five where we finished up our expense claims. Examples. Let's just copy that and paste it into folders seven and then open up the completed file. So here's the formula. We ended up creating, and all I want to show you in this video is how you can avoid having toe. Always use an absolute pass, which, if you remember, is one that starts with a forward slash. And that's fine use in the descendant or self access. So let's look, first of all at the longhand if I deletes forward slash heads forward slash and time Descendant iPhone away hyphens Self Colon Coghlan's That's the longhand way of specifying the descendants of self access. And if I presenter, you'll see I get exactly the same result on what the descendant or self access enables you to do is in this case to find head elements wherever they occur within the document without having to navigate through the XML structure, starting with the rooms element and then working your way down so you'll find Descendant ourself used quite a lot. What you won't find is the longhand that I've just written. I rose it like that just to emphasize that this is the access that we working with. But the shorthand way of writing it is to forward slashes. So whenever you see these two forward slashes, just remember that this is what's going on. Instead of having an absolute path which starts at the document, Root goes to the root element and works its way down. You simply use the descendant or self access to find an element wherever it occurs within a document. So because it is such a useful shorthand, you'll encounter this in tax quite a lot. So hopefully that's just given you more of an idea of what's going on when you see an expert statement containing these two forward slashes. So that completes our initial look at Expo in the next section will move on to look a xsl t on will continue using X path in the context of xsl transformations 48. 9.1. Making the Developer tab visible: excels commands a conveniently arranged in different taps in the Excel ribbon. So we've got all of the common commands in the home tab. Demands for uncertain non work feet, information in the insert tab and so forth. In order to develop user defined functions, you will be used in a language called BB, a visual basic for applications on all the commands relating to the B A could be found in XL's developer Tab. However, when you first install Excel, the develop its have is not visible, so the first thing you'll need to do if you want to start writing user defined functions is to make the Excel development have visible. To do this, go to the file menu and choose options. The different category of options are displayed on the left, and we want the customize ribbon category. Then, on the right, you'll see all of the available riven tabs and you simply clicking the check box next to developer to make it visible. You'll then see you have an extra tab on the right of the ribbon called developer. How many click? You'll reveal the commands related to visual basic 49. 9.2. The Visual Basic Editor: now that we've made our develop it on visible, we're ready to start looking at the environment in which you create your user defined functions. And this is called a visual basic editor in 09 Getting Started With Bebe au D EFs Let's now have not zero to the visual basic editor When you click on the developer, tap the very first command that you'll see on the left. Visual Basic takes you into the visual basic editor, and this is the environment in which you do all your BB a Cody. You'll notice that this environment differs from Excel. It doesn't have the familiar ribbon and ribbon. Tam's has the old school menus, two of ours and floating windows. And, as with Excel 2003 in previous, if you go to the View menu, you'll find a list of all the available tool bars and you'll find a command to hide and show all of the floating windows. Let's just close thes floating when those down. So if you've got any open, just closed them all so we can get an idea of what they call. The most essential of these floating windows is the one called Project Explorer. Without this, you can't get started. So let's get a view into his Project Explorer. The term project in the context of V B. A. Simply means the container or all your VB a code B B A can be implemented in several different ways. For example, you're probably familiar with the concept of macros, which were used for automation. It's also possible to write BB, a code which executes automatically based on actions performed by the user on the way that VB a works is determined by the container inside of which you put your VB a code. And we'll be discussing the containers which applying to use the defined functions in the next video. The second most essential of these floating windows is the properties window. So let's go to you and Properties Window, and we could just resize that so we can see both the Project Explorer on the properties window. The properties window, as the name suggests, allows you to change the properties or attributes of any of the objects that you got in the Project Explorer, and we'll be using it from time to time as we write out code at the moment Let's look at a useful facility provided by the Properties Window. What's come out of the visual? Basic editor. We couldn't do that either. By close individual basic editor. So here on closing the visual basic editor, I haven't closed Excel itself. Let's go back inside country. The second method on the second method is simply to switch back to excel, leaving the visual basic, and it's a window open. You can obviously do this from the task Bob. Simply click on Excel to reveal a windows and then switch to the Excel window. 1/3 method is to press the keyboard shortcut, which is walled 11. And, of course, you don't need to make a special effort to remember that every time you hope her over the visual basic and it's a button, Excel will remind you of the short cut, so I'm gonna press halt at 11. I'm individual basic editor, also 11. Again. I'm back in Excel so you can use any of those methods to switch between the two environments. So let's go back to excel on what I want to show you is a useful feature for hiding work leads, So I got a couple of worksheets here If I right click on the one called Hide Lee Andrews hide as I'm sure you've done in the past that tab is then hidden If I right click and choose a new hide the hidden tab is available and I can then click OK to make it visible again. However, if we go into the visual basic editor and find these two worksheets So here we can see inside Microsoft Excel objects. We have sheet too. And in parentheses we have the interface name the tab name, which I simply called Hide me and then we've got a 2nd 1 called very high bay. So what we conduce you in the properties window is with this sheet selected, we go to the final property as well as Excel Sheet Hidden, which is the equivalent command to the action that I pulled a moment ago. We have Excel sheets very hidden. So if we choose this when we go back to excel, the work she's is hidden. But the difference is that if I now right click, you'll see that unhygienic is great out. So the user hasn't been given a clue that this sheet exists so That's just an example off using the properties window. So for the moment, let's leave it at that. We've got the Project Explorer on, We have the properties window, and in the next video, we'll move on to look at actually writing our first user defined function. 50. 9.3. Writing a UDF in a macro-enabled workbook: having had a look at the environment in which will be working. Let's now move on to write our first user defined function. 709 Getting started with the V A. Let's open up number three and we want the Excel version right in the UDF in the macro enabled workbook the next Senate Sex. As we've seen to write a user defined function in the developer tab, we click on visual Basic, and this takes us into what's told Visual basic editor. We have, ah, Project Explorer Window open on. The first thing that we need to do is to create a container or our user defined function, and the appropriate container is referred to as a model models. They used a store of Mac Rose and user defined functions you can inside a module in two ways. In the menus get to insert singers module, and I think probably guess, if you right click on the projects, you'll see that insert is available in the context menu and even the interest use module. Since the module is a container for code, naturally it has associated with it a code window inside of which you write your code When you create the module, the code window opens automatically, and you can close it and reopen it at any time. Close the code when they use the small X The large axes, obviously, for closing the visual basic editor completely. So when I click here that closes the code window. The module one. The model I have just created anybody double click on Module one again that reopens the code window. When you start writing user defined functions, you may decide to store them in several different modules. So it's a good idea to come up with a name in system so that you know which module contains which function. To change the name of a model that you can probably guess. We use our properties window. Close the properties. When those. I could just remind you that to make it visible, we went to view properties window. So with Model One selected, you'll notice that there is only a single property available, and that's the name of the module. Let's just call it M basics. As with named Ranges, spaces are not allowed in the name of a model and the convention of starting the name with a capital M purely distinguishes it from other objects that you may encounter while you're right in your BB a code. So we now have a module which will act as a container for any user defined functions that we right. Let's now move on to writing the user defined function. The simplest way is to go to inserts and shoes procedure because a function is a type of procedure. Then in the type section we click on function and we leave the scope set to public. And finally we come up with a name for the function. The function that we're going to write in this example is really basic. It simply returns a piece of text. Let's go back into the training folder and you'll see that as well as the Excel file. We haven't identically named text file. If you open that, you'll see the text that we want our user defined function to inset on. The reason for taking this approach might be where you have a standard piece of text that needs to be worded in a specific fashion on which may occasionally need to be updated. So if you insert it by using a user defined function. When you redefine the user defined function, when you have in the workbook, you'll find the new version of that text. So while we're here, that's places on the clipboard. And then let's get back to Excel. And now we can enter a suitable vein, so I'm just going to call it confidential and click OK and that causes Excel to insert the function code. And of course, there's nothing to stop us simply right in that code rather than use in inset function. So the key words are public function and end function. And then we've got the name that we've chosen and parentheses. The parentheses have exactly the same purpose as they do with excels built in functions, their containers for arguments. However, as with some Excel functions such as today and now, notable functions require arguments. Let's go to be the case in our first function. We don't need any arguments, But as with Excel functions, even if you don't need arguments, you still need the parentheses on the final piece of infrastructure that we need to put in place is to define the type of data which are function, will return or produce and In this case, it's clearly a piece of text to specify the data type. You simply type the keywords as when you hit the space bar. Excel gives you a long list of all the different types of data that your function they return on in B B A. The word for text is string. They find type S T or rather str We'll see that string is highlighted in the list and then they can just press the tab key to insert it. So that's the mechanism. But he fine in a function and specifying the type of data it returns. Now we need to specifically say what that data actually consists off. So that's the code that I've currently got on my clipboard. I'm just gonna press the tab key to in dense the line of code and I'm gonna write that is purely for readability. Specify what the function produces. The results of the function used. The key word let in the name of the function and you'll notice that I typed it with lower case. Since I've defined it with another case, C even if I type it with lower case, Excel will always capitalize. It it will follow, the case indicated in the definition. So let the name of my function confidential equal the result of my function. What I want my function to produce. And in this case, because it's text, we put quotation marks, and then we can just copy that long stream because it is a long string. It's a good idea to split it onto several lines for readability, so that when you look at your code, you don't have to constantly scroll left and right to split this line of text. We simply end the string, so I'll end it here. And in fact, that has noticed that the word Andi is missing. This report is confidential and should only be viewed. So that's just a little bit and in spirit here suspect it. We put closing quotes. I m percent what we've encountered many times in our functions and formulas. And then you put an underscore. So space, followed by an underscore, is the signal a split in a line in B B A, and you do that whenever a line is going right off the screen. So now I can just press return but open quotation marks on these two lines are now going to be treated as a single one, So that's an example of the simplest type of user defined function that you can create. It has a name, and whenever it's used, it simply returns a literal value in this case, a string which I, incidentally, have to terminates with a closing quotation mark. Whatever you finished writing code. If all test in it, you can go to the debug menu and truce, compile and acceptable. Then I like the first error that you made, which will prevent the code from running, and you repeat that process until you've got no errors. Obviously, in this example, there's not much room. Ferreira, so we haven't made any errors were now ready to test our function so close of E. B. A. And to find out function, we can simply start to type it. So if I type equals c o N f, you'll see it comes up in the list. And as usual, I don't even have to put the clothes in parenthesis. When I present up comes my text. So in this example, were actually storing the user defined function within the workbook itself. It's not being stored in an external resource. Whenever that's the case, the workbook has to be saved Is a macro enabled workbook So any VB a code that you put inside a workbook requires that the workbook be saved as macro enable. So to do that, we simply go to file save as a man in the savers type section, we choose Excel macro enabled workbook from the drop down and then save. 51. 9.4. Using trusted locations: in the interest of security. Microsoft Excel regards any file in which you placed BB a code as a potential security risk . So, for example, or we're going to fold in nine and then open up the workbook that we used in the last video . 03 Excel automatically displays a security warning and disables the content. Naturally, you can click on enable content and everything works fine. This feature is obviously quite useful, and it's in our best interests. But when you're doing any kind of BB a development, you don't really need tohave files that you've created. Treated with any suspicion, A good way of achieving the right balance is toe. Add the folders in which you're doing development to your trusted locations. As always, everything related to the B B A is in the developer town and in the code section. On the left, you'll find the macro security button, and this command takes you into the trust center settings on these settings for part of Excel options. So we cancel. We can get to the same location simply by going to file options. Then, on the left, we click on trust center and on the right you'll see a button marked trust center settings , so clicking on that button is exactly the same as clicking on the macro security button in the developer tab. While it's possible you can see to set global settings, it's more convenient to use trusted locations because this allows you to be more selective . So on the left we choose trusted locations, and then we add any older locations in which we do in VB a development to our trusted locations. It's on the right. You'll see a button marked at a new location, and this enables you to browse to any location that contains the B A that your develop it that's going to the desktop. And here I've got the training folder. I opened the outer folder and add that to my trusted locations. And then, very importantly, before clicking OK, I also activate the option. Sub folds of this location are also trusted. Then I click OK, and from now on, any code files within that folder that I open won't trigger excels normal security warning message. I can click. OK, okay, again close without saving. And then when I reopened the file because the file is in my trusted locations, it doesn't trigger the security warning message 52. 9.5. Writing a UDF in the Personal Workbook: created a user defined function in a particular workbook means that that function will only be available within that workbook. In this video, we'll look at how you store functions in Microsoft's personal workbook, making them university available. 09 even started. That's no open up the final filed 05 and you'll notice that this workbook has the file extension XLs sex. There's not a macro enabled workbook, So what we're going to do is to create a macro which lives in excels personal workbook. Whenever you create a user defined function in excels personal macro workbook, that function is available for use in any workbook. And you don't have to save your workbook in the special macro enabled Hornet. If all you started writing B B A, the personal workbook will be visible, so we're going to developer and click on Visual Basic Toe launched a visual basic editor. You'll see that the only project that we have is full, the file that I'm just opened. So what we want to do is to write a function, but we don't want to store it in this particular work. We want to story in excels own macro work, so it's always available. So a simple trick for making the personal macro workbook available is to use the macro recorder. If I simply click on record Macro, I don't actually want to record a macro. I'm just doing this to make the personal micro workbook available. So the key thing is here in this drop down stall the macro in, make sure we choose personal macro workbook and when we click OK, Excel creates the macro stores it in the personal macro workbook, thereby making the personal macro work with visible. So without doing anything, we can now just click on Stop recorded. When we go back to the visual basic editor, we now have an extra project or the personal bankers workbook. And inside it we have a models folder and inside that the model that has been generated automatically for us to store the macro that we started. Obviously, we don't need this macro so we can just delete it. But that simple trick basically enables you to make the personal macron workbook available so you can start using it for your user defined functions for convenience. We're not gonna take this approach during this course will just put out Macron's in individual Worth books, which is the least efficient way of working. But for training purposes, it's fine. When you come to write your own user defined functions, you'll probably find that this is a good way of having more user defined functions always available. So let's just rename the module. I'm gonna call it M U T X and let's write our second user defined function. So for this example, we're going to have a function which does a simple conversion. That missile give us a chance to work with function arguments. So in the training folder we open up 05 right in the UDF in the personal workbook dot txt. We have a simple conversion formula on. We want to encapsulate that into a user defined function. So let's just copulate formula and we're now going to write a function which does that conversion that you create the function we can use insert procedure. Specify that type is being a function the scope of defaults to public, which is what we want, and this function will calculate a person's body mass index B m I. So let's call it be at mine or to be more accurate. Let's say B M I under school Imperial, there's a function, but this time we do need to specify arguments. So let's begin by pasted in the generic formula on will convert this into a comment. And we do that by putting an apostrophe at the start of the line, and their function is to enable you to explain the lines of code that you're writing. This is both for the benefit of other people. Read in your code and for yourself when you come back to the code and it's not this familiar is when you initially wrote it, so we can see from the formula that the two bits of information that we need are the weight in pounds on the height in inches. So those are the two arguments that we need to specify within the parentheses, so we can simply call them wait. And if you remember from the previous video, we then specify the type of data that this parameter will our human will accept. We do that with the keyword, as so this is going to be a decimal number, and the data type that will use for that is referred to a single coma, and then we have the heights if we can. Also specify is a single when the final part of the definition is the data type that's returned by our function, which will also be single. So now we can specify the results produced by the function. I'll just press tab to invent the line and to specify the results. Muse the keyword Let and we use the name of the function equal the necessary calculation, and we can just follow the generic formula. So open parentheses wait, divided by open parentheses. Height multiplied by heights, close parentheses twice multiplied by 703 That's all we need to do for this simple function . So we're input in two arguments, and then we use in both of those arguments in a calculation, especially find that our function returns the results off that calculation. So before testing, we go to debug Andrews. Compile. No, there is appear. So we good to go ahead and test one thing that you're not able to do when you put functions in excels personal Macron workbook is to simply start typing the function and have excel. Recognize it. So if I type for example, equals B. M I. You'll see there's no intelligence. We'll see that there's no water in completion. So what we need to do is to use the insert function command, and this is available just on the left of the formula bar in certain country. It's also available in the home tab in the form of his tab. So whichever is convenient and this brings up the insert function dialogue. We look at our categories as soon as you start creating user defined functions, an extra categories automatically created, called user defined. And within that you'll find the function that we've just written. And you also noticed the syntax that's required. Whenever you store a function in the personal macro workbook, you have to actually precede it with the name of the personal macro workbook and an exclamation month when we click. OK, because we've used the Insert Function command. We get this useful wizard so you can see our two parameters weight and height on using commanders click on the cell containing the weight and the cell containing the height, and our function returns the correct results. Speaking land. Just copy that, holding it down like any other function. So although the syntax used to reference the function is a pit long winded, it's still useful in that you can immediately distinguish between excels, functions and your own user defined functions. They makes them stand out. Basically, if we now quit excel, there are two things which we can save on which, Excel will ask is whether we want to say the changes to this workbook, but also the changes to the personal macro workbook. So let's go ahead and quit Excel. Do you want to save changes to this file? Yes, we do. But then a second message appears. You want to save the changes you made to the personal macro workbook, And of course, we would need to click on Save if we want to preserve those changes. So if you want to experiment with that feature, go ahead and click on Save and then the macro that you created in the personal macro workbook will still be available. But for training purposes, it's more convenient to store our Mac rose in individual workbooks, so that's what we'll be doing. So I'll just click on cancel for the moment 53. 10.1. VBA syntax: we've now reached section 10 of the course, so let's go into this section 10 older on what would be doing in this section is to look more closely at the key aspects of right in the B A code. Let's begin by discussing the first thing that you're gonna need to get to grips with, which is B B A syntax. So let's open up 01 BBs in tax, which is a Pdf file. And if lightly your PdF so open with Adobe Acrobat, you can just catch a view Bull Street mode. Well, press control L. BB is a fully fledged programming language, and it has a series of features which are common to all programming languages. This table is the key syntactical elements of B B A. And as we encounter these elements in our code, I'll keep referring back to this chart. I'll recommend that you do is saying each line of code that you write in B B A will fall into one of these categories. Now we've obviously just stomach did so we haven't really done very much, but let's look at the features that we have encountered so far. Festival. We've encountered procedure declaration that's just come out of full screen mode for a moment by pressing Escape and go back into the train in Boulder. Hold on nine and 03 We wrote our first user defined function that we're going to The developments happened. Click on visual Basic. This basically is a procedure declaration, and it remember we went insert procedure and then chose the type of procedure which was function. So the two lines of code relating to procedure declaration that we've encountered and which , applying to the creation of user defined functions, this line, public function, name of function, her embassies containing optional arguments and then a declaration of the type of value produced by the function using the ask he were. And then the other line that relates to procedure Declaration is, of course, the end function, which indicates the termination of that particular function. Because, of course, there's nothing to stop you put in other macros and other functions within this same model . We also encountered comments, comments or simply lines of code, which you insert a clarification both for your own benefits and for the benefit of anyone else looking at your code as well as placing comments on their own line. You can also place a comment at the end of the line. So here, for example, I can just hit the space bar. But in apostrophe, which remember, is the signal for a comment. And then I can simply say procedure declaration. When I move away from the line, Excel will change the color of the comment, which is quite useful. It makes them stand out another nice feature, by the ways, when you've got toe a lot of code, and when you're debugging when something's not working, you can simply highlight a line of code. If you remember, these two lines are treated as one line because they got the space. Underscore the continuation character At the end of the first line, I can highlight any line or lines of code on. Then, in the editing toolbar, which is here, I just remind you, the old school method. If the editing toolbar weren't visible, just right click into edit to make it visible. Well, that's a view to bars on Giuseppe from there. So on the Edit toolbar has this feature called a comment look. So if I highlight some lines of code and click on comment block that effectively disables those lines of code so I can focus on something else. And then when I want them back again, obviously next door has the uncommon command. The other thing we've seen is assignment off data value, and that's indicated by the keyword less. As I've said, Let is actually optional so most people admitted. But it's useful when you'll learn inmediato. Always use it so that you remind yourself off what's taken place on that particular line of code. So the assignment of a data value means that on objects, which is essentially a container for ordinary data which means text or string base numbers , these are ordinary pieces of data as opposed to excel objects such as worksheets, cell ranges, etcetera. So the ASIC nation of a data value involves place in the appropriate type of value into that container. So in this case, the container is the function itself. But the function is essentially a string simply by sane after the function name as a string . We're saying that the sole purpose in life of this function is to produce a piece of text, a string. So what we did on this line is a signing the string, which is on the right hand side of the equal sign to the object or container, which is on the left of the equal side. And that's the way that the assignments operation always works. Whatever is on the right of the hole is being placed inside whatever is on the left of the equal sign. So let's put one more comment into that effect. Apostrophe assignment or days of value and then in print diseases weaken, say string. So the key thing to remember is what I mean by data is the kind of thing that can actually go inside and excel Cell a date number piece of text on the big contrast that you've got to get used to when your programming in B B. A is the distinction between these ordinary pieces of data on references to excel objects. So whenever you're working with ordinary pieces of data used, the key word let and as we'll see later, when you come to work with references to excel objects such as worksheets and workbooks, you use the opposite keyword set. That's a far as we've got without look at B B A syntax. We've just started to scratch the surface, but as I said, I will keep coming back to this chart so that we can just keep track off the different syntactical elements that you need to get used to. 54. 10.2. Excel object model: as you write your BB a functions you'll need to manipulate different aspects of Excel on Excel elements. The Excel Object model is the term or the hierarchy that Microsoft have created, which represents Excel and all things excel. So key aspect of this Intacs that you'll need to learn is how to reference different parts of the Microsoft Excel structure. This diagram is far from complete. It represents only the key aspects. The key elements within the Excel hierarchy on also bear in mind that there are certain elements which you can't access when you're right in user defined functions, these elements can only be automated, so when you write macros, you can refer to them. You can't, for example, right. A user defined function, which uses excels speech capability to talk to the user. That can only be done by writing a macro as we manipulate Excel elements or come back to this diagram and just point out different aspects of the hierarchy that were actually manipulated. So in order to get used to the syntax that you use when you work with the Excel object model, let's just briefly discuss four of the elements in this diagram complication Workbooks This workbook Onda workbook. As you can imagine, application represents Microsoft Excel itself. It's whenever you're working with settings, which are essentially excel settings rather than attributes pertaining to a particular workbook or a particular work seat. We'll be working with the application object. Let's have a look at some of the syntax that applies to the application object. Let's go to the developer tab on visual. Basic visual. Basic Editor includes a very detailed reference to excel, maybe a. But you can access my feelings of you in choosing Object browser. And here you can find a list of all the components that make up the Excel object model in the top left. Prince has all libraries. We can restrict our list into excel and let's find application. But I highlight application the members off the application object displayed on the right. That basically means all the other elements within the Excel object model that apart off the application object and there are two key types of elements. But first, let's have the green icon our methods, which are essentially functions, and for the most part, when you're writing user defined functions, you don't really access them these operations. So if you're right in Mac Rose, you want to close files, open files, print documents and so forth. These are all methods or actions that you can perform on these objects. The type of member that you focus on when you're writing user defined functions is really properties. These are the attributes off the elements. So, for example, if we scroll down, we have calculation. That's the current set in within Excel As to whether calculation is manual or automatic. When I click on calculation on the bottom of the screen, you'll see a brief description is displayed. If I want a more detailed description, I just click on the help button. And here you're given a description of the calculate property, handsome examples of its usage and at the bottom of the screen. We have a brief description on also useful links if we click on the link that says Excel calculation were taken to a list of all the permissible values. So this is a constant. So we used to represent the settings that air access to the interface, automatic manual or semi automatic, another tool that's quite useful when you're experimenting with the Excel object model is the immediate window. To get to this, you get a new choose immediate window on in this window, you can type a line of code and their two styles in line that you can write. One is where you simply want to get an answer to a question. So, for example, we might want to check What is the current calculation set in within Microsoft Excel. But that style of line You start with a question mark, and then you just write a line of code and presente so we would write application, not calculation. Now, when we present, we get a constant and if I close this window for a moment, but I click on these. If you look at the bottom of the screen, you'll see the actual constant that's represented by each of these. So the minus 4105 If we can see represents automatic, it would move onto wet books. Workbooks simply represents the collection of files, which you currently have open in Excel. As soon as you close a file its top speed in the workbooks collection on when you open a file. The workbooks collection increases by one so if we type application, don't work. Bookstop count. When I press enter, I have to the file that was created automatically for me when I open Excel and the personal workbook, which is currently open. And that kind of says one, if I want to reference a particular workbook in parentheses after the word workbooks, I can either put the name of the workbook or it's positional number. So the personal workbook, because it's integral to excel if it's open, it will always have a number of one say bye, puts one in parentheses and then use the property. Don't name. When I press enter, the name of the personal work is displayed. So when we reference a workbook in this way by place in either a name or a number in the parentheses, we are referencing a workbook object. So to get to a workbook object, you dive into the workbooks collection and pick out a single one that gives you the workbook object as well as reference in individual workbooks. You can also use application, not this workbook on what this means is the workbook that contains your BB, a code so here, instead of access in a particular workbook. Weaken, say dot this workbook Full name on. When we press enter, we can see it's called Book one, which is the default name of the workbook that Excel creates when you first want to the program and will be used in this workbook very shortly in our code. So those are the two tools in Excel that you can use to just navigate on. Learn about the Excel object model. Let's just close these down for the moment. And as I said, we'll come back to this diagram as we encounter different aspects of the Excel object model . 55. 10.3. VBA object model: as well as the Excel object. Model B B A itself provides a lot of syntax. Buyer The BB A object model, The V B A object model consists of a series of useful functions divided into different classifications. A lot of these functions relate to the manipulation of data, and they directly equivalent to some of excels. Work three functions. So, for example, most of the functions in the strings classification are equivalent to excel text functions , and similarly, we have data time, miss information. So these are all useful things that you conduce to manipulate data, many of which are equivalent to the familiar territory of using Excel functions. So let's take just one example off using a BB A function. Let's go back into the previous folder, number nine, and open up the first user defined function that we rose. And if you remember this, simply insert a piece of text. Let's say that we prefer to split this text onto two lines so it's displayed in a large cell. Let's make this a larger, and the cell has wrapped text activated. So let's say we want copyright City smart limited, then a return, and the rest of the statement. So that's going to developer on visual basic on before we actually modify the function. Let me just point out that the Object browser, which we saw earlier, can also be used to check BB a syntax. So let's go back into view object browser and then choose the V B A library. So here we've got the different classes that we saw earlier on the one we're going to use his constants. Now we've got the VCR, carrot return, BBC RLF carriage return line feed on VB LF line feed. And obviously, in the context of a cell, there's no difference. If you exported the text and opened it in a word processor, then carried return line feed will always to give you a paragraph. Where's the other two will give you a line break. So all we want to do here is to split this text, so I'm gonna speak it now onto three lines. So let's terminate the string here, put an ampersand space on the school to split the line, create a new line and then reopen the quotation marks. So although we've got three physical lines, we have one logical line of code on what we now want to do is after this statement to insert a line feed so we can just click here until I m percent. And when you type in BB A functions, a useful thing is to type the function longhand. So it stands out, becomes very conspicuous whenever you look at it. You know that V be a function has been used at that point. So we would type B B a dots and then the intel a sense this auto completion pops up and gives us a lot of different baby A functions if you remember when looking inside the constants class and when we took the second dot we get all the different options. So I'm just gonna use V b l f A line feed, and that's it. So, having updated out function, if I just add in the cell and you'll see our line feed character kicks in and splits the string onto two lives. So he now looked at the three different parts of the V B. A syntax puzzle. We have BB a syntax structures such as if statements, loops, etcetera. Then we have the Excel object mobile, and then we have the V b a object model. So as we go through the course, I'll just keep coming back to those three themes, so hopefully you'll have a good idea of what's being contributed by each of these three aspects. 56. 10.4. Displaying the user name: Let's get some more practice on writing user defined functions. So we're working in number 10. Unless in open up number four displaying the user name in this example, we want to create a kind of welcome page, similar to what you see when you're logged in on a particular website. We want to display and sell a one a message to the effect. Use the name logged in as log in name, for example. John Smith logged in as Jay Don't Smith on For this. We're going to use two pieces of information. The Excel Log in name on the windows, looking they to get the Excel log in name manually. We can goes a file options and in the general category, the first category. Whatever entry is in, user name becomes your Excel user name in terms of syntax. Anything Excel related is part of the Excel Object model and anything that refers to excel itself. It's part of the application object, so we know it's going to be application docked something on. There are no prizes for guessing that it's actually application. Don't use the name to pick up the windows log in name. We use the V B, a object model and in the interaction class we can use in Vyron, which obviously sure for environment. This provides a number of different insights into the user system and environment. User name will give us the windows Look on me. That's right. Our function in developer. We've taken visual basic to bring up the visual basic editor. And if, like me, your personal workbook is visible, just ignore it because we'll be right in this function in the workbook itself. So it was right. Click on the workbook and choose insert module. And then let's bring up the properties window View Properties Window and renamed the new model and then call it and use that interaction bends right. The function. Obviously we've got a click in the code window. Man inserts procedure, time function name that's called it. Use a greeting, but we see no space is allowed. That's the Selivanov function. It doesn't need any parameters, but we do need to specify the type of data that the function will produce, and this is going to be string, so we put as string after the parentheses. Now let's look at another feature of V B a and a programming in general, which is the use of variables. Use variables wherever you want to capture and track a piece of data, or create a reference to an Excel object, which you can then manipulated in your will. So in this function we want to capture the person's Excel user name and their log on name into separate variables. The variables are simply named areas of memory, which store either data well references to excel objects. So in terms of syntax, we're now going to discuss variable declaration to declare a variable used the key word dim , which is short that I mentioned. You then create a name for the variable, which describes its use. Additionally, it's a good idea to use a prefix to the variable name, which indicates the type of data that it will contain. If we go back into excel by closing the visual basic editor, we'll see. How about another work seat here called prefixes? This gives examples of prefixes that you can use whenever you're crazy, variable to indicate the type of data that it contains, and this applies both to data and to excel objects. So if you're creating a reference to an Excel object you can. All that references are variable, and again it's useful just to use the prefix. So these prefixes that I will be using, but the main thing is to be consistent, and that's particularly useful if you're working this part of the team. So we're gonna create to string variables because we've got two pieces of text the Excel user name on the windows local name. So both of these will be str variables so dim as in dimension and let's have str excel name . Obviously, as you can anticipate, those spaces are allowed in a variable name and then we used as to specify the data type. So this is obviously as string a man dim str loving name also a string. So that's variable declaration. The next thing is to populate the variables to put something inside them, and this is where we can see why they call variables. We, as programmers don't know what they will contain. We simply know where the data is going to be captured from, and if this function is used in 10 different locations on 10 different machines, it could produce 10 different results. So in terms of syntax. Populating the data variable comes under the classification off assignment of data value. Your place in a piece of data be is a string a date or number into a data container whenever that's the case, As we've said, you used the key word. Let let my container equals my data. So let's my variable, which is str excel name bqool my string value and we're grabbing the string value from Excel. The key word Frank's cell as an object is application knows on. Then we make our best guess, and obviously the best guess we use the name is user name. Then we do the same for the windows. Log on Let's str log in name equal and this time we're digging insight. BB A so b b A. Don't interaction don't environment on the syntax that you need to use is to put parentheses and then a quoted reference so again use the name. But this time it has to go in as a string. Having populated those two variables we can now compose are simple message, and that message will be the output of the function itself. So again we're signing a data value, but this time we're doing it to the function itself. So let because its data user greets in equal STL. Excel name. Empress End Colon space loved in as a space close close. And then we can Katyn ate the str long in name. And if you want a full stop after that and percent full stop, and that's our function. Since we broach the topic off variables, this is a good point to explain. Option explicit. You probably noticed that every time we create a module when the code window opens, it has the words option explicit and what this means. Basically, let's just get rid of this unwanted in audio way. Need to expose it. No, we don't know what the option explicit does basically is. It requires you to declare your variables. This is how he declared variables that this, strictly speaking, is optional. If you use option explicit at the top of your model instead of being optional, variable declaration becomes obligatory. So if I were to take out option explicit if you remember, you can take a line out simply by comments in the line out. So click on comment. Look, that puts an apostrophe at the start of the line, turning it into a comment, so it's no longer functional. And now let's say that in type in the let's statements, I made an error, so I just did a typo, I m A instead of a M, and I didn't notice. If I now go to debug in twos, compile, you'll see that the code compiles with no problems. But of course it's going to be wrong, because Excel name hasn't been populated. Another non existent variable. An accidental variable, if you like, has been populated. When I don't put my greeting, I'll get nothing at that point. That's now reactivate option explicit by Uncommon Tin it. Now let's try debug, compile and you'll see very usefully. It picks something the error or me so I can immediately see. Oh, that's a typo on, then I can just correct it. The automatic inclusion of Option Express it is controlled by going to tools options and ensuring that this check box is activated require variable declaration, so if this is not activated, often explicit will not automatically be inserted when you do a new module. But there's nothing to stop you from type in the words anyway. OK, so that's our function completed. Let's now check that it works. So we wanted in this cell. So they called it. Use a greeting equals use a greet in. No arguments required and let me enter Excel named Gamble Log in name Train in seven. 57. 10.5. Displaying workbook properties: In the last video, we focused on the application object, which represents Excel itself. In this video look of working with a couple of properties of the workbook object. So we working in Folder 10 KBB A concepts. It's no open 05 displaying work but properties. In this example. We have a workbook info, work feet, and this might be used where a company has a policy off, always filling in document properties. Let me just remind you where document properties live, file info and then I'm right. We have properties and click here we can get into the balance properties and that you can see I fell down most of the categories. So when we opened the workbook, we want the workbook info tab to display all of those document properties, and we also want to display the entire path to where this particular work look insane. It's just a way of allowing to distinguish this workbook from others with a civil, and they so you can just remind yourself of the physical location or this particular document. So let's write down code. First, we'll need a module and I put in the module in the workbook as opposed to the personal workbook. Let's rename it workbook info. So Emma workbook info. Then we click in the code window to activate it and goes inside procedure all the function time, so we'll need to functions. The 1st 1 displays the path, so we'll call that one workbook past. We don't need any arguments that nothing goes inside the parentheses, but we always need to specify the type of data produced by the function. We do that with the key, but as And of course, the path is a string, and then we can write one line function. So one line function should always start with the keyword. Let because we're assigning a value to abate a container. In this case, the workbook parents function. So let's work but pomace equal. Then we've got to find the syntax for the past of the workbook that contains this function . So if you remember, we've got a special way of saying, but workbook that contains this function and that is application, not this workbook. And then it's gonna be dot something, and in fact, there are three properties relating to the name and location off a workbook. First of all, we simply have not name. And if we simply copy that into our new best friend, the immediate window you remember you type a question mark and then he can execute any statements because you can see name is purely the name of the workbook. It doesn't give us any clue as to where the work is saved. The second property is dot path and it was roll over. You can see that that is folded 10 inside of which this file is actually saved. So that's two parts of the puzzle. We know where it saved and we know what it's called. And the third property combines the two. And that is Stop, Full name, right, of course, is the one that we want. You'll see that after the folder, it actually continues and gives us the full power. So that's the property we need here, Dr. Full name. And that's our function. We can press that it hasn't got any errors. I'm leaving. Then go straight ahead and use. It equals work, but path enter. Now let's do the same for our document properties, Incent procedure, time name, document properties. And this time we will need an argument. The text in column A will be the argument that determines which document property is displayed in column B. A logical name for the argument would be property, and it cools its a string. The function itself also returns a string, and again we can get away with a one lying function. So let's name of function equal application, not this workbook on the property we need is called Got Built in document properties as opposed to custom document properties and then in parentheses, we specify which one. And of course, the mechanism that we're using is to use the parameter, the argument as the property. So we simply put the name of the argument inside the parentheses, and then whatever we pick up in column A will determine which document property is displayed on that road. So it compiles okay on to use it. We've got one further thing to Ryan out. We have the text, we have the name of the property, but we also have a colon, so we'll need to get rid of the colon before whatever is in Colin A can be used as the argument for our function, but we know how to do that we use the substitute Gulshan. So this is our first example or combining one of our user defined functions with a built in excel function. So we invest in the substitute function. Inside our music defying function. So equals document properties, or pensar and then we're nest in substitutes. The text is a to former all text is colon coma. New text is nothing. In other words, quotes, quotes, close parentheses, substitute old and and the closing Prentice's or our Document Properties function. It picks up out cycle army and then copy down, and it picks up each of the other document properties. 58. 10.6. Displaying worksheet properties: Let's continue to work our way into the Excel object model by looking at the work seat. Object Number 10. It's no open 06 displaying worksheet properties in this model. We want to display some work read stance. So first of all, we want to display the total number of worksheets currently in the workbook. And then we want to put some information on each of those sheets. So the first thing is, we want to conditionally display that information, starting with the seat number. So the way upset this up is that we have one as the first treat position because you can't delete Aled the seats in a workbook. You have to have one work street there and then below that. We've got a simple Excel formula, which checks to see whether the cell above plus one is less than all report to the value currently in a to the number of worksheets. If it is, then we display the cell above, plus ones in this case, we would display it to, but if it's not, we display nothing. And then I just wrapped that insight and if error statements, so you can think there is an error. We display nothing. So if I just type in 12 in here, you'll see that the 12 numbers now appear and I've only gone down as Faras 15. Obviously, we could just keep dragging this formula down, but 15 fits nicely on my screen, so that's the way that set up. We now want to create three user defined functions to complete the model. So here we want a function, which will calculate the number of sheets currently in the workbook. Here We want to function, which will pick up the sheet name based on the position name of seat one, name a fleet to and so forth. And finally, we want to display the used range, so they used range of this worksheet. As you can see, it always starts from a one on. Then it stresses its faras column C and Rowe 19 So the use range will be a one colon C 19. So we need to write three functions. Let's go into developer visual basic, and we insert a module in this workbook. Let's rename the module worksheet stats. So I m work, read stats there, and then it's like in the code window to activate it and go to insert procedure, procedure, type function, and it's called the function worksheet count. It won't require any input, but we have to specify its output using the key what eyes? And it's gonna output a whole number on the standard data type in B B A for whole number is long, as in long indigent. Another command that you'll find useful when you're writing you to define function is application dot volatile. And what this means is that your work seat gumption will recalculate whenever any cell within the workbook is calculated. And this is useful where you have a worksheet function that's going to go into several different cells. So in our model, well, obviously want all of these cells to update. If someone, for example, deletes a worksheet, so I use an application not volatile. Within our function, we can make our functions as interactive as possible. So that's the first line Will could application dot Volatile is a method rather than a property. If we just delete it and put it back in again, you'll see that when I type it, it has that green icon next to it, which indicates that it's a method. The method is basically a function just like an Excel worksheet function on. As with Excel functions, most methods take parameters or arguments to specify the argument for a function in V B. A. You put a space excelled in obligingly displays the names of all the arguments which that function takes. In this case, as with excels built in worksheet functions, we can see that the parameter has square brackets around it, and this means that it's emotional. However, let's put it in so I can show you the syntax that you use when you're specifying the arguments or a function. Best practice is to put the name of the argument so volatile is we can see and then put colon equals and the value that applies to that argument. So this is clearly a 1,000,000,000 value, and we want to set it to truth. And, as you can probably guess, true is the default. So if we were to admit it, we get the same results. That's just to demonstrate how we write arguments for functions or methods as they call in League B A. And now let's specify the return value for our function so as we've seen before. If the function returns data which this one does, it returns an insurgent. We used the key word let in the name of the function equal. And then we specify a calculation will statement which returns the correct type of data. So here we want to find out the number of sheets in this workbook. In other words, the workbook that contains this function. So we use application. No, this workbook don't work seats and then don't count. So we've drawn into the hierarchy, starting with excellent soul down to this workbook, looking at the worksheets collection inside it. That's all the work treats it contains on menus in the Count property to find out how many there are. And the count will obviously return an indigent a whole number hence which specified that this worksheet returns a long interview. Let's just test it so we can just type equal work leads count? Yes, and so And we have one. So so far, so good. Let's write our next function. Now we want the name off our worksheet against that. Another function procedure, Brooksie name on. The key difference here is that we do need an input. We need to know the number, the position or that particular workbook that we want the name on little cool, that prime minister position. And, of course, it's an integer. The worksheet name, however, is a string, so the function returns a string again. We'll use application of all the time, and I'm using controlled drag to copy that line into the new function. Let's now take this opportunity to put a reference to a work feet inside a variable. This is something we haven't had to do before, so let's declare a variable for holding a worksheet. As always, it's dim. And then I'm going to use the prefix wks for worksheet, and I'll just call it W cast targets as well. She's to put a reference inside wks worksheet. We use scent in contrast to let so let container equal is used for making a data ass IG Nation. You're what incident data into a data container. What we doing here? Within a reference to an excel object. So that's why we use set as opposed to let so said wks target equal to and then we're drilling down inside application. Not this workbook don't work feats and to specify which one. We just use parentheses and inside the parentheses, we can either specify a name or a number. And of course, our function has asked for a number, which is the position off the worksheet. So we can use that as our index inside the parentheses. So we can now put our return statements what we want to let worksheets name equal. And of course, it is a let because works. The name is a string. So let's work seat name equal wks targets don't name, and then let's test equals work seat name. And this time we need to specify a parameter. A useful thing you can do, which we've done a couple of times, is, of course, to insert function. And the benefit of that is that your prompted. If there is an argument or several arguments required, you're always planted as to what they are. So we just click on the cell that contains the position, which is a five, and then we get the name off work sleep one, which is, of course, the website were already in used range is gonna be a virtually identical. So I'm gonna copy all of this and use that as my starting point. Change the name of the function two news range. And then, of course, we're returning used range as opposed to worksheet name. And then the final thing is, the property that we're home in in on is the use range. Don't use range, but used range is a range. It's not a string, so you can't just put a range into a cell in Excel. It's not textual, so we've got to drill down inside the used range on Find the address on. As you can see, this is a property, and, of course, it's just a string. So let's test equals used range. Um, work feeds one treatment pickup. They fine, and that gives us our address. Now, of course, if we copy this town before we've actually got a position, we'll end up with errors. If I just copy it down ones, you'll see because there is no work. She, too. We get that value era so we can just do what we've done here and wrap out formula inside and if error statements. So, yeah, after the equal sign, we just put if error, Then, after the original formula we put comma, double quotes, double quotes to indicate blank on that would do the same thing here that will close little quotes. So now it's safe to company this all the way down on. Because all these cells are filled with formula, I can just double quick. So without the affair, we would be getting error values in here. What we've done with the if era is to replace them with the non string. So let's introduce some more work feats. If we get back into the training boulder, I've got some extra sheets. 06 extra streets. Let's move them all across something. Hold down the shift key. The 1st 1 is ready selected. I hold down shift, click on the last that selects all of them. And then I can just right click Movil copy. Choose the name of my other work ST 06 on. Move them to the end in They will go there when I get back to my worksheet stance. Terrible. My new seats. These are the names of the feats, and then I can check the used range of each one 59. 11.1. Using IF statements: Let's now move on to look at two types of VB, a statement which are essential in order to land more complexity to your VB a functions condition. ALS Looping folder 11. Using conditional czar Looping, let's open the first example using if statements adding conditional logic to your excel. Formless can often seem quite complex because, of course, in a formula, everything has to be a function. But in BB eight, we don't have this restriction, so you'll often find that right in logical structures is much easier in V A. Than it is in Excel or analysts. In this example, we want to create a V be a function which will display the version of Excel that's being used. Let's say, for example, we're tracking printing problems and finding out whether certain versions of Excel are less compatible without princes. So this message, which is currently static we want to make dynamic and instead of heart code in the version , we want the version automatically picked up and displayed at the end of this message. So let's rise our function, go to developer like visual basic, and then let's insert a module in this workbook, as opposed to the personal macro workbook. Let's go straight into our function. So insert procedure procedure time function on Let's call it Excel version. Since the information being used by the function will come from the system, we don't need any arguments specified. It's with parentheses. However, we do need to specify the type of data reviews by the ocean itself. And this, of course, will be a string confirming the version of Excel being used. So after the closing parenthesis we put as string the two pieces of information that were interested in are the platform Windows or Mac and the Excel version, and we can find out both of those by looking at the properties of the application object. So we dip into the object, browse a few object browser on look inside the application object. Here we have operating system, which is you can see is a string, and then we have version, which, although it appears to be a number, is actually a string. So let's create a couple of variables to keep track of this information. You First of all, let's create str version as string, and that will hold the actual text that we intend to display to the user confirming the version of Excel because the version returned by application dot version is inherently numeric will be useful to convert it into a proper number. So let's all said them l m G version as long, I just remind you, long, short or long integer. And then finally, let's create our 1st 1,000,000,000 variable on will use this to keep track of whether the user is on a Windows machine, So call it him bln win as Julian. And now let's construct our if statement. Let's begin by looking at the generic structure oven. Xlv a. If statement we have the key would. If we have a logical test, let's call it test one and then we have the key. But then then we have the statements if test one is true and then we contest for alternative possibilities. But this time we use else if and then we have statements which will kick in if test to is true on. We continue in this way with us many tests as we need when we finished all the test. We can then have a cattle statement, which will only apply if all the tests have been false and to do that we use else and then to terminate the if statement we used end. If so, let's begin our testing by checking for all the Windows versions of Excel. So it would be a good idea to populate our bln wind variable before we do the testing. So we can say let bln win bqool. And then, rather than testing to see whether application operating system is equal to a certain string, it's more useful to test whether it contains the word windows. And we can do that by using the in string function, which is supplied by the V B. A object model. So we start with B B A and then we drill down into the strings close on. We find in string. When we talked, you have in parenthesis. Excel reminds us of all the arguments that we need and best practice is to supply the name of the argument followed by colon equals bullet by the value. So start specifies the character from which you want to start searching, which in our case, is one the first character coma space underscore so we can end the line. I mean, let's just align. The second argument under the first on the second argument we need is String one Colon equals on This is the text string that you're evaluated. And of course, in our case it's application got operating system comma under school on the final argument that we need extreme to, which is the string that we're certain for within String one. And that, of course, is the word windows and then the closing to emphasise to end the in string function. Um, in the second thing we need to evaluate is the version. So let's use LNG version for that. Let LNG version equal, and what we need to do here is to convert a string value into a long indigent and again, v B A has a function that does that forest. So this time it's vb a got conversion and then we won't see l injury as in converted alone , and we need a single argument, which is called expression. The expression come on equals and then we simply use application. No version. So now we have the two bits of information that we need to evaluate. We can specify out tests, so let's go back as far as 2010 Excel 2010 was version 14. So test will be if LNG version equals 14 and the Ln win People's Truth on our statement is true will be let str version Equal Excel for Windows 2010 and then we can copy this test, just paste it here and make the necessary changes. So let's meet up. Version 1015 was Excel for Windows 2013 on. Then let's just do one more version 16. If Yellen, when it's true, is Excel for Windows 2016 then let's move on to the Mac versions. So if the version is 14 but bln win is false, then the user is on Mac 2011 on, then let's copy that. And finally, if the version is 15 on bln Winnie's Falls, for some reason, the user is on Excel for Mac. 2016. If you're on a Mac, use in 2016 the version comes up, it's 15. If you're on Windows, it comes up a 60. So that's the technique. And obviously, if you need to do this, you may need to sort of a test for some of the earlier versions that this is just to illustrate how, if else if it works So these are all the tests we want to do and then the else becomes our cattle Is none of the other truth. What value do we want to place inside? Str version So let's simply say Let's str version cool and then we'll put a fixed piece of text for example, a pre 2010 version. I think so. So having the else clause within your structure allows you to cater for pretty much every eventuality chest or all the things you're interested in testing ball and then the Els will apply to all other possibilities. So what we've achieved with the if statement is to have the variable str version contain the version that excel and now we simply need a function to return. Str version. So after the statement, we simply need to say Let's excel version equal. Str on Then let's just coated, debunk and compiled to make sure there are no errors. And now let's give XXL and use on function. So this static message we can now convert into a dynamic one. So make the whole thing. Is your formula equal sign at the beginning and converting out text into a string by enclosing it in quotation marks. And then instead of this static 20 tainment, we now can Katyn eight using ampersand. How do you function? Excel version Closing Apprentices on memory Presenter I'm using Excel for Windows 2016. Onda function just picks that up. 60. 11.2. Using Select Case statements: Let's now move on to look at the second conditional structure provided by B B. A select case statement. So in 11 using condition ALS and looping, let's open zero to You didn't select case statements. And in this example, what we want to do is to display a formal date in this cell, one that includes the use of an ordinary Let's go into developer visual basic. And here you'll see we've got the function that we wrote in the last video, unless I didn't there just for comparison. The reason that we used if else if in that example, is that we had a complex test, so we were testing for a different possibility. Each time where you're evaluating the same expression each time, it's more efficient to use a select case statement. So if we were purely evaluated and Engy version and nothing else, select case would have been a better option. Let's write a function that uses select case as opposed to, if insert procedure tight assumption and let's call it dates with war journal Excel likes to put new functions at the bottom of the page. Sunday is in the movies to the top for convenience. How function won't need any input. So nothing goes inside the parentheses. All we need to specify is the type of data it returns. And since we're gonna be using concatenation, we'll end up with a string. So after the closing parenthesis, we need a string. Next. Let's create a couple of variables to make life easy for ourselves. So first of all, we have a variable toe hold the ordinary itself them str orginal a string and then let's have an integer variable toe hold the day of the month So dim LNG day as long next week and populate l N g day. So let's day equal and we can use the BB a day v v a date time, don't they? It takes a single granite and which is the date they killed all equals a man. We again used to be a to specify today's date again deviate update time. In this time we use the date function. Now let's have a look of the generic structure of a select case statement. We have the keywords select case. Then we haven't test expression. Then we have a series of case tests and you'll notice that we can combine them into a comma separated list after each case statement specify what we want to do. If any of these values proves to be true, just as we have the option for a cattle else statement when we're working with if statements with the length case we have case else, they all of the case statements proved to be false. Case else will apply and then we use in select to close the whole structure. So now scenario the test expression will simply be LNG date. That's something we're evaluating and then we need to test for S t nd on rt So s t we test for 1 21 and 31. And of course, if LNG day he calls any of those three our str orginal needs to contain S t and then we can just copy that for nd on rd so nd would be to or 22 and R D would be three or 23 if LNG contains none of these values. Enter case else we want str orginal to equal th So now we have our ordered all we can construct our final date on return. That date is the value of our function. So after the select a statement, we simply say, Let my function. In other words, let's date with orginal equal most of all the day which is contained in L N G Day and then we use an percent to tag on. Str orginal amber send. I meant to generate the rest of the date I The month in the year we used the very convenient all that function. So that is a string function so excited we ve a got strings. No formats on the expression that we format in on the expression that we format in is the date that's BB a hot date time don't date comma And then the four minutes which has to go in close will be a space followed by the full name of the month. So four times space followed by the full year. So for wise and closed parentheses, toe end the full meant function. So that's our function. Let's just test it and then let's use it in our worksheet so he can delete the static date and replace it with equals states with little way enter, we get our dates, which includes the orginal 61. 11.3. VLOOKUP pro rata function: if you're an experienced Excel user, you're probably familiar with the B look up function I won't will be doing in this video is creating our own variation of the V look up function, which has a pro rata feature. In this example. V Look up is being used to calculate the annual bonus paid to sales people. And if we take the first person Alexander Williams, you'll see that he's entitled to a bonus payments of 1.25%. He falls in this bracket between the quarter of a 1,000,000 half a 1,000,000 but he's much closer to the half a 1,000,000. So if a pro rata system were in place instead of getting 1.25 because he's fallen short off the half a 1,000,000 required for 2.5% he'd be getting a pro rata figure. So because his sales close to the upper limit of the band in which he falls so his bonus rates will also increase. So on our pro rata system, you get a bonus of about 2.4% so that's kind of visual. Basic. Let's begin by incessant module. I will call it M user defined, and now we need to insert a public function. I will call it be Look up pro rata. Let's begin by defining our variables and at the same time I'll describe the technique that we're going to use to come up with the answer. So our first step will be to identify the upper and lower limits that apply to each person , said Look at the person sales. And then we want to find out which band they fall into. So the first person is you can see Onley look up column falls into the band between quarter of a 1,000,000 half a 1,000,000 then on the Return column that column but contains the answer. He falls into the 1.25 to 2.5% bracket. And, of course, for this function, both the input values and the output values are numeric, so hence will use the double data type for all of these variables. Next, we'll calculate the look up range, and to do this will simply subtract the lower limits from the upper limit. And we'll do this both for the look up column, but first column. I'll look up table on the return column in this case, the second column of our look up table. These two figures will tell us the input and output bands within which we need to calculate the final pro rata figure. What we now need to do is to calculate where within these bands the figure actually falls on. The first step in doing this is to calculate the difference between the sales figure that we're looking up on the lower limit. So in this case, there'll be quite a big difference because this person sales figure is close to the upper limit. So we'll call this one DB l look up, def. Then once we have these figures in place, we can work out our pro grant a factor. And to do this will take the difference between the look up value on the lower limit. A man defined that by the look up a range. So if the person sales figure falls bang in the middle, we'd end up with no 0.5. In this example, this person sales figure is very close to the upper limit, so we end up with a figure of 0.9374 and finally will need a range variable so that we can loop through the range specified by the user. Let's now turn our attention to the other type of variables will make the parameter variables. So we compare our V look up with the built in V. Look up what we've got here. I can just click on the inside function button to bring up the function with it. You'll see that we've got a parameter called look up value. Then we've got the table of Ray and then we've got the column in. Next time we will need all three of these. We won't need the final optional parameter, but what we'll do is we'll use exactly the same terminology that Excel uses. And then finally, we need to specify the type of data being returned, which in this case is going to be a double excels, built envy. Look up can, of course, return text values. But for our function, text doesn't make any sense. The points of pro branta is too calculated numerical output value, so our function only needs to think about returning a double. So our first task is to locate which banned matches to overcome value so the use is going to specify the table the look up table by doing one of three things. Bacon drank across just the figures they could drink across the entire table, including heading. Or they can do what most people do and dragged across the column headings. So the strategy we're going to take is to start on the top left of the specified range. And then before we proceed well, there's need to calculate whether we're in a cell that contains a number, and if we're not weaken, then just move down one cell, which will take us to the first numerical cell within the range. So when we set our anti look up, we need to look inside our table array parameter on that has been defined as arranged. So since I pressed dots, the intelligence gives me all of the members which apply to the range. Object on what we're interested in is the cells within the range. And to get to the top left, we say Really, next one column index one. So that's our starting point. Then we can use a one line if statement to say if they sell did not contain a number. Let's just move down one self so that takes us to the first numerical cell. And then we want to keep moving down until we come to a cell, which either contains a value. It is greater than the look up. Values in this case will actually stop on half a 1,000,000. Or if this person had actually sold more than a 1,000,000 we would be taken right off the edge of the table. So you also have to say, Oh, we come to a blank cell. So either of those two, we either come to assault. It contains a number greater than the look up value, or we come to a blank cell, so I'm going to use a do while loop for this on. We simply want to move down again. So after this code executes, we're either going to be on a soul within the table in the first column of the table. If the users sales figures exceeded, the maximum value will be on that blank room. So let's deal with the blank row first, because that's the simplest. If they're on the blank road, they simply get the highest rate. So I just basically need to use the offset property to move up one cell and write one cell . Then that's the value that the function will return such a return of value. We used the name of our function, and we set that equal to the value wondering, turn. And of course, we'll meet and else. And then it ended. So in the else scenario we're on one of these cells on were then interested in populating our four variables to calculate the upper and lower limits of both the look up column and return Colin. I'm just going to copy this section. Ondas modify the code first, tap them in and change all of the let's all of the Dimmesdale. Let's on. We just specify what each of them is going to be. So the upper look up will simply be orangey look upto value, and then each of the others will simply get offset. So for this one, we need to go up one minus 10 and then to get to the return column. So if the first person this will be our orangey look up. So to get to the upper return limits, we simply need to go right one and then for the lower we get off one and write one, so this one will be 01 on this one will be minus 11 Next we calculate our ranges, and that's simply the difference between the upper and lower limits. On the other difference will need is the difference between the look up value on the lower limit, and this will tell us what the pro rata will need to be. So here we could see this person is very close to the upper limit. So we take his figure and subtract the lower limit from it. So here we using the look up, valued, specified by the user, and we subtracting BBO lower look up. So once we've got all these calculations in place, we're home and dry. We can calculate the pro rata figure, and then from that we can calculate the final value returned by this function. Said you calculate the print Bronson. We simply need to divide the look up def by the range that it forms part off. And then finally, to get in function to return a value. We used the name of the function like we did here in the positive part of the statement. What will need to do? It's festival to multiply D B l return range, buyout program figure, and then add that value to the lower limit of our return section. So for this first person you remember, he falls very close to the upper limit of his bracket. So he had a high de bl pro rata of no 0.9 something on. What we then do is we multiply that pro rata by the difference between these two. That gives us the programs a factor, which we then add to his lower limits. So instead of just get in the lower limit, which be look up, gives him he gets that lower limit figure, plus the program after amount within the band that he falls into. So let's save and test our code. And to test our code, we can simply delete the original look up. And he says, I run so he's inside function, and then your functions, ones that you define will be under a special category gold user defined There it is. You look up program. What mean set the function, All the parameters that we've defined it is blade in exactly the same way as for built in functions. So I look at value will be too table array of there's gonna use the column methods on drinking across the two column headings pulls the column index. It's called him, too. America is so his bonus rate instead of Bean 1.25 it would have bean with you. Look up is now closer to the upper limit on his 2.5. I just double click to copy that down for everyone, and we could do one final test. If I put a 1,000,000 in front of his figure. He now gets the upper limits of 7.5. So let's just don't do so. That's an example of where you might use a user defined function where you want to calculate a value within the body of the worksheet on. There isn't an Excel built in function that will do exactly what you need