5 ms·
Matrix functions and named ranges are the most underused functionality of Excel. Collectively they can replace a large amount of the conventional spagetti VBA
by zjonsson 14y ago
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.
Simple example: CDO pricer using Gaussian Copula and base correlation
https://dl.dropbox.com/u/10755342/120626_Simple_CDO.xlsx https://dl.dropbox.com/u/10755342/120626_Simple_CDO.xlsx
- niggler 14y agoYou mean array formula (the {=....} jazz). Matrix functions refer to functions like MINVERSE
- zjonsson 14y agoNot 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))