![]() ![]() ![]() Rand and RandBetween can be useful for creating test data in a model but, assuming that’s the case, you won’t be leaving those functions in place for very long.Microsoft's list contains some functions I’ve never used. Some I’ve seen used a bit: The list includes Now, Today, RandBetween, Offset, Indirect, Info, Cell, and SumIf ("depending on its arguments"). You can get a full list of volatile functions (= functions to minimise/ avoid) from Microsoft’s support documentation here. The volatile functions to worry about most Just like a little full fat ice cream is unlikely to do you much harm, introducing the tiniest volatile function to do one job in one part of your model is unlikely to make the whole thing fall over. But if there’s a risk of you needing to do the same kind of thing lots of times (perhaps because you've got a spreadsheet that's using macros to resolve circular references or run and output results), and you find yourself operating a few big models, to your armoury you might want to add alternatives to Excel’s volatile functions. The lesson here is, if you’re serious about best practice Excel financial modelling, filling your model with lots of volatile functions is not going to help file processing speed. Microsoft’s own support documentation acknowledges the drawbacks of volatile functions at. Microsoft guides us towards minimising volatile functions ![]() So, with that background, imagine there was a small class of slightly-evil functions that had crept into Excel. Wouldn’t you want to try and weed those awful functions out of your beautiful spreadsheets? This class of functions continuously forced Excel to walk out to the end of the branches of the tree. Not just once, when a change was made at the source, but continuously. That doesn’t mean that, if you’re operating big spreadsheets and you’d like to optimise them for speed, you can forget about doing things properly – just because we’ve got more processing power available to us these days. In the olden days (yes I am qualified to talk about the olden days), with less computing power at our fingertips, this was a real day-to-day issue in big models (say >10MB). That’s why you find some people thinking hard about optimising functions, reducing complexity of functions, and exactly how things should be wired up inside spreadsheets.Īs time’s gone on, we’re probably now at the stage where worrying about spreadsheet speed is less of an issue. To the point where, if you’ve got a spreadsheet that’s say >40MB and really slowing down your Excel, you’re probably in the zone where you should be asking yourself whether the task you’re trying to tackle is best solved using Excel, or whether you should be using another tool altogether. If you’re into your Excel, it really is quite interesting. You can read more about it from Microsoft here. What’s triggering the whole calculation event is the change in one particular cell. Depending on how often that cell’s going to change, and the extent and complexity of calculations branching off it, that whole recalculation event is going to suck more or less of Excel’s processing power, and potentially slow your computer down. Think for a second how Excel works. You change one thing in one place, and Excel has to look at everything that links from that cell and decide what else needs to be changed. If the cell you’ve just changed is the base of the tree Excel has to work its way out to the end of all the branches that link from that cell, making all the requisite changes. “Volatile” in the case of Excel refers to the list of busy functions, who never settle down and never stop. Excel’s volatile functions are like atoms at high temperature always shaking back and forth, never resting. Perhaps that’s why they attract the name “volatile” as a nod to definition (1) above – because they’re like an evaporating atom always busy shaking itself free. In the case of Excel, volatile functions aren’t those (1) whose usefulness is likely to evaporate when they get overworked and hot, or (2) become a little bit unstable and bounce around all over the place. That word “volatile” has cropped up in a couple of posts we’ve put out on LinkedIn recently (see this article from Rob Bayliss on making the most of your choice of Excel functions, and this article from Denis Battiston on constructing a model timeline for a long-lived asset). But what does that word “volatile” really mean when it’s mentioned in the same breath as “Excel”? ![]()
0 Comments
Leave a Reply. |