Matrix functions and named ranges are the most underused functionality of Excel. Collectively they can replace a large amount of the conventional spagetti VBA code, emphasizing Excel's true strength.
Not only that, regular algebraic operations also work with vectors/matrices as inputs, as long as you press CTRL-SHIFT-ENTER after editing. This means that most functions with complex MATCH and OFFSET can be replaced as well (i.e. SUM( (column_vector = cvalue) * (row vector = vvalue) * (matrix))
Simple example: CDO pricer using Gaussian Copula and base correlation https://dl.dropbox.com/u/10755342/120626_Simple_CDO.xlsx