Transcripts
1. Introduction: Array Formulas: All right, Welcome to Section nine. We're in the homestretch now, and this section is gonna be talking about a new breed of formula called the array formula . And for those of you who have never worked with the ray formulas, they operate a little bit differently from non ary formulas that they have a slightly unusual syntax and usage that takes some getting used to. So there's a bit of a learning curve there. But once you get comfortable and learn how to use them, you'll start to see that they can open the door to do some things that you really can't do any other way. So we're going to start out the section with the rules of array formulas, how to use them and what some of the common pros and cons of array formulas are. We'll talk about vertical, horizontal and two dimensional array constants. And then how to use those constants formulas how to use named a raise. Then we'll go into a bunch of demos and excel. Start with transposed. I'll talk about linking data between sheets. I'll show you how to use the non Array versus the array approach. Compare and contrast that to. We'll talk about how to return the X largest values from arrange in a single step. We'll use the Len function that count characters across a range of cells and last but not least, will create a custom max if function so as usual to files to download. Excel for analysts, array formulas that pdf and a Section nine or a formulas Excel file. So go ahead and open those up and let's hit it.
2. Rules of Array Functions: Okay, so what the heck are right? Functions? Basically, they're designed to perform multiple calculations on one or more items in an array. What makes them very different from normal cell functions is that a ray functions can either take the form of a single cell formula, in which case they exist within one cell or a multi cell formula, in which case it's the same formula applied across a number of cells. Very important note is that you must press control, shift, enter toe, enter, edit or delete any array formula. If you don't press control, shift enter. If you just press enter like you're used to, these will not work so worth repeating again. Press control shift. Enter every time you enter, edit or delete an array formula. When you do that, you'll see that excel automatically at brackets around your formula, starting before the equal sign and going all the way to the end of your formula, which indicates that the function applies to an array. So let's take a look at one example. If I were to select the range from D to three d five here and type the formula be to through be five Times C two through C five and hit Enter. The formula will only be applied to sell D to If I select that same range type the exact same formula and hit control shift enter. I've been created Honore formula and it applies to all cells in the range that I had highlighted. So as you can see, the results populate across all four rows within my ray. So rules of array functions unfortunately, there quite a few on you must obey all of them in order to use these properly. The first, like I've mentioned multiple times now is that you must press control shift enter as sometimes abbreviated as C S E. Some people call it ray functions. CSE functions for that very reason to edit or enter Andre Formula Rule two For multi cell array functions, you must select the range of cells before you enter the formula. So step one is understanding what range of cells you want your formula to be applied to and then step two is actually writing the formula. Rule number three This is an important one. You cannot change the contents of any individual cell which is part of an array formula. It's it's kind of strict and that as soon as you write in a ray formula that's specific to an array you can't insert Rose. You can't, uh, delete values. You can't move cells if they were part of that array. Rule four. You can move or delete an entire array formula, but not a piece of it. So often times. If you're array needs to change, you have to delete your entire array formula and rebuild from scratch and then last but not least number five. You cannot insert blank cells into or delete cells from a multi cell array formula, so bottom line press control shift enter. Select the range of cells before you enter the formula. And make sure that your array is not gonna change once you've written the formula. So those are the rules of a ray functions. In a nutshell.
3. Pros & Cons of Array Functions: all right, so you may already beginning. The sense that Ray functions, although they can certainly be incredibly powerful, can also be a total buzzkill to work with, especially as your first getting familiar with them. They do tend to be a bit more rigid and strict than non array functions, so the learning curve is a bit steeper here. Regardless, let's take a look at some of the comment pros and cons on the pro side. First, they're really great tools to condense multiple calculations into one formula, which has the added benefit of often reducing file sizes. Second, it can perform some complex functions that non array formulas cannot. I'm gonna walk you through some examples that demonstrate that, and then third, they helped to reduce the risk of human error, such as accidentally deleting parts of a raise or miss typing formulas on the con side. It could be very difficult to modify or delete existing array formulas. It could be kind of clunky to work with. You have limited visibility into the formulas function, especially for users who are not familiar with the rays. So it just makes a little bit trickier, toa que way to troubleshoot and to identify how combinations of functions may be working. Third, it eliminates the option to modify cells contained within a raise. So if you have a data set that tends to change quite a bit, are a functions may be very difficult to work with. And last but not least, they may actually reduce processing speed if multiple array functions are used so personally in the work that I do, I find myself working with a very dynamic data that's constantly changing and updating, in which case, there many cases that are ray functions aren't the right fit for me. But that said, there's certainly opportunities where I've used array functions because there's no other option that could have accomplished the same thing. So at the end of the day, it's really just a function of what data you're working with and what you're trying to accomplish when it comes to using array functions versus non array functions. So with that, let's talk about the different rate types and named raise, and then we'll jump right into excel
4. Vertical, Horizontal, and 2-Dimensional Array Constants: Okay, So a ray constants are a raise that are created by manually entering a list of items directly into the formula bar and surrounding that list with brackets. So there three types. There's horizontal or a constant, which creating array contained within a single rope, those air delimited by commas. So what you do is select the horizontal array of cells that you want to create in this case , a 1 31 If we wanted that array to populate with the numbers 123 and four we were type equals. Open bracket one comma, two commas, three comma four. Close the bracket and then hit controlled shift Enter. It's important to note that you have to manually type this set of brackets, and when you press control shift, enter. Excel will then add a second pair of brackets around the whole thing. So that's the horizontal or a constant vertical array. Constance work the same way you just select your vertical array of cells, and then you delimit your values by semi colons instead of commas. So if we were to populate an array from a one through a four with the values 1234 we were type equals Open bracket one semicolon to semicolon, three semicolon four. Close the bracket, then hit control shift. Enter And last but not least, you can combine horizontal and vertical array constants to create two dimensional rate constants. So to write a two dimensional array constant to create one from scratch, you basically input your values the same way that you would read a book starting the top left each row moving from left to right. So you list the sequential values from columns aid B to C to D, separated by commas and then use a semi colon to indicate a jump down to the next row. So in this case, we have the values from one through eight and since 123 and four off, all in the same row. Those air separated with commas and then the semi colon indicates that the five is starting on the second row back and column a so again press control shift. Enter and your array will populate. So let's bounce over to excel. I'm just gonna open up blank work. She just get some practice working with this. So let's select a 1 31 go into the formula bar and will take equals open bracket one comma , two commas three common for close the bracket. And if I just press enter now, you'll see that value populates and sell a one. The number one but B one C one and d one remain unaffected. Issue is that I didn't press control shift, enter. And when I do that now you can see that it's added the second pair of brackets around my function, and it's populated the array that I had selected with the values that I had input here. So that's how to create a basic horizontal array. Let's take a look at how to edit and delete that arrange. So the challenge is, you know, now that I have my array function written, if I want to change the Ford of five, I can't just change the cell value because it's part of this array. So press cancel. Um, similarly, I can't just go into the formula bar and change it there for the same reason. So what I need to do it's basically select the entire array, go into the formula, change the Ford of five and then press control shift enter again to update the array when it comes to deleting the array. A similar case. I can't just delete individual cells. What have to do is either select the entire array and delete the values that way, in which case the functions disappeared. Or I'm gonna undo that. It can select the array, go into the formula bar, delete the formula. And now here's the catch. Even though there's no formula written there by press enter, nothing happens. So I can keep hitting. Enter over and over and over, and nothing's gonna happen after press control, shift, enter even when I've deleted the function to fully clear the array that I just created. So again, learning curve is a little bit steep. Once you start playing with this, it starts to become a bit more natural, but again, definitely much more strict and rigid then traditional cell formulas. So let's create a vertical array. You select a 134 Going to formula bar equals open bracket. One. Semicolon to semicolon. Three semi cone. Four. Remember the semi colons, or what? Indicate or delimit different values that fall in individual rose. So close the bracket control shift Enter and there you go. It's populated Mireille in cells a 134 So again to delete that, delete the function, press control, shift, enter and it's gone. So let's do one more example. We'll do a two dimensional array from, Ah, a one through Be for So in this case, type equal bracket. And then remember, it's just like you're reading a book. So it's going to start with whatever value I want in a one followed by B one than A to B to a three B three a four before. So why don't we just do, Ah, sequential numbers again? So one and then comment takes me to the next Colin Value, which is to semi colon jumps down to the next row. Three. Common for semicolon 56 come close. The bracket control shift enter and what's needed? A. I needed one more pair of numbers for this to fill the full array that I had selected, so this is a good example. When we just go back into the function, add one more semi colon seven common eight control shift, enter and there you go. That's the proper array. So horizontal, vertical and two dimensional race
5. Using Array Constants in Formulas: so two important things to note about a rate constants. The first is that they don't just have to contain values as I've been showing you. They can also contain text surrounded by quotation marks or even logical values or error values on second, rather than being used just to populate cells there more often used as part of a ray formulas. So looking at this example here, as you can tell, there's an array constant nested into a larger ray formula. And they're actually four things going on here. We've got our some function a stored array, which is just the reference to a one through a five, the multiplication operator and finally, our ray constant surrounded brackets. And because these numbers are values are delimited by semi colons. I know this is a vertical array constant. So all that this function is doing is it's taking each value in the array from a one through a five and multiplying it against its corresponding value in the array constant that we've nested in here. So basically the some function is just adding up or aggregating all of those products, so five times one eight times 22 times 37 times four and three times five to arrive in a total of 70 eso tip here. Like I mentioned, these brackets surrounding the rate constant are manually added. The outer brackets are added once you press control shift enter to convert this into an array function. All right, so let's hop over to excel, get a little practice with this. First things first, I'm gonna create vertical array. Constant. I mean, type equals bracket. 12345 Close the bracket control shift, Enter. And now I'm going to select B one to B five and I'm going to create a multiplication problem. That's just gonna be the values in a one through a five. That's my story. Array times. And now I'm gonna nest of new vertical array within this formula rather than creating one separately and then referring to the cells that it's populated. Just gonna work it right into the formula. So 10 semicolon. 20 30 40 50. Keep in mind that if I were to put comments here, it would screw everything up because it will be trying to multiply values moving down, row by row by values moving over calling my column and things wouldn't align the way I want them to. So right now I'm creating an array with the same dimensions as a one through a five. I'm so that I get a clean kind of 1 to 1 relationship. Close the bracket control shift, enter to finish that. And as you can see, this seems to working properly. Basically, what it's doing is saying a one times 10 which is 10 a two times 22 times twenties, 40 three times, thirties, 94 times, forties, 1 60 finally, five times 50 is to 50. So, as you can see, I've saved a step by just inserting this constant into the array function. So in the next section, we're gonna talk about how to actually name these arrays eso that I don't have to write 10 2030 40 50 from scratch. If I'm gonna be using it over and over, I can give it a name and then just type the name into the formula the same way that we used named a raise in some of the earlier sections.
6. Named Array Constants: all right, so just like normal cell ranges, you can assign a name to a certain array constant. The only difference is that in the refers to box, you write it just like you would if you're trying to populate cells, so you add the brackets and either delimit by semi colons. If it's a vertical array, commas if it's a horizontal array or a combination of both. If it's a two dimensional ray. So all you need to do is just go into formulas, either name, manager or define name. Create a name for your a constant and then type it just like you normally would in the refers to box and know that you don't have to press control shift enter here. You just type it in with the brackets and press OK that will store the array constant with name so that you can either populate cells by just typing the named array. Or you can replace the array constance with the named Array. If it lives within a formula. Eso, even when you're just typing the name Furnari Constant, you still have to press control shift enter for it to populate, so it's jumping to excel and do a little practice with this, um, again, Just working with the blank workbook For now. Um, why don't we start by creating, you know, standard vertical array from one through five equals bracket. 12345 It should be very familiar by now. Control shift enter. So that's one way to add the array. And as you can see, it's stored as an array constant with each of the five values. Now what I could do is go into formulas to find name, give it a name called Numbers and then here, where it says refers to delete everything after the equal sign, type the bracket and then the exact same thing. One. Semicolon 2345 Close the bracket off press. OK, and now, if I select be won through be five and in the formula bar type equals numbers, you can see excellence flagged it because it knows that there's a name to Ray called numbers. I could press control shift enter and it will apply that array to my selected range. So let's do the same thing. Except instead of using numbers, let's create a named array with text. Someone call this one text, and it's going to refer to another vertical array that's going to be in quotes. One semi colon to semi colon three semi colon. Sorry, blocking you there for chemical and five. So just make sure that your surrounding each of your values quotes that you're surrounding the whole thing with brackets. So press OK. It saved that named Array as text, Now in C one through C five, There's type equals text could see it's recognized it control. Shift. Enter and it will apply the text array there. So now, just to use those stored or named raise as part of a function, it really works the same way. So let's say we just have five random numbers in a vertical array from a seven through a 11 . Um, what I could do B seventh or be 11 if I want to do a similar multiplication problem that we did in the last lecture but use or refer to unnamed array rather than an embedded manual array, it could do that so type equals a 73 11 times. And then here's where we would have done you No one semicolon 2345 So this is kind of how we used to do it. Now it's edit that instead of putting the manual array in there, let's just right numbers. So a seven through 11 times the numbers are a control shift. Enter and we'll get the same response. So there's a just a few examples of how to use named arrays with a rate constants to make your life a little easier. Next up were about to dive into excel and walk there a bunch of different demos.
7. The Transpose Function: all right. So the first or a formula demo that we're gonna do is called transpose. And basically, the transposed function just allows you to change the orientation of a given data array. So, for instance, if you have an array that's five rose tall but two columns wide, transposing that would change it into an array from two rows tall to five columns wide. One thing to note that makes this a little bit tricky is that the range in which you enter transposed function must be the exact dimensions of the transposed data. So syntax wise couldn't be simpler. It's just transposed. And then the reference to your original array on again. Since this is gonna be an array function control shift, enter toe, lock it in one little tip. Here there are multiple ways to transpose data, and if you want to transpose the data set that you will want to later edit, you can transpose it as just values as opposed to a formula using pay special transposed, which is the altar H v T shortcut that I showed you back in the first section. So let's jump to excel this time. We're gonna open up our Section nine Excel Workbook array formulas. It's a pretty simple workbook. We've just got one tab to work with called sales data. As you can see, I've got a year column here. Ah, list of store locations, including Boston, New York and Detroit and then some hypothetical numbers for revenue spend profit and are alive. And we're just gonna work with this state it ray as we practise summary functions. So I'm gonna show you two ways to transpose this data the first way, which is the simple A route. If you just need the values to be transposed and that's that, it's just to grab the array from a one through F 10. It controls C to copy. I select any cell where you want to drop your transpose data that hit Ault H V T. That's Paste Special transposed. And as you can see, it's changed from a six by 10 array into a 10 by six array, and it's basically just moved the values into a new orientation Here. The second way is to use an array function. So remember, I need to select the perfect dimensions of my transpose data before I write this function and I'm actually going to show you the wrong way to do it first, so that you get a sense of what happens if you don't follow those rules. I'm so I'm gonna just pick a random array like this and in the formula bar type equals transposed. And then again, my raise the original data set. Close the parenthesis, and I'm ready to press control shift enter to transpose it. So what the transpose function will do is it will fill up the array that I've selected and any cell where it can't find the appropriate value from the original right? It will just fell in with an N A. So I'm gonna delete that control shift enter to get rid of it. And now I know I want 10 by six. So there's 10 columns wide 23456 And let's start over and type transpose one more time again. You raise a one through f 10 close it control shift, enter. And there you go. If I want to give this the same formatting, that's kind of what's nice about all th e v t is that it preserved the formatting so I could just grab this chunk in the home toe had press format painter cook the top corner here and will apply the same formatting. So as you can see right now, the same transposed function will appear anywhere. I click within this. All right, um, so there you go to different ways to transpose data. And again, it depends on what you're trying to do here. To determine which approach to take if you need to maintain a link to your original non transposed array than the ray function is probably the best bet. If you're looking just to reorganize your data and then edit or modify that data later, then the old H V T shortcut is probably your best bet. Either way, that's transpose function.
8. Linking Data Between Sheets: Array vs. Non-Array Comparison: all right. Demo number two is about linking data between sheets. So let's return to our sales data tab. I'm actually just going to delete the columns that we had created from the transpose exercise. Now, what I want to do is just duplicate the values in a one the ref 10 by just linking them to a new sheet. So I'm gonna show you two approaches the non array approach first, followed by the array approach. So I'm gonna create a new tab. Call this duplicate one, and I'm gonna do is write a self formula and sell 81 just equal to sales data. A one press enter. And since this is a relative reference, I can just drag it out to f down to row 10 and it will duplicate, uh, the array in the sales data tab. As you can see, each cell has a different function because it's linking to a different cell in the sales data tab. And again, if I wanna be particular about my formatting, it can also use the format painter to apply for many between cells. So I've got duplicate data on two tabs. Let's create 1/3 tab called duplicate, too. And in this case, I'm gonna select the entire array that it will fill and then press equals sales data a one through F 10. Only difference is I'm going to hit control shift enter as an array function. And there you go. It's pulled in all of the correct data from the sales data town again. I'll just copy the formatting and paste it there. And so there you go three versions of the same data set. The difference Now, when you look at these cells is that in the duplicate one version, I have a total of 60 formulas here granted their simple formulas. But each cell has a different formula in the ray version on duplicate, too. Anywhere you click, you'll see that it's just one function because it's being applied to an entire array in a single step. So that's just a demonstration of how Ray functions can help you consolidate formulas in a case like this. And so, you know, even if I were to change values here, so 2015 Boston spend was actually 1000 that will actually update properly in both the array and then Honore versions. So there you go, linking data between sheets using array functions
9. Returning the "X" Largest Values in a Range: aren't so. Now that you have a sense of how these air a formulas work, let's start integrating some the tools and functions that we've learned in previous lectures and see how those could be used as part of a ray functions. So the first example that I want to show you is, Ah, let's say I want to pull out the top three years of revenue from column C. So in each one, I'm just gonna right top three and, ah, the first way that I would think to do it. But non or a version that we've already learned would be used. The large function so equals large ray C two through C 10. Press that for to fix that and then, for the number one largest that just type but one for the K value. Close it out and hit. Enter. I could just copy that, paste it two more times and just change the K value to a two for the second largest and 23 for the third largest. So that's the non array version. I wanna format these same way. She's the format painter, so that's three separate formulas to return the three largest values from column C. Um, let's do the same thing here, Top three. This time I'm going to use an array function to do the same thing. So remember, I'll start by making my selection of all three cells I to through I for and formula bar type equals large ray that I'm looking for. The largest values is still C two through C 10. The only difference here is that for the k value, because I'm looking for all three largest values. Can't just put a one or two or three. I need to put on a rate constant in here. It's gonna open the bracket and do a vertical array constant from one through three. Since I'm looking for the three largest values and the array that I've selected is a vertical array three rows tall. So then I just need to close that parenthesis control shift enter and there you go. So same formatting as you can see getting the exact same top three values. The only difference is that I have three formulas here in column age, just a single array formula and column I Now what if I wanted to change this from the top three to the top five. Um, again, I could just copy this. Pasted down two more times. Change the 3 to 4 and a 4 to 5 for the array version. What I could do. It's select a vertical array with five rose in it. I could add a semi colon four, semi colon five and control shift. Enter. And there you go again. Just update the formatting Here, change this to, uh, top five and again two ways to accomplish the same thing.
10. Counting Characters Across Cells: all right, So the next demo that I want to share with you is about counting characters across a range of cells, and this isn't a particularly difficult one. It's actually quite a simple array function, but it's a good example of a task that you can't accomplish in a single step unless you use theory a formula. I'm so if our goal, for whatever reason, is to count the number of characters across this entire range of store locations. B two through Beat 10. If I were to take the non array approach when I have to do it's type of Siris of Len functions that relate to column B and just apply it down. So that gives me the number of characters in each of these city names, including spaces. And then I would have toe quite a sum function down here to give me the total. So 63 characters across this entire range. I can also do the same thing with an array function in a single step, and I could start with the length function and apply it to be 23 b 10. But the problem is, even if I enter it at with the control shift. Enter, I only get six because it's on Lee calculating the length of the first cell in the reference. So what I need to do is just insert a some function here as well. So it's gonna be some of the lengths of each of the strings in every row of my ray. And now, once I hit control shift, enter. As you can see, I get the same total in one single step, as opposed to about a dozen so one example of how you might be able to work more efficiently by taking the ray approach versus the traditional cell formula approach.
11. Creating a "MAX IF" Array Formula: All right, so for the final demo gonna show you how to build a custom max if function. This is a great example of where array functions can come into play when you're non array or traditional cell formulas may fall short. So if you recall in the statistical function section, we talked about some ifs, count ifs, average ifs. Unfortunately, there's no equivalent formula if you want to calculate the max value in a column based on some criteria. So there's no max if out of the box function that we can use. Luckily, array functions can do the job. So what I want to do here is return the max value for revenue and column C based on a specific store location name So one of these three city names. So the first thing I want to do just for the sake of showing an example. It's just name this array city, and I'm not making ah array constant. All I'm doing is just naming the range from B to three b 10 just so that we remember that that is an option to help save some time when writing this function so that arrays now called city What I'm gonna do is write one of the city names right here in H eight, take Boston and then in h nine. And this is where I'm going to type my max. If function so it's gonna be a relatively simple function we're going to start with equals , Max, open the princess if and now the logical test is gonna be city. You see, it's kind of triggered at the array because it recognized it as a named Ray, and city equals whatever values in h eight. Right now it's Boston. And if I change that value, you would pull in the max for New York or Detroit, for example, on then the value of true is just gonna be the array from C two through C 10 which is my revenue field. That's where I'm trying to extract the max value from so close off to parentheses and control shift enter. And there you go. So grab the formatting from appear in the home tab. Boston, $1726. We could do a quick spot check. Confirm that that is the max value for Boston. And after change this to New York, for instance, in 1944. She's also correct. And Detroit, 17 43. There you go, so great way to kind of create our own custom function where we didn't have the option. Using our traditional statistical formulas that wraps up the array formula section. This is really just a small sample of the types of analyses that you could do with the race . So I encourage you. Once you get comfortable, just start exploring how and where you can use the's in other areas. So next up, we've got one final section covering some bonus functions or to do some really, really interesting demos there, so stay tuned for the final stretch.
12. BONUS: "MAX IF" with Multiple Criteria: all right, so actually lied. I've got some additional array function demos that I want to share with. You guys that have come up with since the course was originally published really helped to demonstrate just how versatile and powerful array functions could be. So in this lecture, I want to actually pick up on the max if function that we just completed and make it a little bit more complicated. So instead of just basing the max, if on one criteria where City equals Detroit or Boston or New York, I want to update that Max. If array function to account for multiple criteria specifically both location or city and the year so jumping back into the workbook, the first thing I'll do is just select a two through 8 10 and name that range year just to make it a bit easier to work with and out here. And I ate. Let's just put one year could be anything. I'll put 2014 here, and I would sell I nine. This is where I want to enter my new max. If function, I'll actually just drag this over to give me a good starting point, I can edit the references City still needs to equal H eight, and the values were searching for the max within. It's not D to three d 10. It's still see to the Receipt 10 the revenue field. So if I press control shift enter, you'll see that I've got the same starting point as before. And now I could just add that second criteria in here So I want to do is actually surround my first criteria. City equals H eight in parentheses. Multiply that by my second criteria, which is year. You can see it identifies the name range equals I ate close that parenthesis. So I've got two criteria, each surrounded by parentheses, separated by a multiplier. I'll show you why in just a minute. So if I control shift, enter now it returns 16 09 which is the max value If City equals Detroit and year equals 2014. So Detroit 2014 16 09 Now, obviously, it's the only value that exists for Detroit 2014. But to give that a test, we can change the 2013 to 2014. And now, as you can see, the max value changes from 16 09 to 17 43. So just undo that change back to 16 09 Now, let's dig in a bit, trying to understand exactly how this is operating in. The best way to do that is to use the evaluate formula tool. So with this cell selected, I can go into formulas, evaluate formula, and this will walk me through each step of the function and how it's evaluated. So we're gonna start with the city underlined, and when we evaluate the city named range, it translates it into the array of nine different rows of data. Boston, Boston, Boston, New York, New York, New York, Detroit, Detroit, Detroit. So we're now looking for cases where the values within that array equal the value in age eight, which evaluates to Detroit and as Excel cycles through this, it's going to translate that statement into an array of truce and false is so for every row where that condition is true or the city equals Detroit, it will return a true and for every row where that criteria is not true, it will return a false so that first criteria city equals age eight translates into false, false, false, false, false, false true. True, True, since only the last three rows within this array meet that criteria. Next up, we're gonna do the same thing with our year criteria. So year evaluates to an array containing all of our dates in these nine rows and columns A . And we're searching for cases where the values in that array are equal to the value and I eight, which right now is 2014. So this will evaluate in a very similar way it will translate this statement into another. Siris of true and false is in this case, we have false, true, false, false, true, false, false, true, false. And as you can see, that makes sense because for every three years, only one is 2014. So now here's the key that allows this function to return the proper value we've created to a raise. Based on our two criteria. The first array contains true and false values based on rows that match the location city. The second array contains true and false values based on rose that meet the year. So when we multiply these two together, the result is a single array of zeros and ones which can be interpreted exactly the same way that you'd interpret truce and False is where true is equal to one false is equal to zero. So what we have here now is an array that looks like 000000010 So Onley one value in that array populated as true. And that's because any instance where one criteria or the other failed to be met. When those two values are multiplied, one of which has to be zero, it will result in a zero in the final array. So one indicates the row within our array where all criteria have been met, and in this case it's the second to last row. And if you just look at our data here, that second to last row Row nine is the road that contains both the year We care about 2014 and the city that we care about Detroit now the last bit of this function. The if statement essentially just assigns a value from within the range from C two through C 10 to any value in our ray that's not equal to zero. So in other words, we're gonna pull out the value from the second toe last sell in the range from C two through C 10 since the second toe. Last spot in our arrays, the only non zero value. And as you can see, it results 16 09 since that's the second to last value in our array from C two through C 10 and now we're just left with a simple max function. So what's the max of false or 000000 16 090 It's 16 09 And that's the value that gets returned. So as you can see a ray, functions are very, very different form, normal cell functions in that they kind of cycle through these processes all within the same formula within the same cell, and that's what makes them so powerful.
13. BONUS: Using the Double Unary Operator ("--"): Okay, let's walk through one more example. Just to show you how array formulas can sometimes be used to do things that sell formulas simply can't accomplish. So in this case, sticking with our example Data, Let's say someone asks you, how many years did profit exceeds spend in this data set? If you were to accomplish that using cell formulas, you might do something like a conditional statement that says, If he's greater than D, then returned one. Otherwise zero and you could apply that down and then take the some of that column and there you can see okay there four years or four rows in this data set in which profit exceeded spend. But that took two steps. I used 10 formulas. Total array functions could do this with a lot more elegance and with a lot less processing power to do that, let's just say, you know, profit. It's greater than spend going home menu line that making italic And here's where I want to return that same value of four. So I'm gonna actually use an array function using some, so I'm gonna say equal some and now, instead of just a single number or a single traditional range of numbers here that can actually input to ranges of data here all at once. So I can say E to through e 10 is greater than D to three d 10 and close the parenthesis. So in theory, what this should do as an array function, it's cycle through each row within both a raise and tally up a true or one in any case where the criteria is true or where column is great and column d and a zero otherwise, and then take the sun and return of four. So if we control shift enter, you'll see we get a zero, which is obviously not we want. So it's going to formulas, evaluate formula and see what's going on here. So in this greater than statement is evaluated, it does result in the array that we'd expect false, false, true, true, false, true, true, false, false and, as you can see therefore true values in positions 34 six and seven. And if you look at those specific rose within our data row, 1239 68 is great and in 7 58 49 eighties, great in the 9 64 6 and seven. So those are the correct positions or the correct instances of rows that match that greater than criteria. Problem is, when we try to sum this statement, it evaluates to zero. And the reason is that even though in many cases Excel will treat false is at zeros and True's as one with a some statement, it doesn't know how to handle those values. So Excel actually has a very useful little tool called the W Nery and that essentially converts non numeric Boolean values true and false into actual ones and zeros. So if we dive back into her function here right after that first parenthesis going to do two dashes, that's the double you Nery. No need to open the parenthesis again and close out the second at the very end. Now, if I control shift enter, it returns the proper value of four. So any time you have an array function where you need to convert a specific array from Boolean values into ones and zeros that W Nery should do the trick
14. HOMEWORK: Array Functions: all right. Time to pat yourself on the back. You just finished the race section. Crazy crazy stuff, definitely a little bit tricky to get used to and very, very different from the traditional cell functions that we've been talking about now again . Like I said before, this is really just scratching the surface and giving you a few select examples of how our A formulas can be used in Excel. That said, once you mastered the fundamentals of how Ray formulas work, you can pretty quickly start applying them and more complicated or more advanced ways. So homework time for anyone interested in digging in a little deeper and getting some more practice with these in the homework exercise file that's available in the course. Resource is we've got two tabs with array exercises. The 1st 1 is a ray functions, and here we're gonna practice some stuff like Max. If Max if with multiple criteria using that double you Nery to convert Boolean values into numbers and then last but not least, we've got this bonus array function tab, which is where things start to get really hairy. Essentially, what we're gonna try to do here is use Honore formula to return a subset of this range from a three d 14 based on specific criteria. So we want to return that subset right here in I three through l 14 but Onley for Rose that meet the start date, end date and minimum revenue criteria assigned here in column G. So I'm actually gonna give you a little sneak peek at the answer file because what I've done is laid out a step by step explanation in my own words of how this thing's actually operating. And if you take a little peek at the function, you can see that it's kind of a beast. That said, this is probably not the only way to do this. Keep in mind that this is the approach that that I chose to accomplish this, But there may very well be others. So, as always, posts to the discussion board. If you need help, message me directly, or if you just want to say hi, always here to help. Thanks, guys. Good luck
Chris Dutton, Founder, Excel Maven
