How bad is having lots of volatile functions index() in a big spreadsheet?

**jonnyyyl** · 08-04-2014, 02:35 AM

What's so bad about have a lot of volatile functions, provided you don't change them?

Hypothetically speaking, suppose if I were to have a worksheet with over 10,000 cells with index(indirect(range1),match(date,indirect(range2),0)). Ranges 1 and 2 are determined by the number of date entries. They are unlikely to change, only when new date entries are added because new increased row numbers.

Ie.
A1 = Jan 1, 2014 B1 = food
...
A20 = Feb 4, 2014 B20 = chocolate

A reference cell at C1 = row(max(A:A)) = 20 (to find max row number)

In a separate worksheet, I want to reference items from column B, based on date entry in column A.
I retrieve the data in the operation below:

=index(indirect("Sheet1!$B$1:"&'Sheet1'!$C$1",match(date here,indirect("Sheet1!A1:"&'Sheet!A'$C$1),0))
(by the way, am I writing this correctly?)

Far into a month ahead or something, suppose I add new date entry in column A, making C1 = 21 now.

Now, suppose I multiply this type of operation across over 10,000 cells. What's wrong with this? What problems can I face?

**Ace_XL** · 08-04-2014, 02:47 AM

Volatile functions slow down Excel due to long processing times every time Excel re-calculates. See here

http://www.decisionmodels.com/calcsecretsi.htm
http://chandoo.org/wp/2014/03/03/han...-are-dynamite/

In your example you'd be better off using the INDEX() function rather than INDIRECT()

**samba_ravi** · 08-04-2014, 02:48 AM

attach sample work book with desired results

**FDibbins** · 08-04-2014, 11:13 AM

What's so bad about have a lot of volatile functions, provided you don't change them?

Thats the problem, even if you dont change them, volatile formulas recalc with every worksheet change. So even if you change nothing that the formula in A1 references - or is even remotely linked to - it will still recalc every time to enter (or even edit) anything in the workbook