This financial model illustrates the potential problems or pitfalls with the Excel VLOOKUP function in financial analysis. In this example, we are using a VLOOKUP to reference the Last price for the following Dow Jones Industrial Average stocks.
Excel AGGREGATE SUM with Errors
This follow-up article on the use of Excel AGGREGATE SUM introduces the added complexity of error cells, text cells or other erroneous cells that would cause errors with a standard Excel SUM function.
Excel AGGREGATE, not Excel aggravate!
The simple SUM function is an adequate tool to aggregate a data array in a financial model. However there are some financial spreadsheets that will require a more robust Excel function, in order to provision for hidden rows, error values, and nested SUBTOTAL and AGGREGATE function cells.
The use of Excel AGGREGATE with SUM as the function syntax, depending on the Option selected, in terms of which values to disregard in the evaluation range for the function, will overcome certain problems with aggregating a data range.
Excel SUMPRODUCT in Business Planning
The Excel SUMPRODUCT function is one of the more flexible, powerful and practical tools to compute numbers in business planning. The length and breadth of Excel SUMPRODUCT speaks for itself. This article and video will identify its value in business planning, to perform both aggregation and multiplication of financial variables in a financial model.
Sum an Excel range – ignoring blank, text or error cells
This blog and video will demonstrate two alternative ways, using Excel SUMIFS function, to sum a range ignoring blank, text or error cells.
Reverse HLOOKUP in Excel
This is a follow-up to a similar article on reverse VLOOKUP options. There will be instances where a reverse HLOOKUP, like a VLOOKUP, is required in a financial model. The following feature will outline two alternate approaches, which enable a form of reverse HLOOKUP.