Join Dennis Taylor for an in-depth discussion in this video Using the AND and OR functions independently or within IF functions, part of Excel Tips Weekly.
- [Tutor] There are two logical functions in Excel, one's called and and one's called or, they're frequently used with the if function, but they can be used in a standalone fashion, in this particular worksheet, there are headings on columns H and I and you can see what we're trying to do here, we simply want to find out if different conditions are true, now we might eventually be using an if function here to come up with a bonus amount possibly, but let's first focus on this idea, did this person exceed his or her sales quota, so we could simply put in the function equal is this Actual right here, this is the actual sales amount, greater than this amount, it's pretty obvious in this case, it's not, so what kind of an answer do we get? False, of course in some situations, it will be True, I'm going to double-click to copy this down the column, it's over 600 rows and if I wanted to zip down to the bottom real fast, I'll press Control+Period down to row 667 and of course you see the various True and False entries and again Control+Period to zip back up top.
Now, let's combine this with the idea, as we see in the heading, did this person exceed the Quota and does he or she have more than 10 Years of service? So we embed this within the and function and when you're typing function names, you don't need to capitalize them, after we've finished the function, this will automatically become capitalized, so equal and, this can be followed by any number of different entries, I believe the maximum is something like 127, so we want to check to see if this is True comma and if the Years of service, that's in cell F2, is 10 or more and we can do this in one of two ways, greater than, nine is one way, perhaps slightly clearer, although it takes up more screen space, greater than or equal to 10, that's the way we write it, when you're using a function by itself and it's not nested within others, you don't need to type in that closing parentheses, I will press Control+Enter instead of Enter, so the active cell doesn't move downward, I think you can see in this case, that these are not both true, in fact one is true and one isn't, but the and function says only when all the conditions are true will this be True, in this case, it's going to be False, I haven't copied it into the next cell yet, we can see there, in both cases, it fails, next one down on row five, Actual is greater than Quota, Years of service is 10 or more, but we haven't copied this just yet, so again, we're taking the expanded function, I'll double-click and copy down the column, so this is the first case, where both conditions are true, now we can do other things with this, but for the moment, we want to know, now although it's not that important necessarily just yet, you might want to tabulate how often False appears, so off to the right, we could use the function called countif and we're looking in column H comma, we want to see how often the word False appears, of course, we would also be interested in how often the word True appears too, so either one, at least initially and I'll press Control+Enter here, 305 occurrences, I'm going to copy this downward into the next cell, for the moment, that's a duplicate, but I want to change False to be True, the total of those we see at the bottom of the screen in the Status bar, 666, earlier you saw there were 667 entries, so the title on row one isn't counted there, we've got that information.
Now, off to the right, notice this heading, it's almost the same, with the obvious difference being the word, or is used instead of the word, and, so I simply could take this data here, I'll copy all this with Control+C, Escape, click here, press Control+V and make sure this reads equal or, the companion to and is or and the logic of course will change, but it means the same thing it would mean in English, if either of these conditions is true, we're about to see True, so on row two here, because the Years of service have got to be greater than or equal to 10, this will be True, remember either condition is true, double-click to copy this down the column.
Now possibly we're going to be giving bonuses based on these, but let's see what these totals are going to be, I'm simply going to take these entries here and with the right mouse button, drag them down here, let go of the right mouse button, copy here, make a change and now we're looking for the moment in column I, highlight those, click column I and Enter and then down here, column I, once again, click in column I, so a lot more Trues, if we use the function called or, when either condition is true.
Now if we're trying to use one of these as the basis for a bonus perhaps, we probably would rely upon the first one, something like this, so what if we changed this to, in fact, we change the heading eventually to say, when this is true, we want to provide a bonus, so it's not uncommon to see the and function and sometimes the or function used within an if function, so when this condition is true comma, then perhaps we'll provide a bonus, now we could do any kind of calculation, maybe to make it simple and the example here will simply say, okay, these people get 3,000 dollars comma, when not true, zero and we do need to put in the right parenthesis there, I'll press Control+Enter, double-click to copy this down the column or better yet, let's copy just a few of these, the fourth entry here, the one in row five, we will see a number there, we don't see a number in the others, not in the next cell, but in the next two and so on, so last cell here, just double-click and as I click column H, something else we want to do is to tabulate the total of the entries here, but we don't have to write a formula, because in the Status bar at the bottom of the screen, you'll see a total, it's not formatted, we could format column H, Home tab, here's the comma button, we probably don't want to see the decimal, so we can decrease that and now the totals in the Status bar are a bit more reasonable, we see a million and 83,000, that's not truly necessary to have done that and by the way, on the Status bar here, if you're not seeing these entries or if you want to see more potential entries, right-click in the Status bar and amongst these six choices here, maybe check them all, that'll be your call as to whether you need this information, but once you make this change, you might not come back here and change it again, I usually leave these all checked, at different times, I might turn one off, but for the most part, it does no damage and any time you highlight two more cells, it's going to be viable information at the bottom of the screen.
So now we're using the and function within if, now we could make another change here too, what if we wanted to provide a bonus for people, who might not have reached the Quota here, but maybe they're within 95% of it, so let's say we change this a bit and use the or function initially to say the following, if you have exceeded the quota based on your sales comma you're going to get 3000 dollars, now we don't necessarily put that in right now, although we could, but let's say if that is true, you're going to be getting the bonus, but if not, you might get the bonus for another reason and that other reason could be you've been here 10 or more years comma and this Actual here, the actual sales happens to be greater than 95% of the Quota and sometimes when you're writing formulas like this, clicking on cells, you will get a notice like this, we're not quite finished with this, 95% of asterisk the quota, so, and another right parenthesis, now, it's a bit more complex now, but what are we saying? You will get the bonus for one of two reasons, either your sales Actual exceeds the Quota, that's going to work just fine, you'll get your 3000 dollars, if that's not true, then if these two conditions are true, so in effect what we're saying is the or function here is giving us two options, if either one is true, remember sometimes the or function and sometimes the and function can have multiple entries within here, multiple arguments they're called, sometimes people call them parameters, so if either of these are true, you're going to get 3000, so I'm going to press Control+Enter here and in this case, it's not true, that's not 95% of that, even though Years of service are okay, but some of these others will change, I'll drag this down slowly, this one changed, the reason here is this exceeds the quota, earlier we had the requirement that you had to have the Years of service, being 10 or more at the same time, so I'll copy a few more down there, will be some changes, more people are going to get the bonus and again, it will vary as to why, in some cases like this one here, sales are not exceeding the Quota, but they're certainly within 85% of it, in fact almost the same and the Years are plenty above that 10 number, so completing this to copy this all the way down the column, I'll get a few more cells potentially and of course we could have copied this down the column from the beginning, double-click and now how much will the company be spending on these bonuses? Click up here and as we look in the Status bar, one million 698,000, so you can imagine all kinds of variations on how we use and and or, sometimes within the if function, sometimes not and keep in mind too, when you're using these by themselves, typically you will see the answers like True and False and they can be tabulated, as we saw earlier with the countif function, so these are valuable tools in working with your data and coming up with different answers, depending upon how you use and and or and if.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.