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.
In the video that follows I recreated the scenario on a much smaller data set so that it would be easier to visualize:
ASM Challenge: Work through the rest of the formula to better understand how it works. This process will expose you to some useful Excel functions including =IFERROR and =INDEX, as well as others that are less common, but very helpful when used in the correct context including =ISNUMBER, =ROW, =COUNTBLANK and =SMALL.
You can expand the worksheet (download here) to expose rows 11 through 31 by ungrouping the selection. In that grouping you will find the formula broken down into three steps to facilitate the process.
I will post the answer as a follow up.
Update: Click here for the solution.