Tag: Excel

Excel Error and Error Handling Roundup

Excel Error and Error handling Roundup 

Technology used: Office 365, Excel 2021

You just typed the closing parentheses of that powerful lookup formula that’s going to save you hours next month on the report. You slowly depress the Enter key while feeling empowered as if the Excel gods have chosen you! Feeling the click your stomach turns as you watch the fire bestowed upon you turn into a ball of steam that wisps away.  Excel is taunting you. Instead of the sales figures, you see #N/A but what could be wrong? 

Excel’s formula errors are unpleasant to see but they’re trying to help us improve our spreadsheet game. In this article, we’ll learn about the types of formula errors that Excel will display, a few formulas to help us avoid them and some thoughts on good use of error trapping. 

What are formula errors

Formula errors are the result that Excel returns when it is unable to evaluate and provide a proper answer to the formula in the cell. The errors always begin with a number sign (i.e. #) and then a word to give you a clue as to what is causing the error. Below is a table listing common errors and some of their causes

Error (As shown in the cell)Description
#N/ACommonly found in lookup formulas (e.g. Vlookup, Hlookup, Index, Match, etc) if the formula is unable to find what the formula is looking for
#VALUE!Excel’s general error. Simply put, something is wrong with the formula.
#REF!A cell reference is invalid, typical if you delete a row/column that was being referenced by the formula
#DIV/0!Division by zero. The result of which is undefined mathematically. 
 #NUM!Mathematical operations are being applied to non-numeric cells. You can force text to be treated as numbers using the VALUE() function.
#NAME?Excel doesn’t recognize the formula name, usually the result of a typo (e.g. =SMU(A1:A5) instead of =SUM(A1:A5)
#NULL!The range is not constructed correctly in the formula’s parameters (e.g A5 B7 instead of A5:B7)
#SPILL!Excel is unable to return the results because there isn’t enough room. For example a FILTER function is returning 10 results but there are only 9 blank cells available
Microsoft Excel’s Formula Errors

What formulas can be used to stop errors? 

Excel gives us two formulas to handle or trap errors. Using these formulas can make your worksheet more robust and user friendly. They can also allow your workbook to function despite Excel being unable to calculate a cell. 

IFError

The IFError function allows you to provide an alternate value or formula if Excel encounters any of these errors:  #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Its usage is: 

=IFError(cell reference or formula, value if error) 

The cell reference is any cell or formula that you are testing to see if it has or results in an error.  

The value if error is what will result if there is an error. Here you can be really creative. A common usage would be a helpful error message consider something like: 

=IfError(1/0, “Division by zero is undefined”)

In this case the formula 1/0 (e.g. One divided by 0) would result in #DIV/0!, we can replace that with the more friendly error message explaining that we can’t divide a number by zero. 

But you can also use a substitute formula. For example:

=IFError(1/0, A1/B1)

This formula will result in the value of cell A1 divided by B1. Coincidently if the values of A1 divided by B1  would result in an error, then that error will be displayed. 

Using the IFERROR formula gives us a lot of flexibility in trapping and resolving errors. 

IFNA

IFNA works very similar to IFERROR except it only works with the #N/A error. Otherwise it’s usage is identical. Recall from the table above that the #N/A error is only encountered with lookup formulas thus this formula will only trap cases where the lookup value can’t be found in the table. 

Its usage is:

=IFNA(Cell Reference or Formula, value if error) 

For instance:

=IFNA(Vlookup(A1,A2:B20,2,FALSE),”Value was not found”)

Would display Value was not found instead of #N/A if the lookup value couldn’t be found. 

Similar to IFERROR you can use a formula or value in place of the value if error parameter, this can help keep the logic of your workbook functioning.

Consider this example:

A Vlookup function encapsulated by an IFNA function

As you can see, since New York is not in the list an #N/A is triggered, the first version of the formula returns a 0 which we could then use in our workbook if we need to. 

Trap every error? 

As you’re building your workbook consider the effects of trapping the error versus knowing there is an error. Sometimes we want to see the error, to know that the workbook is not working as we intended. For instance in the example above, we are returning 0 for cities not in the list, however since our worksheet is to tell us total sales $0 is also an acceptable answer. We have no easy way to tell if there were no sales in a city or that the city is not in the list, which might indicate a data issue. 

This is a problem known in Computer Science as the semi predicate problem where an error can be confused as a valid value. To prevent this care must be used when selecting what errors we want to trap and which we want to allow to break the workbook. 

Excel has a variety of error codes to help the user understand why their formula isn’t working. Excel also has useful formulas for trapping and resolving potential errors. Careful and considerate use of these can lead to well constructed and robust workbooks. 

Do you want to learn how to automate your financial workpapers? Do you want to get started with VBA?

My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!

Learn More:

Function pages on Microsoft Support

IFNA

IFERROR

#VALUE Error

#NUM! Error

#NAME? Error

TEXTJOIN function makes list building easy

Technology: Office 365 as of 2020

Microsoft introduced a new function which makes combining the contents of cells even easier than before. In addition to concatenating, or joining, the contents of cells the TEXTJOIN function also allows you to insert a delimiter between each item. This makes it more powerful than the old concatenate formula.

The ability to add a delimiter is what makes this function very useful. In the past we had the CONCAT and CONCATENATE functions which would join together text however, depending on the application, you usually need some character between your values.

Here’s a common example. Pasting a list of email addresses from Excel into an email in Outlook. The application expects a semicolon or comma between each email address or name, however you may not have included these characters in the list in Excel as it wouldn’t make sense to store this with the data.

If you require functionality similar to the old CONCATENATE function you can set the delimiter to “” which joins the text together without any character between each item. There is also the ability to include or exclude blank cells in the cell range from the results. If you need to do this depends on your use case.

The syntax is =TEXTJOIN([delimiter], [ignore blank cells], [cell range])

Where:

Delimiter is any character you want between your list. The character need to be enclosed with quotes, i.e. “;” This is required.

Ignore blank cells is either True or False depending if you want blanks included in your list. This is required

And the cell range is your list items you’re wanting to join together. You can also have multiple ranges in this field if you need them.This is required.

Let’s look at some examples

This first example presents a case where we want to email some individuals, to put their email addresses in the To field of our email client we need them separated by a semi-colon.

This next example provides a result similar to the old concatenate function. In this example the list of numbers is joined together without any delimiter.

The TEXTJOIN function saves a lot of time for those of us who use Excel to work with data needed in other applications. The power of adding a delimiter means you can quickly transform a list stored in a cell range to suit the needs of your application. It’s a simple & powerful formula to add into your repertoire.

Do you want to get started with VBA? Want to learn how to automate your financial workpapers? 

My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!

Learn More:

Example Workbook:View Workbook on Microsoft OneDrive

TextJoin Function: TEXTJOIN at Microsoft Office Support

9 time-saving tech tips for the busy CPA

In the average day you probably spend at least an hour or more doing the various steps to accomplish one goal. The time you spend highlighting a sentence in order to paste it into google. The time you spend finding file paths so you can share it with a colleague.

These mini-tasks are not only tedious they waste precious seconds which add up to minutes. Combine with with the few minutes it takes us to switch between tasks, and you’ll see how it adds up quickly! The only solution is to best optimize your workflow so that you reduce the number and time of these mini-tasks.

A quick Google search will reveal thousands of articles on this topic. Although many of them have the same suggestions. However this particular article has some unique points and is well worth a read. One that I find really useful, and I didn’t know until I saw it here, is to use the Copy as Path option.

In Windows, to copy the path of a folder (such as M:\Important Stuff\Firm Holiday Schedule.docx), hold down the Shift key and right-click the file or folder that you want and select Copy as path. The path will be copied to your Clipboard, and you can then paste it where you need to. This tip can also come in handy when you’re attaching files to emails or filling in dialog boxes.

Another is to use MS Office’s built in PDF functionality. I’ve used this for a long time and I prefer it to using Acrobat Pro’s PDF printer or even other many of the third-party utilities. The Save As PDF feature is much less prone to crashes and faster than the apps out there. Plus it’s already built in to the app so there’s no additional IT cost.

We’re always going to be juggling tasks in an effort to get through the day. It’s a part of our job, but it’s something we need to master. By taking every available shortcut we can maximize our own productivity and take back our minutes.

Source: 9 time-saving tech tips for the busy CPA