Amazing BYROW Function in FILTER Function Make Dynamic Filtering by OR Logical Test Easy! EMT 1913

Welcome to Excel Magic Trick 1913. And in this video, like last video, we get to check out the filter function to extract records. In this video, we're going to see how to do it with an or logical test. Last video we saw how to do it with an and logical test. And the most amazing of the three examples will be when we use by row and the or function in the include argument of the filter function. Now the goal of this video is to use the filter function on these customer records, comma, and in the include argument create an or logical test. Now these are credit customers and we have 1, two, three columns with financial data and the or logical test that this company uses to decide if the customer gets credit. Well, it's an or logical test. We say of the one, two, three columns, one, two, three conditions, are last year's sales from this column greater than 1. 5 million or is the credit rating greater than seven or are the assets greater than 9 million? If any one of these tests are pass, and by the way, we can have any one, we could have two, or we could have three. That's the definition of an or logical test. One or more of the tests must come out true for the or logical test to deliver a true. Now, we're going to need to create a formula element here that has a column of trus and falses that's exactly the same number of rows as the data set. But before we do it up here, I want to show you in the cells one, two, three different methods. Then we'll take that formula element if we can put it in the allow argument and bam the filter function will work. Now we're going to start out looking at the or function. Now the or function like the and function is an aggregate function. It can only deliver a single answer. Even if you put the whole range in here and do the logical test like we'll do in this second column and somewhat in the third column doesn't matter or is aggregate. So we're going to have to check each row one by one. Now logical one that just means of the one two three tests which one are we going to look at first? Hey we're relative cell reference last year sales for this customer in this row. Are you greater than the hurdle? Now we have to hit F4 to lock that cell reference. So as we copy it down, it will be locked. This one is not locked. So of course it will move to each subsequent customer. Last year sales, comma, credit rating as a relative cell reference, are you greater than credit rating the hurdle? F4, comma. And then the third test, relative cell reference for assets. Are you greater than 9 million? F4. Now if we close parentheses, when I controll enter, it delivers a true and I can copy it down. So it will give us a true or false if I hit F2 in every single row. F2 allows us to check the locked cell references and the relative. Now, if we wanted to, and I'm going to do this down here, we could use this column inside of filter. Once we put all of the records for all of the customers into array, comma, we can't put the ore there, but we can highlight each one of the many ore functions in this column. And notice that range has same number of rows as the array. So, when I close parenthesis, control enter, there are the correct records. extract it off to the side. Now to show you that the ore function doesn't work when we put the entire column in. This construction is what we're about to do in a different form. But if we do this controll enter, forget it. Or only delivers a single aggregate value. So what do we need? Equals. Well, we're going to need the three logical tests. And each test has to be in parentheses. We ask of the whole column. Hey, how many of you are greater than the hurdle? Close. If I highlight hover, you can see there's a column of trus and falses. But we need to run this as an or logical test. Now, as opposed to an logical test where we use multiplication, an or logical test uses

the plus operator. And here's the reason why. When we use multiplying when we get two TRs that's two 1. So 1 * 1 is 1 which is true. Any other scenario when you have only two tests yields a false. Why? Because false times true is 0 * 1 and 0 * 1 is zero. What we want for the or logical test is we need this to come out true. So we switch over to plus. If we get true 1 + 1 is two. And because any nonzero number in Excel is true and zero is the only number that's false, of course that's true. But the plus works when we get false plus true. 0 + 1 is one. That's true. True false. 1 + 0 equals true. It's only when we get all falses that an or logical test comes out false. So we use the plus. And in our case, since we're going to have three tests, the possible answers in this column are 0, 1, 2, or all three. So we use the math operator plus. We put the next column in. Ask the question, are any of you greater than seven? Close plus. Open. Hey, how many of you are greater than 9 million? Now, we use parentheses and we did that on purpose. The order of precedence or order of operations runs comparative operators after math operators and we need all those trues and falses before we run the plus operator. All right. So when we controll enter we get a column of ones and zeros. One 2 three records need to be extracted. Now I'm going to change some of the inputs just so we can see all of the possibilities. Zero to three. 8 9. 5 million. So there we get two TRS. We'll put 1. 5 million. Tab 9 tab 15 and we don't get three because what is the comparative operator greater than? So I'll make this last year sales 1. 6 million. And so those are the possibilities. Now we can use this F2 because it's delivering a full column of numbers that represent trus and falses in filter array. Highlight all the records including the customer name, comma, include controllv close enter and it's working like a charm. Now for our last and most spectacular example F2. Notice we do have one, two, three columns all next to each other. If we just took that as a rectangle and they all have the same comparative operator. So greater than and we'll compare it against this single row and that'll allow us to use that inside a by row with the or function inside the include argument of filter to shorten up the formula. Let's just check out the result from taking a rectangle three columns same order many rows greater than single row same order of columns that row will be compared against each row and deliver an array of trus and falses and sure enough down here true two true that's why we got a three two trs and up here one true now we just have to run the or logical test for each row to get a single column of trues and falses. We can do that with the by row function. It's going to take that lovely array of trus and falses, comma, and whatever function we put in here, it's going to run it row by row. We're putting or close controll enter. There's our single column of trus and falses which we F2 can use inside the allow argument of filter array the entire tablet controllv close enter that formula is extracting the records if I change this to nine just like that everything's updating controll Z so you can definitely use the ore function as a helper column. Put that into the second argument of filter. You can build the entire plus operator direct or logical test array calculation or we can use by row and or because the columns are in the same order and the same operator is used for each calculation. All right, that was a lot of fun with bro by bro and and or all inside the include argument of the

filter function. All right, we'll see you next Excel magic trick.