1. IF: The IF function is a conditional function in which you want to set the value of the data according to a given logic. The IF function is =IF (logical test, value if true, value if false). For example,
=IF (C2SUM (E2:E9), SUM (D2:D9), SUM (E2:E9)): This is an example of a complex IF logic wherein the comparison is made between the sum of values in the range D2 to D9 and E2 to E9. If the former is greater than the latter, then it makes the value of the cell equal to the sum of values from D2 to D9 otherwise the value of the cell becomes the sum of of E2 to E9.
2. AND: Expertise in the different logical functions and the way to nest the functions for bigger and complex problems can only be achieved by practicing it, however even before that, the first step should be to get a good command over the concept. Excel tutorial videos on the subject could be of great help with regard to this. The AND function is used to assess whether or not the conditions specified in a test are TRUE. For instance, we may use the function to determine whether a value in cell B9 is more than 50 and less than 100. The syntactical way to write it is:
=AND (logic1, logic2, …)
where logic1 is a mandatory argument and logic2 is an optional one. It returns TRUE if all the logic is evaluated to true and FALSE even if one logic fails.
3. IFERROR: The IFERROR function provides a way to catch and handle errors. The way to write the function is:
=IFERROR (value, value if error)
where the first argument, i.e., value is the cell address the value of which is to be checked and the second argument tells the function what to output in case of an error.
4. OR: The OR function checks for returns true if any of the conditions is true and returns false otherwise. The function is written as:
=OR (logical1, [logical2], …)
where the first argument is mandatory and is the first condition to be checked and the second argument is optional and is the second conditional.
5. NOT: The NOT function can be used to return TRUE if a value is not equal to the other value and FALSE if it is. The syntactical way to write it is:
=NOT (logical/numerical)
where the argument is either the condition to check in which case it outputs TRUE/FALSE and if it is a number, it returns 0 or 1.
Information Functions:
The information functions are not that tough to understand. They are generally used for debugging cases and in the exploratory data analysis for data cleaning. Although, this article has very emphatically tried to explain the functions and their usage, watching excel training videos once would be more than enough to understand it.
1. ISBLANK: The ISBLANK function checks if a cell is blank and if it is the function returns TRUE and FALSE otherwise. The way to write the function is:
=ISBLANK (value)
where value is the cell, we want to check.
2. TYPE: The TYPE function returns the type of value. The syntax of the function is:
=TYPE (value)
The value argument can be anything, viz. number, text, error value, etc.
It returns 1 if the value is a number, 2 if it is a text and 16 if it is an error message.
3. ISFORMULA: The ISFORMULA function returns TRUE if the cell contains a formula and if it doesn’t, it returns FALSE. The function is written as:
=ISFORMULA (cell)
where cell the cell address, we want to check the value for.
4. ERROR.TYPE: The ERROR.TYPE function returns a numerical value according to the error value. Excel defines different numbers for different error types. The function is:
=ERROR.TYPE(error value)
where error value is the value, we want to check the type of. Some common outputs and their corresponding outputs are: 1 for #NULL! error, 2 for #DIV/0! error, 6 for #NUM!
error, etc.
5. ISNUMBER: The ISNUMBER function checks if the value inside a cell is a number or not. The function is:
=ISNUMBER (value)
where value is the cell address to be tested.