Operators in Excel
In MS Excel, operators are used to defining the operation you want to perform on the used elements or between variables. There are four types of operators: Arithmetic operators, Comparison operators, Text concatenation operators, and Reference operators. Comparison operators are most used after arithmetic operators while working in MS excel.
Let’s dive deep into the types of the comparison operator.
1. Equal sign
The "=" sign checks whether the two values are equal. Let’s consider the below-stated example.
STEP 1: Introduce two values in two cells
You have two values, namely a & b. you have to store that in a cell (also known as) memory space. Allocate them a cell name, namely A1 & B1.
STEP 2: Select a cell
Now you have to decide the operation to be performed and fix a cell to display the result. Let’s fix it as C1.
STEP 3: Enter the formula
- Enter the formula as =A1=B1. Every operation always starts with the '=' sign.
- Now, if the value compared is equal to each other, the result will be displayed as "TRUE", and if they are different, the result will be displayed as "FALSE".
2. IF FUNCTION
Not only numerical values but both the text and numerical values can also be compared and checked for results.
Following is the illustration of the IF functions.
- The above image shows that both the numerical value and the textual content can be checked in "IF “conditions.
- The general format for the if the condition is "=IF(VALUE1=VALUE2, "Yes", "No")
- On the 3rd value, we took both the text and a numerical value such as "ABC" and 32, respectively.
- In the 4th column, we took just the textual content as "red" and "red", which, when compared, is equal and gives the result YES.
3. LESS THAN OPERATOR
When used, the less than the operator gives the value "TRUE" if the first value is lesser than the second value.
Let’s look into the below picture to understand better.
- As the value in cell A1 is lesser than B1, the result is TRUE or else it will be FALSE.
- The complex comparisons can be made by AND operator. If in the given two values, you have to check both the value with a variable value, i.e. (your value that could be changed) AND operator is used.
- The syntax for AND operator is =AND (value1<40, value2<30). Here the variables are 40 and 30, respectively. If both the conditions satisfy the result will be "TRUE", or else it will be "FALSE."
- On the first Colum, the values are 67 and 23, respectively. Even though the condition (23 <40) is satisfied, the result would be FALSE because the other state failed. Both the conditions need to be TRUE for an AND operator.
4. GREATER THAN OPERATOR
This operator gives the value “TRUE “if the first value exceeds the second value.
In the above table, column two has 12 and 23, respectively. The outcome is FALSE because the condition =A2>B2 is not satisfied.
5. OR FUNCTION
The OR condition gives the value TRUE even if one condition is satisfied, and the result will be false if both conditions fail. The illustrated OR FUNCTION is given below.
In column 3rd, the values 12 and 13 both fail the condition "=OR (A3>20, B3>20), So the result is FALSE.
6. GREATER THAN OR EQUAL TO
The operator ">=" give output as TRUE if the first value is greater or equal to the second value. And otherwise, the work will be FALSE.
In the 3rd column, both the values are the same hence TRUE, whereas, in the 2nd column, the condition fails to give the output as FALSE.
7. COUNTIF FUNCTION
The countif function counts the number of cells on the given condition. Let’s take a condition of =COUNTIF (B1:B7,”>=5”)
On the given above condition, the cells are taken from B1 TO B7, and the condition is checked in between those cells.
The result is displayed in the B 8th cell as 3. The condition is satisfied by cells B1 and B2 B3, respectively.
8. LESS THAN OR EQUAL TO
The less than or equal to function gives the value TRUE if the first value is less than or equal to the second value. The syntax used is “<=”.
Since the value A1 is lesser than B1, the result is TRUE.
SUMIF function in less than or equal to is illustrated below
The sum of the column from A1 TO A5, excluding A5, is 62.
9. NOT EQUAL TO
The not equal to operator gives the value TRUE if both the values are not similar.
The two values shouldn’t be equal. Hence the 2nd column has the same values the output is FALSE.
Using of IF function with not equal to operator
On the above workout the syntax used is =IF (A1<>B1,"1","2"). If the condition satisfies, it will print the first value "1", or else it will print “2”.
CONCLUSION
These are the various comparison operators in existence. Even though it appears complex when a function and operator are combined, understanding the essential functions of AND OR IF is sufficient to perform comparison operations. It is widely by large organization for calculation.