If you've ever worked in a big spreadsheet with a thousands of rows of formulas, you may have noticed that Excel may recalculate when you change a value in a cell (or cells). Excel is supposed to be optimized such that only cells that depend on those cell(s) are recalculated. (See link section at the bottom)
There is an exception to that, i.e. Volatile Formulas. Volatile formulas are formulas that are recalculated every time any value anywhere in your workbook changes regardless of whether it affects the volatile formula or not. One such formula is RAND() - i.e. the formulas that generates a psuedo-random value between 0 and 1. Go ahead and try it. Put =RAND() in any cell in a worksheet, then type a value in another cell. You'll see that the value in the cell with RAND() will change. If you need a list of random numbers that doesn't change, you need to use RAND() to create that list, then copy and paste it back as values. Without knowing that RAND() is a volatile formula, you might not notice that any calculations that reference the cells with the volatile RAND() formula are changing often.
Additionally, if you have a spreadsheet with thousands of cells with a volatile formula, any change will result in those cells with volatile formulas in the being recalculated (regardless of whether the volatile formulas is dependent on the changed cell or any other cells affected by the changed cell) AND all cells that are dependent on those cells with volatile formulas - which could take a long time for some spreadsheets.
Does that mean Volatile Formulas are a no-no?
No, it does not. What it does mean is that you need to be aware of what formulas are volatile, and know that they may slow down your spreadsheet if there are a lot of cells with volatile formulas.
If it is calculation intensive, you may want to spend some time seeing if there is another approach that doesn't use volatile formulas, e.g. INDEX() instead of OFFSET().
All this being said and done, you're probably asking yourself "So, what formulas are volatile?". The first link below provides that information (also, there's Google).
Links
- Excel Recalculation: https://msdn.microsoft.com/en-us/library/office/bb687891.aspx
- Listing of Volailte Formulas: http://www.decisionmodels.com/calcsecretsi.htm