Dynamic Spreadsheet Recalculation Scope Using External References
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Recalculations in spreadsheet applications are performed globally, affecting all open workbooks, which can degrade user experience and waste processing resources due to volatile functions in inactive workbooks.
Innovation Solution
A calculation manager identifies external references within a workbook to dynamically limit the scope of recalculations based on factors such as viewport and real-time data status, performing recalculations only within the identified scope.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If global recalculation is performed to ensure all cells are updated, then data consistency across all workbooks is improved, but processing time and resource consumption increase due to unnecessary recalculations in inactive workbooks
Solution Approach 1:
The patent segments the global recalculation into a targeted local recalculation by identifying and isolating only the specific workbook and cell ranges that are actually affected by the triggering event. This divides the large recalculation task into smaller, relevant portions, eliminating unnecessary processing in inactive workbooks while maintaining data consistency where needed.
Solution Approach 2:
The patent applies local quality by determining the specific scope of recalculation based on the triggering event's location and dependencies. Instead of treating all workbooks uniformly, the system identifies which workbooks and cell ranges require updates based on their actual dependency relationships, applying recalculation only to the affected local areas.
2Reliability
If global recalculation is performed to update all dirty cells, then all out-of-date cells are brought current, but processing resources are wasted on cells not dependent on the triggering cell
Solution Approach 1:
The patent performs preliminary action by pre-calculating and storing dependency relationships between cells and workbooks before the recalculation event occurs. This metadata about which cells depend on which is prepared in advance, allowing the system to quickly identify the exact scope of recalculation needed without scanning all cells during the triggering event.
Solution Approach 2:
The patent segments the recalculation scope by using dependency graphs to identify only the specific cell chains that need updating. This breaks down the potentially massive recalculation task into a targeted set of dependent cells, eliminating waste on independent cells that don't need updating.
3Reliability
If volatile functions are present in all workbooks, then real-time data is always available, but user experience degrades due to frequent recalculations in inactive workbooks
Solution Approach 1:
The patent applies local quality by treating different workbooks differently based on their activity state and dependency relationships. Active workbooks with volatile functions undergo full recalculation to maintain real-time data, while inactive workbooks are excluded from recalculation unless directly affected, preserving user experience by avoiding unnecessary processing in background workbooks.
Solution Approach 2:
The patent implements dynamics by making the recalculation scope adaptive and flexible rather than fixed. The system dynamically adjusts the recalculation boundaries based on the triggering event's characteristics, the workbook's activity state, and dependency relationships, allowing real-time data updates where needed while avoiding unnecessary recalculations that degrade user experience.
Data Source
AI summary
In various implementations, a calculation manger identifies one or more external references within a workbook, wherein the workbook resides in a spreadsheet environment comprising multiple workbooks. The recalculation manager then identifies a scope of a recalculation, triggered by an event in the workbook, based on one or more factors comprising the one or more external references. A recalculation engine then performs the recalculation within the identified scope.