Microsoft Excel: How to evaluate complex formulas

Q. I’ve accepted a job at a property management company that uses an Excel worksheet to calculate agent bonuses for each lease agreement executed. The bonus calculation (pictured below) seems to work correctly, but the formula is so complicated (circled in red), I can’t follow it. Any suggestions?

A. A good approach to understanding complicated Excel formulas is to dissect them using Excel’s Evaluate tool, as follows. (Click here to download this workbook in Excel.) Select the complicated formula, and then from the Formulas tab, select Evaluate Formula in the Formula Auditing group to produce the dialog box pictured below.

Next, click the Evaluate button repeatedly to move through the complex calculation one step at a time. This will enable you to carefully study each step of the formula’s mathematical progression from start to finish, which hopefully will aid your understanding. In this example, it takes 71 mathematical steps for Excel to complete the calculations contained within this one formula, so it’s easy to see why you struggled to understand it. I don’t have room to show all 71 steps, but pictured below are the Evaluate Formula tool’s mathematical progression for the 25th, 50th, and 70th steps, to give you an idea of how the tool works. Note that each “next step” is underlined to help you follow those steps.

With each subsequent click of the Evaluate button, another small part of the formula is solved until, ultimately, the entire formula is solved. After studying the formula a few times using the Evaluate Formula tool, I was able to grasp the formula’s intent, which is to calculate up to six possible bonuses for each lease agreement transaction executed by your sales agents, and then add them together. Those six bonus calculations are as follows:

• Bonus 1: $100 if the agreed-upon rental rate exceeds $1,100.
• Bonus 2: $100 if the length of the lease exceeds 12 months.
• Bonus 3: $100 if the deposit received is equal to or greater than one month’s rent.
• Bonus 4: $100 if the tenant’s FICO credit score exceeds 700.
• Bonus 5: $100 if the property remains vacant for less than 35 days.
• Bonus 6: $250 if all five bonuses mentioned above are earned.

As a result, leasing agents can earn up to $750 in bonuses for each executed lease agreement they negotiate, if all the company’s favorable conditions are met. I concur that the formula works properly, but it contains embedded assumptions, which is never a good idea. Further, I agree with you that the formula is overly complicated and should be simplified. I have found that some CPAs tend to write complicated formulas like this one in a single cell, when it likely would be easier to write several simpler calculations across multiple cells. Pictured below is an example of how I would restructure this bonus calculation so others could more easily follow, review, and edit this worksheet in the future.

As you can see, I have replaced the single complicated formula (formerly in cell D11) with 27 separate cells (on rows 11 through 18), including bonus amounts, bonus descriptions, bonus criteria, formulas, and column titles. The resulting worksheet is easier to follow, and writing the six separate formulas was surely much easier and faster than creating the single gigantic formula your predecessor left for you. In addition, instead of embedding the bonus and criteria amounts in the complicated formula, I have broken those values out into separate cells for easier review and editing. I’ve also added yellow background and border highlights to those assumption cells to make it easier for others to follow and use the worksheet in the future.

The Evaluate Formula tool is useful, but I advise CPAs to design their worksheets so that others will not need to use the Evaluate Formula function to follow their work. You can download this workbook here.

About the author

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

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.