SpreadJS 14
Best Practices / Avoid Using Volatile Functions
In This Topic
    Avoid Using Volatile Functions
    In This Topic

    For common functions, the values are recalculated only when the dependent cell value is changed.

    But, in case of volatile functions (for example- INDIRECT function, RAND function, NOW function, TODAY function), the values are recalculated everytime when the cell value is changed, regardless of whether the cell is in the dependency tree or not. This leaves a negative impact on the overall performance in terms of efficiency because the calculation engine recalculates repeatedly whenever the spreadsheet is opened or refreshed.

    Therefore, it is recommended that users should avoid using any of the volatile functions. This will help you achieve optimized performance particularly when you need to deal with spreadsheets that involve huge data calculations.

    Alternative Solution

    If you're working with a spreadsheet that requires the frequent use of volatile functions and you can't avoid them; then the alternative solution is to avoid the nesting of volatile functions to obtain the cell value (primarily with the array formulas). Rather, you can simply set the volatile function in a particular cell lying on the spreadsheet and create the reference to that cell from the other non-volatile functions. Doing this will significantly reduce the amount of negative impact that volatile functions leave on the performance.