Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter February 2008

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Complete Excel Training Course


Excel Formula Errors & How To Deal With Them


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.

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:


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.


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.

Simply means you cannot divide zero into a number. For example


would result #DIV/0! IF A2 contains nothing or zero. To correct this one could use one of 2 methods.




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;



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


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;


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.


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.


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.


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!

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;


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

Used in any cell as;

=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!
We don't teach Excel from a manual, we teach Excel from experience!
Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Excel Data Manipulation and Analysis

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help