For those of us who review accounting workbooks it’s helpful to have a few tools to make the review easier. In today’s blog post I’m going to list 3 of my favorite Excel formulas or shortcuts that improve the quality of my reviews.
View Formulas helps you remove plugs or audit formulas
Pressing control and the tilde key (i.e. CTRL + ~) will change Excel to show the formulas in each cell rather than the values. This is very helpful when reviewing workbooks to ensure there are no plug values and that the formulas are consistent have the right ranges, etc. Press CTRL + ~ to put the view back to normal
The shortcut key CTRL + ~ changes the cells to show their formula rather than the value
As you can see in the screenshot, with view formulas it’s very easy to spot formula inconsistencies.
TEXTJOIN for making lists
The TEXTJOIN formula allows you to create lists easily with a seperator character. This is particulatly useful in my trade for combining lists of entites to put into a filter in the accounting system. The syntax of TEXTJOIN is = TEXTJOIN (delimiter, Ignore Empty, cell range)
The delimiter is the character you want to separate the items with, it can be anything but I’ll use the carrot ^ character for this example.
Ignore Empty parameter is either TRUE of FALSE, normally you’ll use TRUE so that it doesn’t conisder any empty cells in the range.
In the screenshot below, you can see how I used the formula to easily create the list (i.e. propert1^property2^property3^property4)
TEXTJOIN makes lists quickly
SUMIFS for totaling by category
The SUMIFS formula is very handy to get a quick total. It’s syntax is
=SUMIFS(sum range, category range 1, category, [more categories])
The sum range is the values you want to add.
The category range is the labels for each value
The category is which label you want to add
Consider this example. We have two projects TI Project #1 and #2. If we want to add TI Project #1 interdependently we can do this very easily using the SUMIFS like this:
SUMIFS can easily add up categories for your review
Although we tend to look to excel functions for preparation, even reviewers can harness the power of Excel shortcuts and formulas to make them better reviewers and help reduce or eliminate errors.
Do you want to learn how to automate your financial workpapers? Do you want to get started with VBA?
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/A
Commonly 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
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:
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 problemwhere 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?
Recently I was given the task of extracting a chart of accounts (COA) from a general ledger report. There are many ways I could have done this. Using Auto Filter comes to mind. Being me, I chose to write a small program in Visual Basic for Applications (VBA). In this post I will walk you through the design and code of the program I made.
General Ledger
Design:
First we need to determine the goals for our output or in this case the chart of accounts. I needed a report with:
account number
account description
In the source file we see that the account number is in Column A and the account descriptions are in Column E. We also see that rows that begin with a number are the rows that have the account number/description. Since that’s all we need we’ll ignore everything else.
With that information we know that our solution will have to do the following:
find or determine the first and last rows on the source worksheet
examine each row in the source worksheet to see if it starts with a number
if it does start with a number then
take the account number and
take the account description and
write them to the destination sheet
lastly increment the row in the destination worksheet
It’s obvious that we need a loop in this case we’ll use a FOR loop to examine each row of the source worksheet. It’s also clear we’ll need an IF statement to determine if the first character of the row is a number or not.
Code:
Sub ExtractCOA()
'This program will extract the chart of accounts from a Yardi 7
'default General Ledger (analytics) report run to Excel
'Derek Harlan April 2018 - Licensed in the Public Domain
'Setup Object Variables for the worksheets
'src will be the Source worksheet
Dim src As Worksheet
'dest will be the Destination worksheet
Dim dest As Worksheet
'Setup some counter variables
'firstRow / lastRow will be the first and last rows of the Source workbook
'nextRow will be the row to write in the Destination workbook
'i will be the counter in the For loop
Dim startRow as Long
Dim lastRow As Long
Dim i As Long
Dim nextRow As Long
'Set the initial values for the variables
Set src = Sheets("Report1")
Set dest = Sheets("Sheet1")
startRow = 6 ' this is easy to determine so I just set it the row number
'Find the last row by searching the workbook from the bottom row up
'and assign it to the variable
lastRow = src.Range("A1048576").End(xlUp).Row
i = 0
nextRow = 2 'Row 1 will be the headers in the worksheet so Row 2 is where the data starts
'We'll loop through each row between the first and last rows of the Source workbook
For i = startRow To lastRow Step 1
'If the first character of the value of the cell in A and the current row (i) is a number then
'that row has the account number and name on it
If IsNumeric(Left(src.Range("A" & i).Value, 1)) Then
'set the values in the destination worksheet from the values on the
'current row of the source worksheet
dest.Range("A" & nextRow).Value = _
src.Range("A" & i).Value
dest.Range("B" & nextRow).Value = _
src.Range("E" & i).Value
'lastly increment the nextRow in the destination worksheet
nextRow = nextRow + 1
End If
Next i
End Sub
Running it creates this output –
Chart of Accounts in Excel
With both of the requirements met I can say this was a success!
This project took about 20 minutes, from start to finish. In this instance I’m not sure if I saved any time however should I need to do this again I already have the program ready. When evaluating alternatives think about how often you’ll be asked to do this. It may take time to come up with the initial program but once you have it, you see a colossal increase in productivity!
Do you want to get started with VBA? Or want to learn what a FOR loop is?
Check out my book for more uses of Excel VBA in your daily work:
In the near future auditors will be aided by power artificial intelligence systems. Near future, is a little inaccurate, in fact it’s already happening. As big data becomes ever more a part of our work lives, computers are needed to make sense of this information and help us preform audit engagements.
The major players are starting to emerge. And big blue is throwing the power of Watson into the mix. KPMG is using their human auditors to teach Watson how to do it’s job. Currently, the software can aid auditors by analyzing contracts, legal documents by looking for keywords and other patterns.
Watson aside – this is becoming the norm in the industry.
A quarter of the 180 CPAs surveyed listed big-data analysis as one of the top industry challenges in the future and 20 percent listed increased complexity and scrutiny in engagements.
Big data will only continue to become more prevalent and we must build the tools needed to audit these systems. Artificial intelligence is poised to be the go to solution when working these engagements. Learning how to use this in our day to day business is a must.
Sometimes the most productive tips are the simplest. Adjusting the speed of your mouse pointer can save seconds per each task, which can add up to minutes per day of extra time. Check out the link below for instructions on how to make this quick tweak to your system!
It’s 5:15 PM and I hear that dreaded unmistakable klaxon signaling my doom. I look at the caller ID and my fear is confirmed. “Hi honey” I start to say hoping to defuse the situation but I have to speak more and tell her that I won’t home for dinner. She informs me of her disappointment, that this has happened too much this week but she says her good-byes. I try to focus on my computer, trying to recall where I was. My manager comes by, “She’s really upset with you lately, maybe you should go home and I’ll let Brenda work on this.” I smile at her and thank her for understanding. As I’m walking to my car, wondering if I should pick up flowers, I wonder – “How did my manager know she was upset with me?”
Big data is crossing into every corner of business. Recent developments have the computer search transcripts of sales conversations, diagramming the sentences to determine a persons tone and if they were being aggressive. The implications, however, go further than sales calls. Many firms are establishing departments to monitor insider threats. These monitor employees emails, social media and telephone communications for information leakage but also a person’s demeanor. If one is found to be too aggressive or too forthcoming they can be re-trained or terminated.
[The group monitors] for ethical and compliance violations by its employees… monitors the incoming and outgoing email traffic of particular groups or individuals. The purpose is “to understand if we have a high-risk area that we need to look into”.
These systems are also looking for patterns in other ways. For example by monitoring if certain systems are queried more often than others. This might indicate a hacker has targeted this information. The fact a query has been made is more significant than the actual query itself.
Does big data mean we should stay off of our phones? Does it mean we should avoid social media? These are questions that are difficult to answer but the most logical approach to use common sense and always follow your companies guidelines for handling information. Is this too Orwellian for you? Let me know in the comments.
Computer running slow? Well there are thousands if not millions of articles that offer advice on how to fix that. However they generally boil down to the same three or four steps. They are clear temp files, empty recycling bin, add more ram, install updates and more in that same vein.
This article goes a bit further and offers some unconventional advice. Hiding unused fonts, physically cleaning the case (to help the computer run cooler) and even assigning a static IP to cut down on time waiting for the computer to find one. In one way this article can be seen as a comprehensive guide to performance tips.
Also interesting, the author offers a practical benchmark to see if your system is up to par.
To quickly test its performance, simply reboot the system and then launch Excel and a browser. If the system reboots in less than a minute and the applications launch in less than two seconds each, then your system’s performance is adequate; otherwise, perhaps you should consider implementing some of the measures outlined below to rejuvenate the computer’s performance.
High performance doesn’t guarantee productivity, however, it prevents hindrances to it. If you don’t find your system up to snuff go ahead and try some of the suggestions in this article. With your computer at full performance you’ll be equipped to deliver your best work product.
Imagine this. You’ve just walked into your local shopping mall when you hear that particular unmistakable jingle. As automatic as breathing your hand reaches into your bag and draws out your phone. Only it’s not a text or call your seeing, your phone is directing you down the crowded corridor. Instinctively you follow the line not sure where it’s leading, but you have no reason to fear, you’re phone has never lead you astray before. As the line ends you find yourself standing in front of a display case at Zales. Ah! You think that’s exactly what my spouse wants for our anniversary! You pull out your wallet and and get the clerk to wrap it up into a nice package, and voila anniversary gift shopping is done.
Retailers have begun experimenting with technologies that enable shopper to find products they want using their smartphones. A variety of products exist however they all basically operate under the same concept. By leading consumers right to the object of their desire brick and mortar stores are hoping to gain an edge over online shopping.
Two products in particular iBeacon & IndoorAtlas tackle the problem in different ways. iBeacon uses Bluetooth devices to alert the users of potential sale items and offers as they walk past the transmitter. IndoorAtlas works more like a traditional map app such as Google Maps to navigate users to stores and even specific products within a store. One major issue, however, is that consumers will need to install these apps and then use them when they are in a shopping center. This leads to some delays in deployment as the shoppers need to participate in the roll-out of these services.
“That’s due in part to the fact that we’re trying to teach them new behaviors,” Kindred said.
Adam Silverman, an analyst at Forrester Research, said that customers who are looking for something in a store find it easier to just ask a sales associate, rather than installing an app.
“Although it makes sense on paper, the customer hasn’t been fully engaged in some of these beacon-based technologies, mainly because it doesn’t make their shopping easier, or more engaging,” Silverman said.
Once these technologies become more mainstream the uses can branch out to more than just shopping. For example these apps can give travelers directions to their gate or baggage claim in an airport, or directions to a particular suite in an office building. Or even provide emergency evacuation instructions should an event arise.
Indoor mapping technology faces an uphill battle of user adoption. While it seems like a good idea only time will tell if it becomes a popular one. However, it gives us hope to reduce the labyrinth of today’s malls and airports to a manageable path. Can you see yourself using these apps?
Out friends over at IT Canada have given us a strong warning against using unlicensed or pirated software. A study conducted by the Business Software Alliance (BSA) found a strong correlation between malware attacks and users of unlicensed software going as far as to say that distributors of pirated software may embed a virus, or worm or other malicious piece of code which will unknowingly put your data at risk.
I’d like to remind you that software piracy is theft, and I’m shocked that some very large organizations are using unlicensed software to the extent found in this study. Although not necessarily caused by pirated software, lets look at the Sony breach as a testament that you don’t want that to happen to you.
With this in mind, lets all do the best thing for our clients, employees and other interested parties. Just buy the license.
Recently my Grandfather passed away. We were very close and this loss has been difficult for me. One hobby that my Grandpa shared with me was his love of computers and you can see the influence that it had on my life and career. In memory of him I wanted to share some material related to the IBM 5150 – the first IBM PC.
He had worked for IBM for his entire post-military career as a computer engineer and programmer. The computers he worked on were the now classic room sized machines but when IBM released their first consumer PC he had to have one for his home. He saw so much potential in computers and it was always fun and interesting to hear his thoughts on the technology of today – especially being so connected to its roots. I should add that he kept up with computers as time went on.
Today – I wanted to share an article in that vein of comparison between new and old. In fact, if his still working 5150 comes my way, I’m hoping to write something similar. The below article is a bit dated being written in 2011 but it’s a fun read especially for anyone with an interest in vintage computing. I won’t go into it to much here, I’ll let the piece speak for itself.
Stay tuned to my blog for my own 5150 experiments!
