Hi, this is Wayne again with a topic “Using the Powerful AND Function in Excel”.
In this excel tutorial, i’m going to show you how to use the and function, which is a great function. That requires multiple things to be true, in order for something to happen or to return the result of true, if those multiple things aren’t true, then it returns. The result of false, so let’s take a look at a couple of examples of this and how you might use it. You can see here in this spreadsheet. I have an employee list from a fictional company and let’s say the management of this company has decided to give a bonus to every member of the sales department that has at least five years in the company and has received a performance evaluation of at least six.
How would i do that? How would i calculate that automatically in excel well with the and function it’s easier than you would expect? I would just start here in f2 type equals and left parenthesis, and then i start putting in logical tests and every logical test that i put in here must be true in order to return the result of true. If any one of these is false, it will return the result false. So let’s begin and left parenthesis and then comes logical test number one b, two and i can just type that or i can click on b2 is greater than let’s say four. So if it’s greater than four it’ll be five years in the company or more now i put in the comma and my second logical test, which is performance evaluation that needs to be at least six or higher, and this time i think i’ll, just click so d2 Is greater than five and i put in another comma and now my third logical test e 2 needs to equal sales.
Now because the word sales is a word and not a number, i need to put this inside of quotation marks, so sales quotation mark and then i should put the right parenthesis. Sometimes you don’t have to do that, but i’ll do it anyway. In this case i tap enter and look. It looks like gina pullen.
She has been in the company more than four years. Her performance evaluation is higher than five and she’s a member of the sales department. Okay. Now the good news is.
I don’t have to recreate that same formula for each of the employees. All i have to do is click and drag this green square. The autofill handle just drag it down the page or the easiest way is just to double click on it. It copies it all.
The way down, as far as the data goes, and we can see that several people in this company have earned a bonus now, let’s take this to another level, let’s say instead of just true and false. What, if i want something to be printed here, something more meaningful like bonus or no bonus or certificate, or congratulations or whatever it might be, that might go into this cell instead of the very simplistic, true or false. How would i do that? Well, what we could do is use an if function and if you haven’t already watched my tutorials on the if function and also on the ifs function with an s at the end, i highly recommend that you do so there’s some very exciting things that you can Do with, if or ifs in this case, we’ll just use the if function so equals if left parenthesis and now we’re going to nest the word and inside of this, if function so i’ll put in the word, and so if and and then whatever i put in Next, everything here must be true, so if and left parenthesis, b2 is greater than four and and i’m putting a comma there now. Our second logical test, d2 is greater than 5 and i put another comma e2 is equal to and in quotation marks sales. Now it’s important at this point to put in the right parenthesis.
I really have to do it this time and the reason why is because that now closes off the nested and function and the formula that goes with it, so that part is done now. I need to go back and finish off the if part of the formula, so if all of this is true, then what the way you indicate then in this case, is you put a comma so then i want to print so i’ll. Put this in quotation marks the word bonus now, of course, this isn’t going to print it on a printer, it’s going to print it on the screen in this cell.
Now, what if it’s not equal to bonus? I could just stop the formula here with the right parenthesis: tap enter and you can see it says bonus if i double click on the autofill handle it copies it down, but it still says false for those that haven’t earned a bonus. So if i want to change that, i could add another comma after bonus and let’s read the formula: if each of these things are true, then print bonus and then the next comma signifies, if not then print what how about nothing? And i put that in quotation marks, tap, enter and then i’ll copy that formula down by double clicking on the autofill handle, and now we can see some people have earned a bonus. Some people have earned nothing, but i hope you can see the power of the and function. It’S a way to require that multiple things be true.
If they are true, the word true is printed. If they’re not true, the word false is printed. If you want it to be something else besides, true false, you can use the and function inside of an if function, and there really are some brilliant things that you can do with this and creative useful ways that you can use them in your spreadsheets. Thanks for watching, i hope you found this tutorial to be helpful.
If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you want to support my channel, you can do that through my patreon account, or by buying channel merch and you’ll, see information about those options. In the description below this video speaking of patreon, i want to give a quick shout out to my five dollar patreon supporters.
Thank you. So much for all you do to support my channel. .