Back to Excel Newsletter Archives Back to Excel Newsletter Archives
DEBUG / EVALUATE FORMULA ERRORS
As soon as you have discovered how to use formulas in Excel, you will likely need to lean how to correct or hide formula errors. The first thing you should know is what each error type means. Once you understand what each error value means, correcting the formula becomes a LOT easier. Also note that a Formula can return an error IF a range it references contains an error cell.
To mask errors and return an alternate value in its place, it is best to return zero rather than empty text (""). This is because zeros are generally more downstream formula friendly than text.
HIDE ZEROS
To hide zeros on the Workbook level go to Tools>Options>View - Zero Values.
Custom Formats
Excel sees a cells format as having four Sections. These are, from left to right:
Positives;Negatives;Zeros;Text.
To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired the format for non zeros. Note the use of -0.00 for negatives.
#NULL!
Often occurs when you specify a intersecting range which in fact does NOT intersect. The space is the Intersect Operator and should be used correctly like;
=A1:F1 B1:B10
OR with named ranges
=Range1 Range2
In both cases Excel will return the cell value that intersects A1:F1 and B1:B10. In this case, B2.
However, if we used =A1:F1 B2:B10 Excel would display the #NULL! error as it is NOT possible for a row 1 range to intersect a column range that starts at
row 2.
#DIV/0!
Simply means you cannot divide zero into a number. For example
=A1/A2
would result #DIV/0! IF A2 contains nothing or zero. To correct this one could use one of 2 methods.
=IF(A2=0,0,=A1/A2)
OR
=IF(ERROR.TYPE(A1/A2)=2,0,A1/A2)
Note the use of the ERROR.TYPE Function. It is important to identify the error type so you are NOT masking another error type you SHOULD know about.
That is, we could use;
=IF(ISERROR(A1/A2),0,A1/A2)
OR
=IF(ISERR(A1/A2),0,A1/A2)
BUT, it is NOT good practice as you will end up masking most error values
when you SHOULD be masking only the #DIV/0! error.
Error.Type Function
For specifying error types. #NULL! = 1 #DIV/0! = 2 #VALUE! = 3 #REF! = 4 #NAME? = 5 #NUM! = 6 #N/A = 7
#VALUE!
Possibly the most frequent error type. Occurs when the wrong type of argument or operand (operand: Items on either side of an operator in a
formula. In Excel, operands can be values, cell references, names, labels, and functions.) is used. For example, you may have;
=A1*A2
and IF either cell had text and NOT numbers, the #VALUE! error would be displayed. This is why one should NOT change the default horizontal alignment of data cells. That is, text is always left aligned while numbers are right aligned by default. If you allow this and then widen a Column, you can tell at a glance what Excel is seeing as text and numbers.
#REF!
This means a non-valid reference in your formula. Often occurs as the result of deleting rows, columns, cells or Worksheets. This is why deleting
deleting rows, columns, cells or Worksheets is bad practice. Also check named ranges if used.
You DO NOT want to mask this error as you SHOULD be aware of it.
#NAME?
This error means a Function used is not being recognized by Excel. Check for
typos and always type Excel Functions in lower case. This way, when you
enter the formula Excel will automatically convert it to upper case, if it
is recognized.
Another common reason is if you are using acustom function without the code being present in he same Workbook. Or, you are using a function that requires a specific Excel add-in being installed. E.g the Analysis Toolpak
On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.
As with the #REF! error, you don't want to mask this error.
#NUM!
This error occurs if you supply a non valid number to a function argument. E.g, using a negative number when a positive is needed. Or, using a $, %
symbol with the number.
This error can be masked so long as you are aware of the reason why. Again, use the Error.Type function as shown in #DIV/0!
#N/A
The most common reason for this error is any of theLookup functions. It means Excel cannot find a match for the value it's being told to find. There
are many ways to correct or mask this error out there, BUT most are wrong in their approach and force a LOT of unneeded over-heads.
Consider placing the Lookup functions on the same Worksheet as the Table (if not already), then create a simply reference (e.g. =IV1) to the cell(s) to
get the result into the needed Worksheet. Doing this also opens up another opportunity in that we could now use;
IF(ISNA(IV1),"",IV1)
See Stop #N/A Error
Another reason is when Array formulas are used AND the referenced ranges are not of equal size in each array.
Count or Sum Specified Number In Single Cell Housing Many Numbers
Function Count_Sum_NumbersInCell(rCell As Range, _ sNumber As Single, strDelimeter, Optional bSum As Boolean) As Single Dim vArray Dim lLoop As Long Dim sResult As Single vArray = Split(rCell, strDelimeter) If bSum = False Then For lLoop = 0 To UBound(vArray) If vArray(lLoop) = _ sNumber Then sResult = sResult + 1 Next lLoop Else With WorksheetFunction For lLoop = 0 To UBound(vArray) If vArray(lLoop) = _ sNumber Then sResult = .Sum(sResult, vArray(lLoop)) Next lLoop End With End If Count_Sum_NumbersInCell = sResultEnd Function
If A1 housed 1 3 30 3 23 3
=Count_Sum_NumbersInCell(A1,3," ")
To count the number of 3's in A1 where numbers are separated by a space. 30 and 23 are not counted
=Count_Sum_NumbersInCell(A1,3," ",TRUE)
To sum the number 3's in A1 where numbers are separated by a space. 30 and 23 are not summed
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Until next month, keep Excelling
Got any Questions? Free Excel Help
Complete Excel Training Course Special! 
Free Help Forum, not just Excel! Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help