Using logical functions provided by Excel 2010, we could apply and evaluate the specific rationale on the data. The most commonly used logical functions are AND, OR, and NOT. They take values as arguments to apply a simple logic over them and yield TRUE and FALSE result. The logical function helps users in situations where they want to categorize the value into groups, so that further complex functions can be easily performed. For example, if you want to check two different values that belong to different fields against a condition, you can use the logical function to check where the required condition is met. This may assist you in filtering out unwanted values and in focusing on only those values that you require. Additionally, Excel 2010 lets you use the multiple logical functions to check required conditions for the data set in one go. In what follows, we will demonstrate the usage of AND & OR logical functions.
To get started, launch Excel 2010 spreadsheet, containing the data set on which you want to apply logical functions. For illustration, we will apply AND & OR logical functions in a spreadsheet containing fields; Name, Course, Marks, Grade, and Status, as shown in the screenshot below.
Now we will apply logical operations over different fields. Since we want to find out that how many students registered HTML & Scripting course and secured 60+ marks, we use AND function. This function returns TRUE if both specified conditions are met.
If student studied HTML & scripting and secured marks greater than 60, only then the condition is said to be TRUE.
The syntax of the function is;
We will write it as;
=AND(C2=”HTMl & Scripting”,D2>60)
C2 and D2 in the function arguments are the location of the cell from where we started searching. The output True can bee seen in the screenshot below only where both conditions have met. Conversely FALSE will appear where any of the condition is not met.
Now we are going to find out the row against either Marks less than 60 or Status is fail. For this we will write OR logical function as;
Where D2 and E2 are the locations of the cells in Marks and Grade field respectively. As TRUE can be seen against all the values in either field Marks or Grade where marks are less than 60 or grade is D.
You can also check out previously reviewed Excel function; DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, and LEN.