Edit

Volatile values in functions

  • Article

Volatile functions are functions in which the value changes each time the cell is calculated. The value can change even if none of the function's arguments change. These functions recalculate every time Excel recalculates. For example, imagine a cell that calls the function NOW. Every time NOW is called, it will automatically return the current date and time.

Important

Note that Excel custom functions are available on the following platforms.

  • Office on Windows
    • Microsoft 365 subscription
    • retail perpetual Office 2016 and later
  • Office on Mac
  • Office on the web

Excel custom functions are currently not supported in the following:

  • Office on iPad
  • volume-licensed perpetual versions of Office 2019 or earlier

Excel contains several built-in volatile functions, such as RAND and TODAY. For a comprehensive list of Excel's volatile functions, see Volatile and Non-Volatile Functions.

Custom functions allow you to create your own volatile functions, which may be useful when handling dates, times, random numbers, and modeling. For example, Monte Carlo simulations require the generation of random inputs to determine an optimal solution.

If choosing to autogenerate your JSON file, declare a volatile function with the JSDoc comment tag @volatile. From more information on autogeneration, see Autogenerate JSON metadata for custom functions.

An example of a volatile custom function follows, which simulates rolling a six-sided dice.

GIF showing a custom function returning a random value to simulate rolling a six-sided dice.

/**
 * Simulates rolling a 6-sided dice.
 * @customfunction
 * @volatile
 */
function roll6sided() {
  return Math.floor(Math.random() * 6) + 1;
}

Next steps

See also