The excel ISBLANK function: YOUR OBJECTIVE
You’ve been asked to develop some important information for your company, namely how long it takes to process orders. To this end, you were given an Excel table in which are listed: order numbers, the date each order was received and the date each was shipped. But as soon as you use a simple equation to calculate the number of days between those two dates, you see that for orders not yet shipped (no order date entered in the table), absurd results appear. You can prevent such unsatisfactory results from being displayed by using Excel’s ISBLANK function.
In this learning module, you will be shown how to use Excel’s ISBLANK function.
HOW DOES EXCEL ISBLANK FUNCTION WORK?
The excel ISBLANK function examines the contents of a given cell. If the cell is blank, it returns the logical value of TRUE. If the content of the cell is anything but blank, the function returns FALSE. This function can be very effective when combined with an IF function.
In the current example, absurd results are returned when the delivery-date cell in the list is empty. You therefore want each cell in column D to remain completely empty unless both cells in columns B and C have content. Otherwise, you want the order processing time to be displayed as the number of days between the dates in columns B and C.
To create a function that will solve this problem, you can use an IF function together with a nested excel ISBLANK function. Begin by clicking in the first cell that is to display the result, in this case D4. Then start the Function Wizard by clicking the button just to the left of the formula bar. Now find and select the IF function in the Logical category. After clicking OK, the Function Arguments dialog box will appear. Click in the Logical_test input box, and then click the small black arrow next to the Name Box (shown below). Click More Functions, the last entry in the selection list.
Now find and select the excel ISBLANK function in the Information category. A new Function Arguments dialog box opens for the excel ISBLANK function. Enter C4 into the Value input box. If you like, you can type in C4 manually or just click once on cell C4. Do not click OK now.
You must still finish entering the arguments for the IF function. To do this, click the IF function in the formula bar. The Function Arguments dialog box for the IF statement will reappear with the Logical_test argument already filled in with the excel ISBLANK function. Now click in the Value_if_true input box where you should enter two quotation marks without an intervening blank. These two quotation marks signify an empty cell. In the Value_if_false input box, enter: C4-B4.
To finish your function, click OK. The result will appear in cell D4. If you select D4, you will see that the formular bar now shows the completed function:
This can be read as follows: If cell C4 is blank, insert a blank in cell D4; otherwise calculate C4-B4 (i.e., Shipped on date minus Order received date).
Use AutoFill to copy cell D4 down to cell D24.