About the #DIV/0! error
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or...
The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas...
FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars.
The LEFT function then extracts characters...
At the core of this formula, we build a list of row numbers for a given file. Then we use the MAX function to get the largest row number, which corresponds to the last revision (last occurrence) of that file.
To find...
The SUMPRODUCT function accepts one or more arrays, multiplies the arrays together, and returns the "sum of products" as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the...
Working from the inside out, the ISERRROR function returns TRUE when a value is a recognized error, and FALSE if not.
When given a range of cells (an array of cells) ISERROR function will return an array of TRUE/FALSE...
The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel...
The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the "...
In this example, the goal is to count the number of cells in a range that do not contain errors.
Working from the inside out, we first use the ISERROR function on the entire range:
ISERROR(B5:B14) // check all 10...
About spilling and the #SPILL! error
With the introduction of Dynamic Arrays in Excel, formulas that return multiple values "spill" these values directly onto the worksheet. The rectangle that encloses the values is...
The #NAME? error occurs when Excel can't recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a...