dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Excel’s #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL! and #N/A Error Warnings

Understanding the significance of warnings sometimes posted by Excel formulas, how to adapt your formulas to accommodate them, and how to anticipate some potential hazards that may arise from them.

Last updated on 2018-12-02 by David Wallis.


Preface

There warnings that Excel displays when it cannot evaluate a formula for one reason or another are illustrated here in Column C:

Excel error warnings

On the How to Deal With the Excel #DIV/0! Error page you’ll find explanation of the IFERROR function in the formula in Column D.

Adapting your formulas to respond to such warnings is important if you want to ensure that your spreadsheets are calculating as you require them to.


The #VALUE! Warning

The cause of this warning can be hard to track down, but these are common causes of the #VALUE! warning:

To illustrate the #VALUE! warning at work in aritmetic calculations, in this spreadsheet Cells B3, B4 and B5 are empty, and Cells B6 and B7 contain the formula =NA():

Excel VALUE warning

Hmmm! I’m comfortable with what Excel is telling me until I get down to Rows 6 and 7. What the logic is in returning the different warnings, I’m uncertain about. This example highlights why you need to test all likely outcomes of formulas, lest thay have unanticipated knock-on effects in your spreadsheets, possibly leading to incorrect results.

Here I’m running a test to understand how Excel might respond to some functions I want to use in a report summary:

Excel VALUE returns

The SUM, COUNT, AVG, MAX and MIN functions in the formulas in Cells C12 to C16 all reference Cells C2 to C10.

That the COUNT function should be happy to ignore the warnings is a puzzle to me, and as such puts me constantly on the lookout for anomalous behaviour of any of my formulas.


The #REF! Warning

When a formula cannot locate a cell you have addressed in your formula, that formula will return the #REF! warning. For example, consider this spreadsheet in which everything is hunky-dory:

Exce REF formula

Until some vandal deletes Column A:

REF warning

Note that the warning appears within the formula itself as well as appearing in the cell as the result of the formula.

A flawed VLOOKUP formula can return a #REF!, as in this example:

VLOOKUP warning

The VLOOKUP doesn’t like to be asked to find the value in the fifth column of a lookup table that contains four columns, A2:D5, only.


The #DIV/0! Warning

The #DIV/0! warning results from any attempt to divide by zero and is the subejct of How to Deal With the Excel #DIV/0! Error page.


The #NAME? Warning

Here are some possible causes of the #NAME? warning:


The #NULL! Warning

Possible causes of the #NULL! error warning are these:


The #N/A Warning

These are some causes of the #N/A warning:


Give DMW’s Tips Your Support

ContributionIf you intend making use of my tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.

“My beautiful proof lies all in ruins.”

Georg Cantor (1845 – 1918)