Volatile formula

[Daveydoodles]

Hi all,

I'm in the process of replacing a series of formulae which use OFFSET asI wish to avoid using volatile functions in what is a very large and interconnected spreadsheet.

The formula I'm using to replace it is;

=SUMIFS(INDEX('CBL IFRS'!analyst_data_2,,MATCH('MI Summary'!H$14,'CBL IFRS'!$B$1:$P$1,0)),'CBL IFRS'!analyst_row_2,'MI Summary'!$D24)

Is anyone able to tell me if this will successfully avoid volatility? I've been reading that SUMIFS can effectively become a volatile function under certain circumstances - but I'm unsure if this will qualify.

 

[Ron Coderre]

The issue you're concerned about occurs for the SUMIF function.
See Charles Williams comments at his website: Volatile Excel Functions -Decision Models

Consequently, I don't see your approach creating volatility.

 

[Daveydoodles]

Thanks for your feedback Ron, I'll go ahead with this formula