Recalculation of formulas
Calculation is the process of calculating formulas and then displaying the values of the results in cells.
By default, formulas in the document are recalculated automatically in the following cases:
–When you open a document with outdated formula values.
–When you change values in cells used in any formulas.
–When you enter a formula in an empty cell or in a cell that contains a value.
–When you insert or delete a row.
–When you insert or delete cells with a shift.
If there are a large number of formulas in a document, their automatic recalculation can make opening and working with the document difficult. In order to speed up work with such documents, MySpreadsheet provides a manual formula recalculation mode, where recalculation is performed at the user's request.
In manual formula recalculation mode:
–When you open a document and change the associated cells, no formulas are automatically recalculated.
–When you work with a document, formulas are recalculated only when you carry out manual operations on the cells that contain them.
–Manual recalculation of outdated formula values is available in the entire document, on the sheet, and in selected cells.
–When saving a document, recalculation of outdated formula values is performed only if the Before saving checkbox is selected.
Outdated values in formulas are irrelevant data that were previously calculated but are no longer relevant. They may occur as a result of data entry errors, errors in formulas and functions, data type mismatches, changes in table structure, errors in macros, or synchronization problems. MySpreadsheet stores information about the presence of such cells within the file itself.
When formulas are recalculated, references to data from external documents are not updated. To update them, follow the steps described in Reference to data in another document – Update data section.
The formula calculation process takes place in the background, without blocking the work with the application. If the calculation process does not end 0.6 seconds after it is started, a formula recalculation progress indicator appears in the Status bar.
The indicator is activated in manual recalculation mode, but in this case, the recalculation itself is not performed; only the analysis and marking of cells is performed.
If, at the moment of recalculation, you change a cell that affects the formulas being recalculated, the recalculation of cells that have not yet been calculated will be restarted.
If you close the document without saving it during the recalculation, the recalculation will be interrupted. If you close the document after confirming that you want to save the changes, a dialog box appears showing the recalculation progress and offering you the option to close the document without saving. Upon completion of the recalculation, the document will automatically close.
During recalculation, running macros and extensions is temporarily suspended. When you attempt to run them, a dialog box appears showing the progress of the recalculation. Once the recalculation is complete, the macro or extension will be run.
When commands that cause a full recalculation of formulas are called, for example, when inserting a row, cells with formulas that have not been previously loaded will display the #CALC! error instead of values from the cache.
During background recalculation, a number of actions are unavailable:
–Running the Quick print, Export to PDF, Page setup and print commands.
–Creating and updating pivot tables.
–Running the Save, Save as, Save as template, and Save to cloud commands.
–Autorecovery of documents (postponed until recalculation is complete).
–Selecting the formula recalculation mode.
–Running the Remove duplicates command.
–Running the Hide zero values command.
–Inserting and deleting cells with a shift.
–Operations with tables:
•Deletion.
•Conversion to a regular range.
•Addition or deletion of headers and totals.
