%20(11).png){kind=small}
The logical functions are used most widely from Excel's thousands of tasks. A function is logical if a specific condition is applied to it, say, If the cell has a value greater than 10 and returns more or less. Excel logic can be applied using different functions, conditional formatting, or VBA programming.
Here are five important Logical functions in Excel:
If function
The IF function in Excel is Excel's most widely used logical function. The syntax for IF function is: =IF(logical_test, [value_if_true], [value_if_false]). For example, every student scoring above 50 is pass and below is failed. So the formula, in that case, will be =IF(cell_ref>50,” Pass”,” Fail”).
SumIF function
The SUMIF function is used to sum specific cells' values if the required condition is met. The syntax for the SUMIF function is: =SUMIF(range, criteria, [sum_range]). Suppose you want to add all the numbers in a selected range with a blue in their corresponding cell. The formula will be =SUMIF(cell_ref_range,” blue”,sum_range).
CountIF Function
The COUNTIF function counts the number of specific cells if the condition required is met. The syntax for the COUNTIF function is: =COUNTIF(range, criteria). Suppose column A has different color names in each cell, and you want to count the number of Blues so that the formula will be =COUNTIF(cell_ref, “blue”).
And Function
The AND function determines that all the conditions specified in the test are true. The syntax for and function is, =AND(logical 1, [logical 2],…) If all the logics specified are met, the result is given a true value or else false. The AND function is usually used with the IF function in Excel. Let us suppose that a number must be greater than 20 and less than 40; in this case, the formula will be =AND(cell_ref>20,cell_ref<40) when a cell meets both conditions, TRUE will be given as an output or else FALSE.
OR function
The OR function in Excel tests multiple conditions in a cell. Even if one of the conditions is met, the answer is true or else false. The syntax for IF function is, =OR(logical 1, [logical 2],…). The OR function is mostly used along with the IF function. Let us suppose the cells in a column have different color names. We want all the cells with BLUE and PINK in that case, the formula will be =OR(cell_ref=”pink”,cell_ref=”blue”). All the cells with pink or blue as a color will have true as an output, and others will have false.
To be an expert in Excel, you must have a logical mind and a good grip on Excel's logical functions. Try using the above 5 logical functions in Excel, and let us know in the comments section how much did the article help you.
Comments