Excel reader challenge

BY J. CARLTON COLLINS

In the August 2013 JofA, I challenged readers to solve an Excel problem in which a preformatted report fetched an ever-changing last row of data. We received well over 200 solutions from a group of Excel-savvy CPAs. Though each solution was different, all of them worked properly and employed a wide range of creative and imaginative approaches to solving the problem. In summary, the readers employed eight basic approaches, with VLOOKUP being the most popular approach (used by 31.3% of the respondents), followed by the INDEX approach (used by 29.3% of the respondents). The approaches I identified (and their frequency of use) are summarized in the chart below.

 

The solutions submitted showcase the ingenuity and industriousness of this column’s readership, and while everyone who submitted an entry deserves recognition, I identified the solutions submitted by Michael Inouye, Laura Douglas, Larry Moseley, Robert Loudon, Trisha Allen, Brian Carle, and Benjamin Brenner as being especially noteworthy because they were efficiently written, contained clever formulas, and taught me things about Excel that I did not previously know. To view a more detailed summary of the submitted solutions, and to download more than 70 of these solutions (including the solutions from the seven individuals mentioned above), visit tinyurl.com/n56bphh.

J. Carlton Collins ( carlton@asaresearch.com ) is a technology consultant, CPE instructor, and a JofA contributing editor.

CAREER TIPS

CPAs share lessons from dads

To celebrate Father’s Day, the JofA asked accountants across the country to share some lessons they learned from their fathers—especially ones that helped them later on in the accounting profession. Here are their stories.

SPONSORED REPORT

How to audit high risk areas

Revenue recognition, internal control over financial reporting, accounting estimates and going concern are areas of audit that have emerged as particularly challenging and complex.

PRACTICE MANAGEMENT

Q&A: How to determine a client’s price sensitivity

To price effectively, CPA firms first need to determine the price sensitivity of individual clients. Author and consultant Ron Baker, an unabashed value-pricing champion, explains how to do it in this Q&A.