There are no items in your cart
Add More
Add More
Item Details | Price |
---|
One of the most used logical functions in Excel, the IF Function, is used to check whether a condition is met, and returns one value if true and another value if false.
The IF syntax in Excel is
=IF(logical_test,value_if_true, value_if_false)
The above syntax has 3 arguments – logical_test; value_if_true; value_if_false; but only the first one is obligatory, the other two are optional.
Check Out | Excel Club
To understand the simple IF function, let us take an example.
Logical_test: In the example, a candidate has “passed” only if they score more than 40 marks.
Value_if_true: If the value is true – that is, if the candidate has been awarded more than 40 marks, the result is “pass”.
Value_if_false: If the value is false – that is, if the candidate has been awarded less than 40 marks, the result is “fail”.
Always Remember:
Example:
Condition: D8>40
IF formula: IF(D8>40, “Pass”, “Fail”)
When a situation arises where you need to do something specific if two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate the conditions. If the condition evaluates to FALSE, the result is changed.
The IF with AND function syntax is IF (AND (condition1, condition 2, …), value_if_true, value_if_false)
Let us take an example:
Logical_test: in the above example is that the student must have been awarded greater than or equal to 40 marks in all the 4 papers. Only if this condition is met, the student is considered to have passed.
Value_if_true: If the value is true – that is, the candidate has been awarded more than or equal to 40 marks in ALL the subjects, then the result is “pass”.
Value_if_false: If the value is false – that is, if there is 1 subject in which the candidate has not secured 40 marks (less than 40 marks), she is considered as “fail”.
The OR function is a logical function to test multiple conditions at the same time. It is used to compare two values or statements. The OR function can be used to extend the functionality of the IF function. It can be used as the logical_test for an IF function.
The IF with OR function syntax is
IF(OR(logical 1, logical 2, …), value_if_true, value_if_false)
Let us take an example:
Logical_test: The student must have received less than 40 marks in either the internals or the exam.
Value_if_true: The value if true – that is if the student has scored less than 40 in either the exam or the internal, then the result should be “Need to improve”.
Value_if_false: The value if false – that is if the student has scored greater than or equal to 40 marks in both the actual exam and the internal, then the result should be “Good”.
Always Remember:
When the IF AND function is being used all the conditions must be met for Excel to return the value as true. But in case of the IF OR function, Excel returns the value as true even if only one condition is met.
The Nested IF function is used when the user needs to test for more than one condition, then take one of the several actions, depending on the result of the results.
This function includes embedding or “nesting” one IF function inside another.
It allows you to test multiple criteria and increases the number of possible outcomes.
The Nested IF syntax is
IF(logical_test, value_if_true, IF(logical_test, value_if_true, IF(logical_test, value_if_true, value_if_false)))
Let us take an example :
Logical_test: The grading system given alongside was used as the logical test in the above example.
Value_if_true: Based on the above logical test, if the candidate falls into any one of the marking schemes, the grade will be awarded accordingly
Value_if_false: If the candidate does not fall into any of the marking scheme that is, she has scored below 40 marks the result will be declared as fail.
Generally, we type the logical test manually. Another way to add an argument to your logical test is via cell referencing. This makes it more flexible and easier – that is if you want to change the value of the argument in the logical test, you can just change the value in the cell you have given reference to and the formula gets automatically adjusted.
Remember, to always FREEZE/FIX the cell reference by adding the dollar sign ($) before the column and row number. You can also press the F4 key to freeze/fix the cell reference
In the above example, Cell E1 has been fixed with the numerical value 40
A logical operator is used in Excel to compare two values. The following table explains what each of them does and illustrates the theory with formula examples.
Operator | Meaning | Example |
Description |
= | Equal to | =IF (B2=5, “ok”, “change”) |
If the number in the cell is 5, the formula returns “ok”; otherwise it displays “change” |
> | Greater than | =IF(B2>5, “ok”, “”) | If the number in the cell is greater than 5, the formula returns “ok”; otherwise an empty cell is displayed |
< | Lesser than | =IF (B2<5, “ok”,) | If the number in the cell is lesser than 5, the formula returns “ok”; otherwise it returns 0 |
>= | Greater than equal to | =IF(B2>=5,, “ok”) | If the number in the cell is greater than equal to 5, the formula returns 0; otherwise it displays “ok” |
<= | Lesser than equal to | =IF(B2<=5, “Correct”, “Wrong”) | If the number in the cell is lesser than or equal to 5, the formula returns “Correct”; otherwise it displays “Wrong” |
<> | Not equal to | =IF(B2 <> 5, “Good”, “Bad”) | If the number in the cell is not equal to 5, the formula returns “Good”; otherwise it displays “Bad” |
Shreya Nayak