An Easy Learning Source
Excel Tips and Tricks
AmeetZ Academy
As in this graphic shown here , we can get the list of different set of formulae. One of such set of formulae are Information Formulae. AmeetZ Academy
Why Information Formulae ? Excel's Information functions gives you information about the data in a cell or range of cells. This information includes whether the data is a number, the formatting applied to the cell, or even if the cell is empty. Each of these functions, referred to collectively as the IS functions, checks the type of and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical TRUE if is a reference to an empty cell; otherwise it returns AmeetZ Academy FALSE.
For Convenient Reading each formula colored differently
ISBLANK() ISERR() ISERROR() ISLOGICAL() ISNA() ISNONTEXT() ISNUMBER() ISREF() ISTEXT() CELL() ERROR.TYPE() INFO() ISEVEN() N() TYPE() IS EVEN() Important The "IS" statements only take one cell at a time, but not a range. AmeetZ Academy
ISBLANK() This Formula returns TRUE if the reference Cell is Blank . This formula is useful to find blank cell in a large range of Cells In other words - the IsBlank function can be used to check for blank or null values Please see example below: AmeetZ Academy
In this Example , in Case of A1 , the result is False since A1 is not blank , where as B1 is Blank
AmeetZ Academy
Using ISBLANK Function with other Formulae =IF(AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)), ”This Cell is Blank”,False) In the above Formula , we can find out , by using IF Formula with “And” Criteria , to notify Blank Cells from the large Range of Cells. AmeetZ Academy
Using ISBLANK Formula in Conditional Formatting In this example , I have tried to know how many Cells in the range of A1 to E2 are blank Procedure, As shown in the picture, I have opted Conditional Formatting-> Highlight Cell Rules->More Rules ->New Formatting Rules. Then I have opted for “Use a formula to determine which cell to format. I have used Formula =NOT(ISBLANK(A1:E2)) and selected the CELL FILL to be RED). In this Case If any cell contains single space also, it will be indicated with RED Fill. AmeetZ Academy
ISERR() In Excel, the this function can be used to check for error values. In other words , This function, Is Error Other Than Not Available, tests whether a value, an expression, or contents of a referenced cell has an error other than not available (#N/A) AmeetZ Academy
ISERROR() This function, Is Error of Any Kind, tests whether a value, an expression, or contents of a referenced cell has any kind of error.
AmeetZ Academy
Using , ISERROR with other formulae ( better usage of ISERROR) In this case, Search for a Name listed in the range F3:F11, within the range B3:B11. Return the Overtime for that name, listed in range C3:C11, and place the Overtime , in range F3:F11
. If we deleted any name from range B3 to B11, the result in F3 to F11 would be #N/A in respective cells because, the formula would not be able to find the name in range B3:C11. To hide the error value, the formula would be: =IF(ISERROR(VLOOKUP(E3,$B$3:$C$11,2,FALSE)),"",VLOOKUP(E3,$B$3:$C$11,2,FALSE)) AmeetZ Academy
ISLOGICAL() FORMULA =ISLOGICAL(TRUE)
DESCRIPTION Checks whether TRUE is a logical value (Yes)
=ISLOGICAL("TRUE") Checks whether "TRUE" is a logical value (No) =ISNUMBER(4)
Checks whether 4 is a number (Yes)
AmeetZ Academy
More will be in Part III, Keep Watching Also you can find these Power Point Presentations Are Available in http://www.ameetz.com/gallery.htm
AmeetZ Academy
AmeetZ Academy