A complicated inheritance


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.


Click-through nexus: Pushing the boundaries of sales tax compliance

Sales and use tax compliance has been complicated by nexus expansion. In this report, we provide an overview of this issue and include a handy state-by-state summary of click-through nexus or notification requirements.


News quiz: Surveys gauge attitudes toward IRS and economy

Recent news reported about perceptions of IRS service during the recent tax filing season and of the U.S. and global economy during the second quarter. See how much you know about recent news with this quiz.


Auditing risks in culture

Cultural flaws can seriously damage an organization. Here’s how internal auditors can reduce risks by embedding culture audits into existing audit programs.