To create formulas that only return results from non-blank cells we can use the “ISBLANK” and “IF”formulasin Microsoft Excel 2010.
ISBLANK:This function can be used tocheck for blank or null values in a range.
Syntax of “ISBLANK” function: =ISBLANK (value)
Example:Cell A2 contains the textXYZ
=ISBLANK (A2), function will return“FALSE”
Cell A3contains the textXYZ
=ISBLANK (A3), function will return “TRUE”
IF:- IF condition allows us to use multiple conditions in a cell. It helps us to make the function flexible and Excel allows us to use a maximum of 64 conditions in a cell.
Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])
Example:Cells A2 and A3 contain the numbers 3 and 5. If the number is 3 the formula should display “Yes” otherwise “No”.
Lets take an example to understand. We have data in range A2:A7, in which some cells are blanks, and few cells contains text and some cells contain numbers.
To create a formula that only returns a result from the non-blank cells, do the following –
Another method to return results for non-blank cells is to simply use the IF function.
Instead of =IF(ISBLANK(A2)=FALSE, A2, “”), you can use =IF(A2=””,””,A2) or =IF(A2<>””,A2,””). The first option =IF(A2=””,””,A2) checks if A2 is blank. If it is blank, a blank cell is returned. If its not blank, the contents of A2 are returned.
Similarly, in the case of =IF(A2<>””,A2,””) check if A2 is not blank. If its true, A2 is returned, else a blank cell is returned. “<>” is used to denote “not equal to”.
Depending on the option you choose, you can then copy the formula to the rest of the column.
This is how we can create the formulas that only return results from non blank cells in Microsoft Excel.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.