Formula To Stop Error Messages

// Formula To Stop Error Messages

Formula to stop Error messages

I have a formula that returns an error message. Can I get it to display a blank cell instead?

By Neale Blackwood

Let’s assume that cell A1 has some text in it and B1 has a number in it. A1 sometimes contains numbers and then we need to multiply A1 by B1. The formula =A1*B1 will return a #VALUE! error message when there is text in A1. To avoid this here are three solutions that apply to all Excel versions.

=IF(ISTEXT(A1),””,A1*B1)

=IF(ISNUMBER(A1),A1*B1,””)

=IF(ISERROR(A1*B1),””,A1*B1)

Note: “” displays a blank cell.

The third solution will stop all errors showing. Eg if cell B1 contains text, the first and second solutions will display the #VALUE! error message, whereas the third solution would still display a blank cell.

Because the second solution is used so frequently, a new function was added to Excel 2007 to handle it more elegantly. In Excel 2007 the third solution can be written

=IFERROR(A1*B1,””)

This formula displays the result of A1*B1 unless there is an error, when it will display a blank cell. This saves having to duplicate the formula.

Warning: Because IFERROR is a new Function it cannot be used in previous versions of Excel. Cells with the IFERROR function will display #NAME! error message in Excel 2003 and previous versions.

2017-03-08T03:53:58+00:00Functions|