[Solved] Limiting Calculations

[sunjomar]

I've created a payroll spreadsheet using OpenOffice Calc over the past few years, gradually growing in size over time. I had to implement (with the assistance of this forum) several ways to reduce the overall file size and memory usage over time. I recently implemented the SUMIF function to successfully calculate huge ranges based on specific criteria. While the SUMIF function is very effective in reducing the overall file size and memory usage of the spreadsheet, it understandably does slow down the recalculation processing of the spreadsheet considerably. Fully aware of the options to switch off the AutoCalculate function and use the F9 Recalculate function to limit continuous recalculations, my question goes little bit into the underlying technical functionality of spreadsheets being a database:

(1) Understanding that the spreadsheet will continuously recalculate all mentioned SUMIF functions, but understanding that not all such SUMIF functions are effectively in use by the user of the payroll spreadsheet, would it help to speedup the recalculation processing by adding an IF condition prior to each SUMIF function? E g, in stead of =SUMIF(B1:B100;">0";Z1:Z100), would it help to change it to =IF(A1="Active";SUMIF(B1:B100;">0";Z1:Z100);0)? The idea is simply that the IF conditions will effectively avoid calculating the SUMIF functions if not in use.

(2) Can IF conditions otherwise be used the limit the recalculation of large parts of the spreadsheet? E g, by asking the user if the employer is registered for employees tax, a "No" answer will avoid all tax calculations based on this IF condition. One must however be aware that additional IF functions may again increase the overall file size and memory usage.

(3) Are there other effective possibilities to limit and/or speedup the continuous recalculation process? E g, will a larger faster computer with more memory be more effective?

[MrProgrammer]

Understanding that the spreadsheet will continuously recalculate all mentioned SUMIF functions, but …

Calc does not re-evaluate =SUMIF(B1:B100;">0";Z1:Z100) until a change is made to either B1:B100, Z1:Z100, or a cell used by a formula in one of those ranges. If the ranges contain numbers, not formulas, and you change D5, Calc understands it does not need to re-evaluate =SUMIF(B1:B100;">0";Z1:Z100). Internally, Calc has a list of which cells depend on D5, and (Step 1) only those are re-evaluated. The process is then repeated (Step 2) on cells changed by Step 1. This continues until no further evaluations are needed.

A few "volatile" functions (RAND, NOW, etc.) do require some cells to be recalculated anytime a change is made. And for a few functions (OFFSET, INDIRECT, etc.) Calc can't determine, without recalculation, what their dependents are so they must be recalculated each time. This is a complicated subject. Read about volatile functions in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum. As far as I know, SUMIF is not a volatile function.