Archive for October, 2011

Using the Excel SUMIF function to find that out-of-balance journal entry

One of the most underrated Excel functions that I use often is the SUMIF function.  On my Excel function tournament bracket, SUMIF ranks slightly behind the SUM, AVERAGE, IF, COUNT, PMT, and RATE functions.  Still, it has a shot at upsetting one of the higher seeds on any given day. The SUMIF function adds the values in cells specified by a given condition or criteria.  Tim Gavin, a fellow CPA blogger, wrote a great blog post last week for the Sikich Technology Blog explaining how the SUMIF function works.  I won’t reinvent the wheel here, so check out his post for a tutorial.  I simply want to give an example of how I use it. Let’s say I’m cruising along in Excel making journal entries.  Each entry is designated by a letter.  Some entries have multiple debit and credit adjustments.  Work comes to a screeching halt when I check the column totals and realize that my journal entries are out of balance by $200.  It may be easy to spot the difference with only four entries in the screenshot I’ve provided below, but just imagine that I’ve exhausted the alphabet and am up to entry z.  With so many adjustments, finding the discrepancy could become a painful and time-consuming ordeal. Out of Balance Adjustments Here comes the SUMIF function to the rescue.  First, I enter the letters of the alphabet for each journal entry down a column (using the fill handle to drag down the letters sequentially saves some wear and tear on the keyboard).  Then I enter the formula shown below and copy it down for each letter.  The formula adds the amounts for the debit and credit column that correspond to each letter of the alphabet. SUMIF formula The results are in.  Adjustments a, b, and c balance out.  It was adjustment d that threw me off my game. SUMIF results

Reversing the page order in Adobe Acrobat

A coworker stopped by my office seeking a solution to a dilemma.  She had received a large PDF document in reverse order (page 1 of the document was page 100 of the PDF, page 2 was page 99, and so on).  Since backwards workpapers are the type of thing that can drive an accountant to the brink of insanity, there was a lot at stake here. We brainstormed a few possible ways to reverse the page order.  In the past, I had just used the pages navigation panel to rearrange the page order for small PDF files of five pages or less.  However, for a long document of about 100 pages, this idea didn’t seem practical.  We also could have printed off the entire document and resorted the pages by hand, but that didn’t sound like fun. Naturally, I turned to Google.  I searched for “reverse page order in Adobe Acrobat” and ended up in this Adobe forum.  The sixth post in this forum provides Javascript code, which I saved to my Acrobat Javascript Folder.  After How to Create a Bodybuilding Diet uk roids central bodybuilding episode 37 | ironmag bodybuilding blog that, a new item called “reverse” appeared under the document menu (see screenshot below). Within a few seconds, the script put the pages in the right order, and I literally saved one accountant’s world from turning upside-down.