Insights

This is a follow up to the post titled “Excel: Using F9 to Audit Formulas.” This video will explain how to extract nonblank or nonzero data from an array without using an array formula.

I was recently provided a workbook containing a balance sheet with over 700 line items. Many of the line items showed a value of zero, making the challenge one of extracting all nonzero values without having to go through and delete line items one row at a time. This required a slightly more complex formula, which frankly I got wrong a handful of times before finally arriving at the solution. And in that process of auditing (or debugging) the formula, the F9 key was fantastically useful.

Context: I recently received a question from an entrepreneur in the process of negotiating the sale of a control equity position in his business where he would remain post transaction as CEO and a minority shareholder. Most everything detailed in the offer was clear except for a footnote explaining that IRR’s referenced in the term sheet would be calculated using the =XIRR formula in Excel. This was of particular interest because his ownership had the potential to grow substantially post transaction if the business generated certain “hurdle” IRRs at exit. (As a crude example, imagine that at an IRR of 15% the entrepreneur would be entitled to an additional X number of shares, and at an IRR of 30% an additional 2X shares, etc.)

This question made me realize that I should add some material demonstrating how dividends flow through the three financial statements. In the interim please see the Q&A that follows:

A friend of mine recently emailed asking for help with a formula. In an effort to build a dashboard that would provide information on the buildings and apartments he manages, he was looking for a formula that could return the number of units he had a available for rent within a certain time period.