00:00 Hi, so one of the most simplest and useful formula combinations is using and and or inside of an if and what happens is we want to use this when we want an if formula to have multiple criteria.
00:17 This is pretty simple in the situation. I have created here and I hope that this walking through this one, I'll show you the formula combinations of the using and or or and and or in if that's all weird.
00:34 But also actually walk through creating these from scratch. So when let's call it. Talk about this situation. We have names of sort of customers in our CRM.
00:46 We have statuses. In this particular case, we have sent sort of we've initiated that customer. We have a pending where it's in some type of progress and then we have two completion events.
00:59 We have a lost like it is done there. They will not be a customer of ours and one, they are a customer of ours.
01:06 And then we have the agent here. Now when dealing with just statuses, it's pretty simple. You can see those and you can see, you can use sort of a counts count if count ifs.
01:17 But when we add this agent field where we now have every other organization, in that case, we put the agent field on the outside we put agent field on one bit of the one bit of the of the product.
01:41 We ask these questions, but we don't want to have to go to individual files and sheets. We sort of aggregate everything, and then we can ask these big questions like, did Alex win the deal?
01:52 Meaning, is the status one and did Alex is is the agent. We have three agents here Pam, Alex and Fred.
02:00 But also. To have the aggregate kind of a date of like just is the deal done? Do we need work or not?
02:07 And now in that case, it's could be either a status of lost or one. And that's all we want to know is if it's that or not.
02:16 If it's not either lost or one, we know it needs to be needs more work. Done. So we'll classify that as needs work and if it's either lost or one, we'll have a done deal.
02:26 So we just want to know what's in the big pipeline? What's the whole total thing? But also we might want to get even more granular and say, okay, one or loss.
02:37 We don't really care. We we we do care if we get new. Customers, but we want to know are our agents completing deals?
02:45 Meaning are they getting to a closing statement, either lost or one? It doesn't necessarily matter in this one particular analysis.
02:52 We just want to know are they closing or are they keeping for instance? You might find that there's a lot of customers in the pending call pending status and you're like, well, are our agents just scared to mark those as lost and move on with their lives?
03:11 In that case, we're spending a lot of resources in the pending when really they should be lost and just closing the deals.
03:18 So we want to know, dude, does Pam close deals? Is it lost? Or one we just want to know if it's lost.
03:25 So we'll go through those and here I have them already done and we'll walk through each one as we go.
03:31 Here we have Alex one. Yes, the status is one and Alex is the agent and we can scroll down more and see here one and Alex.
03:39 We can also see the two statuses here. Needs work meaning it's in cent or pending and done deal lost or one.
03:48 So we have this correct. And then we have Pam's done here. Which means it's either lost or won in the status column and it's Pam as agent.
03:58 Again, we can change this to any agent and we'll do that later. Okay, I've, deleted the actual formulas here so that we can walk through building them individually.
04:08 Okay, so the question is did Alex win the deal? We need in the B column one status and we need in the C column it is Alex as the agent.
04:17 So we're gonna use if in our function or formula here. We'll use the if. You can see it there. And in if we're gonna go we're gonna actually use the and formula right away.
04:35 So this is the one key thing that is difficult to realize. You might start typing in like B2 is equal to, to one.
04:46 We'll get that right. And be like, oh, I need to add something to it. What you need to do is wrap everything in the if with and for this particular example.
04:56 We're gonna do and, and we're gonna use a comma separator here. And we can put any amount of criteria. In this and and all of them must be true in order for the if statement to be true.
05:09 We want also C2 is equal to and we're gonna type in Alex there. Okay. Now we're gonna end the end parentheses and in the end here this B2 C2 is one.
05:24 Inside of the if. We have two more items which are what happens if it's, if this expression becomes true or false.
05:32 So we're gonna add this comma and we're gonna say what, what do we want to say here if the value is true meaning that B2 is one and C2 is Alex.
05:41 Well we want to say Alex has, one, the deal. Something like that. Some, some sentence or some text that's different than say the status is itself.
05:50 And we also want to have some, do we want to have some value if it's false. In fact we don't.
05:56 We, we just want to know did Alex win the deal? Yes or no. And so we could put here yes and we, We also could put no.
06:07 And we'll do that right now to see. Okay. We'll say no, no, no, no, yes. But I find this very difficult to parse through.
06:16 So what I recommend in this particular case, just to make it a little easier to see between the yes or no.
06:24 Because it's one character difference is to have a sentence here. Alex has won the deal or some phrase. Guppy. I don't know.
06:35 And then in the false part of this, we can actually do nothing. But we do have to have that comma.
06:41 But we can have absolutely nothing. And what happens? Let's go. Copy this all the way down is now we can scroll through very easily and see, okay, Alex has won this deal.
06:51 Alex has won this deal. And there we go. One key thing here that you might not have noticed, but not, or at least I didn't mention, is that in the first two row, the first row of data, I put B2.
07:05 And then I copied and pasted down, or I use the, that very simple command enter that will automatically add all of them here.
07:14 And each one will change. So this B2 and C2 are relative and it will change as it goes down. If you want to use this in another, column, what you could do is add a dollar sign in front of the B and a dollar sign in front of the C.
07:31 And now you can use that same exact formula inside of another column. Or what's gonna happen if you don't do that, let's delete this dollar sign.
07:46 If you don't do that, the column and the columns will change. C to D, that changed from B to C.
07:54 Okay. So now we have, did Alex win the deal? Yes or no? And we have our answer. But is the deal done?
08:00 Meaning in this status column this is it lost or won and nothing else. We just care about- those two. Well, we're gonna do if again.
08:08 And in the same structure where medical or syntactual structure, we're gonna do or around whatever's inside of our if function at least the first item.
08:19 And we're gonna do B to equals lost and a comma B to- two equals one. And now we have a or inside of the if and inside of the first part of the if.
08:38 We do a comma, we say, okay, what happens if the value- value is true right now? Just by adding this comma, it's giving us a little hint.
08:46 It's saying this- right here is false. So we're- we know we're right. If we go- let's go to this one.
08:53 And- oh. Did not- did not uh- I did not copy it so we can write it again if or be- we don't want B2 now.
09:07 We want B9 is equal to 1 and comma B9 is equal to lost and then we end parentheses and we hit this comma and now we see nothing.
09:24 It's- it's- what is the value if it's true? We don't have any- nothing here. So let's see what's yes, deal, done.
09:32 Now it's showing us a key, a little hint. It's saying right here, deal, done. It's saying, oh, it is full.
09:39 Sorry, it's not false. It's true. And we can write what is it that we want to say that needs some work.
09:48 Okay, and end parentheses. And there we go. We can copy this all the way up and down. And it gave us that hint.
09:55 That's really helpful when you're writing this and you're like, is this the right thing? Is it going to, is the value going to, the logical expression going to come out true or is it going to come out false based on.
10:08 And if we have this little hint, it shows us, yes, this is the right thing to do. We want it right.
10:13 And it also tells us if we're wrong. And so now we have a clear classification of these different statuses of saying, you know, deal done or need some work.
10:22 Now, how do we combine or an and. So we want to know we want to know, okay, it is one or lost.
10:33 Yes. And that PAM is the and that PAM is the agent here. So how can we do that? Again, we can, we start typing if.
10:43 Now in our logical expression, we want to start with and. We want some. We want or an inside of or we want the same thing we just did before.
10:54 B two equals one comma B two equals lost. We are going to then. Add. A comma. So end the parentheses for the or at a comma in the and for Pam.
11:16 So that's C two is equal to Pam. And so now we have it's either one or lost and Pam is the agent.
11:28 And so. We go comma, Pam completed. And we see already that it gave us a hint that this is false, which is absolutely correct.
11:40 It is both sent and Fred. So neither Pam nor water close. So that's great. We're on one. We're on the track to correctness.
11:49 And if. Nothing we just can put nothing there. And let's copy paste this down and we can see. Okay, we have our completed and or and or inside of if we have blanks here, which is good.
12:02 We have lost one Pam Pam. This is perfect. Pam completed and we can look down and double check anything else.
12:09 Okay. One Pam lost Alex. See, this is not true. Perfect. So now we have used both and and or inside of the if.
12:20 Hopefully that was a helpful helpful for understanding the combination of and or inside of if. It could be a very it could be very complicated.
12:34 If you aren't familiar with the logical expressions of and or and how they come true or false do look up that logical expression.
12:43 They hold true here with the and in the or meaning in and everything must be true for it to be true.
12:49 Any false will be. False with or any of them need to be true for the thing to be true. And for if it only is going to come out, if it's a true or false if you have some other thing, you need to say, does that thing equal to whatever the thing you want?
13:05 So it needs to come out to be true or false. In order to be the logical expression.