A complicated inheritance

BY J. CARLTON COLLINS, CPA
April 1, 2013

Q: I inherited an Excel workbook containing lengthy formulas, and I am having trouble determining exactly how these formulas work. Is there a magic trick for figuring out how complicated formulas work?

A: Excel provides a couple of tools that might help. The first tool is Evaluate Formula. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Evaluate Formula. This action will display the formula in the Evaluate Formula dialog box (pictured below).

With each click of the Evaluate button, Excel solves the next step of the formula to help you follow and understand the logic.

A second tool that might help reveal a formula’s logic is Trace Precedents. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Trace Precedents. This action inserts arrows depicting the flow of data from its source to the formula.

Click the Trace Precedents tool a second time to insert additional arrows depicting the flow of data to the second level of data (preceding the initial first level of source data). If desired, continue clicking Trace Precedents until all data flowing to the complicated formula is mapped to the original values entered in the workbook. This arrow mapping might help you better follow the flow of data through the workbook. ( Note: You must place your cursor on a single cell for Trace Precedents to work. It won’t work if you select multiple cells. This means you must move your cursor to each cell where you would like to trace the data flow.)

For example, examining the flow of data in the screenshot pictured below reveals that the total formulas on rows 59 and 61 do not line up with the same columns containing the data (i.e., the January totals are contained in the February column, and so on). This might explain why the reader was having trouble making sense of these formulas.

PROFESSIONAL DEVELOPMENT: EARLY CAREER

Making manager: The key to accelerating your career

Being promoted to manager is a key development in a young public accountant’s career. Here’s what CPAs need to learn to land that promotion.

PROFESSIONAL DEVELOPMENT: MIDDLE CAREER

Motivation and preparation can pave the path to CFO

CPAs in business and industry face intense competition to land a coveted CFO job. Learn how to best prepare yourself for the role.

PROFESSIONAL DEVELOPMENT: LATE CAREER

Second act: Consulting

CPAs are using experience to carve out late-career niches. Learn how to successfully make a late-career transition to consulting, from CPAs who have done it.