Is it possible to have a category ‘mirrored’ within one matrix so that it appears twice (or alternatively, can two categories within one matrix be linked so that they have the same items).
….A1 A2 A3 [A]
I can link categories between matrixes with ctrl-drag (but not within the same matrix). I can also copy a category, but it doesn’t link the old category, so that if I add items to the old category they don’t also add to the new.
The context is trying to calculate straight line depreciation for an asset class that has new assets added each year (and potentially removed as they reach their useful life).
I can do this in a single line formula using Quantrix (based on the application note on depreciation):
Year[THIS]:Real depreciation = Year[PREV]:Real depreciation +if(Asset classes::Standard life > 0,Year[PREV]:Real capex / Asset classes::Standard life,0) – if(Asset classes::Standard life > 0, Indirect(“Year[PREV-“&Asset classes::Standard life&”]:Real capex”)/Asset classes::Standard life,0) skip ‘1’,Total
But that approach isn’t exactly satisfactory. Using indirect() makes the formula unreadable, and a whole lot harder to verify. It needs to be even more complex to handle non-integer asset lives. So you lose some of the key benefits of Quantrix.
A common idiom used in excel is a triangle matrix, where the investment schedule is transposed so that it runs down the page, then depreciation is calculated separately for each year’s new assets. Although the result is the same as the indirect style formula above, this method has the advantage of being more readily verifiable, and more common. To do this though, you really need a matrix that is Year x Year. Although even then the formula would be quite complex I think.
Having said all that, depreciation like this is always a bit tricky, and I am open to suggestions for how else you might do this.