Have you ever worked on a handyman/handywoman project yourself? I have. When we moved into our new house eleven years ago, there were a number of things that needed to be done. Not to boast too much, but I personally, without any professional help, installed a souvenir ‘Nessie’ key holder on my kitchen wall (using double-stick tape, of course), and it is still up! That should tell you a lot about my handyman skills. Over the years, I also assembled a few bookshelves and TV tables (with some assistance). And, before I got into a computer career, I used to do piano tuning and repair. Really. Without using Excel. Anyway, one lesson I learned very well is the importance of always using the right tool. How frustrating it can be when you just can’t quite reach that screw, or just can’t quite manage to get to that bolt, etc. You know what I mean. Having the right tool (and knowing how to use it!) makes all the difference.
The same goes for computer programs. In the course of my career, I have often found people using the wrong program to try and get work done. Sometimes they might use Microsoft Word when they really need Excel, or they use Excel when they should be using QuickBooks. One of the most common mistakes I have found is the extreme ‘overuse’ of Excel spreadsheets when, in fact, the company really needs a database. On many, many occasions, I have advised clients NOT to use Excel, because it just is not the right tool. (Is this really Norman saying this?!)
Typically, I believe the problem occurs when a company starts up small and begins using a spreadsheet to keep track of some data. Over time, they decide that they would like to track other data, so they add more columns or sheets and build long and sophisticated formulas to analyze data. Subsequently, more data becomes available from another source, and they start linking multiple workbooks to track even more data and build even more complex formulas. Then something goes wrong, and it becomes virtually impossible to find and solve the issue!
True story. A major shipping company called me in to consult regarding a problem they had with an Excel formula. They knew there was an issue but could not figure out where the problem was. The formula looked something like this:
=if(((((((((AND(INDEX($C$4,INDIRECT(TELM),MATCH($H$1615)="COOP",IF($l$16>=FIND("HMM",GORDALL,12,4),IF(C1080<=.5(AQ1518),IF(ISBLANK(STREAM)STDEVA(1080*2.5,2060*.4),IFERROR(G1615*.05),L968/H1524),IF(OR(G780>H1615,Y60<>40,AP577="SLAM"),"OK",INDEX($C$4,INDIRECT(TELM),MATCH($H$1 MM"$L16,,12,4),IF(C1080<=.5(AQ1518),IF(ISBLANK(STREAM)*.05(SUM(G2540*G2546))))))))))))))
The formula literally spread out a complete four rows in the formula bar!
I told them it would likely take me at least 16 hours just to try and evaluate what the formula was meant to do.
There are usually two approaches I take when confronted with this type of situation. First, when dealing with many data sets, and especially when working with historical-type data, I strongly suggest going the database route. If, in fact, the data lends itself to a spreadsheet application, I suggest a ‘divide and conquer’ methodology. Instead of trying to come up with one extremely long and sophisticated formula, I would try breaking down the components, and separating multiple parts of the formula so it is easier to evaluate.
What is the dividing line of when to use a database program as opposed to a spreadsheet?
Let’s say you have a list of 28,000 products, with the item name, ID number, cost, and sales price. Now, you would like to keep track of the profit percentage for all the items, and maybe even by certain category groups. You can use a formula to calculate the profit percentages, and as the costs or sales prices change, the formula will automatically update. You could then use a Pivot Table to summarize profitability by categories. Perfectly fine for a spreadsheet. But if the company needs to keep track of how many units were sold to various customers at various times, things quickly start to get out of hand. They start to add sheets for different customers, then they need to add multiple columns to keep track of the sales dates, etc. It gets very messy very quickly. In this case, a database is necessary.
For the most part, you can find a software application for almost any industry or company. The problem is that many times these programs do not do exactly what you would like to do in the way that you want, or maybe just do not have the right look and feel. It then becomes necessary to customize the program, which often is either not possible or becomes rather costly.
In instances like this, it can be much better to have someone develop a custom software program. Many times, it can be cost-effective, and offers so many advantages, such as:
If you have any questions in regards to data management, feel free to reach out to me anytime, and perhaps I could be of assistance.
Norman Axelman, a nationally acclaimed computer instructor and consultant, is the founder of Advantage Computer Solutions, based in Miami, Florida. Advantage Computer Solutions has been offering superior computer training and consulting services to individuals and businesses since 2001, saving clients thousands of hours of time and commensurate payroll expenses. With over 30 years of professional experience, Mr. Axelman provides an outstanding level of computer training with a focus on useful and practical day-to-day features, formulas, and shortcuts, resulting in increased efficiency and productivity, and as a Quickbooks Pro Advisor, he has helped many clients with set up and use of the Quickbooks program.
Mr. Axelman has been an adjunct professor for the Miami Dade College School of Continuing Education, has been retained as a computer instructor for a yearly national convention that draws over 1,200 accounting professionals and has delivered presentations for CPA Academy, Insightful Accountants, and many other companies and organizations.