Identify all records where three or more conditions are met. Bill and Mike offer alternate formulas to simulate the mythical =THREEQUARTERAND() function. Also - using Evaluate Formula to test order of operations, converting Boolean results to numeric results, testing multiple conditions in a SUMPRODUCT formula.
Transcript of the video:
Hey, welcome back it's time for another dueling excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is our episode 148.
=Three Quarters and()?
Boy that's, that's not a function isn't sure would make this podcast easier though having a three quarters and function.
So, we're gonna have to, do something else, the question from unbelvbl at YouTube need to pass three out of four tests figures it might have to combine some combination of IF, OR or AND.
So, here's the four hurdle values here's the four values we need to figure out.
If three out of the four of those are true, and if we had a function like three quarters and we would be good to go, but we don't.
So, here's the trick.
So, in parentheses check and see if this first value, is greater than or equal to the hurdle value, I'll press F4 there are two times to lock down just the row.
All right! Now, that's going to return a true or false I need to convert it to a 0 or 1 and so, we have to do some sort of an operation there we can do a minus, minus before it, but just doing a plus between each one of these will convert those trues and falses to 0's and 1's, F4 twice plus parenthesis this greater than or equal to that out is the F4 plus last one for.
All right, so that is going to count how many of those are true.
So, in this case three of them are true because let's see which one was not this one.
If we had a credit rating of four, then four of them are true.
So, undo that.
All right! now, we wanna know if any three of those four true, put the whole thing in parenthesis and say is that greater than two, double click to copy that down and we are now, testing for three out of four.
Now, as you watch me do that the parenthesis are a real pain to enter would this be possible without the parentheses.
So, here's the same formula without the parentheses I don't know the order of operations that well.
So, we're going to just test this using formulas, Evaluate Formula and what I'm hoping is that they're going to do that greater than or equal to before they do this plus it will evaluate there's the B11 there's a B7 a shoot.
So, if they've gone to the greater than or equal to next then this would work, but right now they are doing the plus sign so, they're gonna add those two together which is not what we wanted we're not going to get the right answer.
So, we're kind of stuck.
All right! So, before I throw over to Mike, I just want to subliminally suggest something here If you've been watching these podcasts for a while the dueling Excel podcast, I bet that Mike is going to come up with some sort of an array formula that would do this all in one big step.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Hey, you know this four logical test, I like that, I like the simplicity of it, but yeah typing in all those extra parentheses is a hassle.
Hey, run in formula evaluator I love that trick because when you're not quite sure what's going on formula evaluator will tell you and that, that's just flat-out genius.
All right, I'm going to stay simple here I still want to run all four logical test, but instead of putting them in parentheses I'm gonna put them inside of the SUM function.
So, we'll test the first hurdle are you greater than or equal to that, F4.
So, I'll make each one of these logical test, each one logical test will give us a true or a false check-in for each one of these hurdles, F4 on each one of those, but the cool thing about the SUM function is, the SUM function will take a true or false and by adding each one of these that's the math operation that it will convert it to a 0 or 1.
So, once I put it in there the SUM function will do the trick double click and send it down and then I could simply come to the end and say are any of you greater than 2.
So, 3 and 4 will be picked up as true.
Totally good, check the last one for the cell references it look like it's working good I don't know, but I'm going to give MrExcel the point here double negative because we need to convert the array operation relative cell references four are you greater than or equal to the hurdle and we're going to lock this one F4, right?
So, that I give us a bunch of trues and falses that double negative will convert them to 1's and 0's and MrExcel's formula absolutely perfect F2 and then I'm gonna say are you greater than 2.
All right, throw back to MrExcel.
Bill: Hey, all right Mike, absolute simplicity I love that putting in the SUM gets rid of all the parentheses and the formula I threw up there, that formula, that was not my formula.
That was my guess at what your formula would be that shows after I went through that the method that would normally use us like how the heck would Mike solve this problem.
And so, that was my guess so hey points all around.
All right well I want to thank everyone for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
This is our episode 148.
=Three Quarters and()?
Boy that's, that's not a function isn't sure would make this podcast easier though having a three quarters and function.
So, we're gonna have to, do something else, the question from unbelvbl at YouTube need to pass three out of four tests figures it might have to combine some combination of IF, OR or AND.
So, here's the four hurdle values here's the four values we need to figure out.
If three out of the four of those are true, and if we had a function like three quarters and we would be good to go, but we don't.
So, here's the trick.
So, in parentheses check and see if this first value, is greater than or equal to the hurdle value, I'll press F4 there are two times to lock down just the row.
All right! Now, that's going to return a true or false I need to convert it to a 0 or 1 and so, we have to do some sort of an operation there we can do a minus, minus before it, but just doing a plus between each one of these will convert those trues and falses to 0's and 1's, F4 twice plus parenthesis this greater than or equal to that out is the F4 plus last one for.
All right, so that is going to count how many of those are true.
So, in this case three of them are true because let's see which one was not this one.
If we had a credit rating of four, then four of them are true.
So, undo that.
All right! now, we wanna know if any three of those four true, put the whole thing in parenthesis and say is that greater than two, double click to copy that down and we are now, testing for three out of four.
Now, as you watch me do that the parenthesis are a real pain to enter would this be possible without the parentheses.
So, here's the same formula without the parentheses I don't know the order of operations that well.
So, we're going to just test this using formulas, Evaluate Formula and what I'm hoping is that they're going to do that greater than or equal to before they do this plus it will evaluate there's the B11 there's a B7 a shoot.
So, if they've gone to the greater than or equal to next then this would work, but right now they are doing the plus sign so, they're gonna add those two together which is not what we wanted we're not going to get the right answer.
So, we're kind of stuck.
All right! So, before I throw over to Mike, I just want to subliminally suggest something here If you've been watching these podcasts for a while the dueling Excel podcast, I bet that Mike is going to come up with some sort of an array formula that would do this all in one big step.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Hey, you know this four logical test, I like that, I like the simplicity of it, but yeah typing in all those extra parentheses is a hassle.
Hey, run in formula evaluator I love that trick because when you're not quite sure what's going on formula evaluator will tell you and that, that's just flat-out genius.
All right, I'm going to stay simple here I still want to run all four logical test, but instead of putting them in parentheses I'm gonna put them inside of the SUM function.
So, we'll test the first hurdle are you greater than or equal to that, F4.
So, I'll make each one of these logical test, each one logical test will give us a true or a false check-in for each one of these hurdles, F4 on each one of those, but the cool thing about the SUM function is, the SUM function will take a true or false and by adding each one of these that's the math operation that it will convert it to a 0 or 1.
So, once I put it in there the SUM function will do the trick double click and send it down and then I could simply come to the end and say are any of you greater than 2.
So, 3 and 4 will be picked up as true.
Totally good, check the last one for the cell references it look like it's working good I don't know, but I'm going to give MrExcel the point here double negative because we need to convert the array operation relative cell references four are you greater than or equal to the hurdle and we're going to lock this one F4, right?
So, that I give us a bunch of trues and falses that double negative will convert them to 1's and 0's and MrExcel's formula absolutely perfect F2 and then I'm gonna say are you greater than 2.
All right, throw back to MrExcel.
Bill: Hey, all right Mike, absolute simplicity I love that putting in the SUM gets rid of all the parentheses and the formula I threw up there, that formula, that was not my formula.
That was my guess at what your formula would be that shows after I went through that the method that would normally use us like how the heck would Mike solve this problem.
And so, that was my guess so hey points all around.
All right well I want to thank everyone for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
