Learn how to use TRUE and FALSE functions to test whether specific criteria in your spreadsheet are true or false.
The TRUE/FALSE statement
What the true/false statement does is tell you if something is either true or false. Now to make this more exciting than it may actually be, I tell my students that this is Excel’s lie-detector machine. Excel can tell whether a statement is true or false, so you might say “how does it do that?” Well we’ll use the equals sign and we'll use a comparison operator and you'll say “what is a comparison operator?” I'm going to head over to this section here where I can take a look at the operators that are available to us. We have the equal sign so we can check to see if a1 is equal to b1. We also have the greater than symbol and we can check to see if one value is greater than another, less than another, or greater than or equal to, less than or equal to, or even not equal to.
Exercise 1 & 2
Let's take a look at exercise one and two we have two statements that we want to verify and check to see if that statement is either true or false. Well, the first statement said Peter earns a rate of 24 dollars so I'm gonna type the equal sign and I'm gonna go to the cell that contains his rate. Once I have that selected I will use my comparison operator to verify Peter's rate. that comparison operator is equal and what I want to check to see about. that cell is I want to check to see if that cell is equal to 24 so I'll type 24 when I press ENTER Excel says that is a true statement.
Somebody else or another statement we can look at that maybe set by someone else is Peter works in Ohio. Well, I'm not just gonna take their word for it. I'm actually gonna test to see if Peter works in Ohio so I'll type in equal now I need to go to the cell that contains his location or else I can't make the comparison. so I'm gonna go to the cell that's directly under State and then I'm gonna type the equal sign and then I want to see if it's equal to Ohio. Since this is text I need to use double quotes you'll need to remember that Excel is like a calculator it doesn't understand words so in order to make comparisons with text you have to put it in double-quotes. I'll press ENTER. Excel says that is false so at this point, if someone is sitting in the class they could very well raise their hand and thank me for wasting their time.
Why would they say that? Well, they might say to me “Garfield, I don't know if you realize this about me but I have these two things in my face called eyes and I can clearly see that Peter earns a rate of twenty-four dollars and that he works in Vermont I don't see why I would ever need to type an Excel function in Excel to tell me something that I can tell just by looking at the spreadsheet.” I would say you bring up a very valid point.
Let me show you how this might be useful because it's not gonna be useful just for one value. so if I head over to the right I want to compare values from year one and year two now in this first situation I want to see if the values are equal to each other. so I'll type in equal and I'll select year one value and then I'll check to see if it's equal to year two value when I press ENTER that is true. it's not a big deal but if I have a lot of values that I want to compare, one double click will be able to instantly tell me where the values are true and where they're false. all I would need to do now is do a filter on that table and I can easily pick out those values.
Let's say I want to do another type of comparison I'm gonna go to the equal to and I'm gonna click the drop-down here and choose greater than so the information we're looking for this time is whether or not year 1 is greater than year 2 so I'll type in equal I'll select year 1 again but this time I'm not gonna use the equal sign. I'm gonna use the greater than symbol and I want to see if year 1 is greater than year 2 when I press ENTER that, of course, is false because they're equal to each other but I'm gonna go over to that formula statement and double click on the black plus sign. now it's going to be very easy for me to pick out where year 1 was greater than year 2 and I won't have to look at every single value in those two columns.
Exercise 3 & 4
so if we go back to an exercise that we would conduct in class I would ask the students to work with exercises three and four. we want to see who is working full-time and who is working overtime so we're gonna need to make comparisons with the hours of the people working at the company and the criteria for full-time and part-time for the first exercise full-time is considered as any hours that are thirty-two or more so I'll type equal, I will select the cell that contains the total amount of hours for Mary the first employee and check to see if her hours are greater than or equal to 32. when I press ENTER Excel says that is not greater than or equal to 32. now I want to check for everyone else down the list I can double click everywhere I see true those are full-time employees. so if I wanted to send an email to them or make some kind of announcement I could filter for everyone who is listed as true and I don't have to worry about people who are categorized as false.
Now I want to see who worked overtime this might be necessary maybe we've recommended that people not work overtime so there are some people who have I may want to find out why so I need to make a comparison between the current amount of hours and the overtime hours. now overtime is considered as anything greater than 40 so I'll type in equal I'll select the hours for the first employee and check to see if they are greater than 40 and if they are then they are working overtime I'll press ENTER. Mary is not working overtime because she's only working 30 hours although over to the autofill handle double click now I see two truths in this list. so the two people working overtime are Greg and all so Doug. so now I can select them from the list and then send them an email if I need to because I can easily pick them out from 10 employees or a thousand employees. so that is the true/false statement it helps you to find criteria that match by creating a result that says either true or false based on that criteria.