IF and ISBLANK: Controlling Whether a Calculation is Made or Not

There's a way to tell Excel to only perform a calculation if there are actually values for it to work with.

As an example, someone once sent me their invoice template in Excel to fix up. The total column was multiplying the number of items purchased times the price of that item. With nothing filled into the invoice yet, the totals column was just a line of zeros going down the 15 rows where you could enter items. That means that if you only entered two items, you'd have to delete all those extra zeros or just leave them there (which is sloppy). Some people work around that by using conditional formatting to turn zero value white (on a white page, the white font is invisible). I'd suggest using IF and ISBLANK together to only perform the calculation if there are actually values to work with. Otherwise, have the cell remain empty.

Using this same example, assuming your totals are in column A, the price is in column B, and your totals are in column C, enter this formula:

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"",A2*B2)

It's basically saying: if A2 is blank or if B2 is blank, put nothing (""). Otherwise, multiply A2 by B2.

This ensures that the calculation only takes place once there is both a number of items and a price. Unless you really need all those zeros, this is a great way to help clean up your spreadsheet.

0 comments:

Post a Comment