Pattern-Based Formula Filling in Spreadsheets
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current spreadsheet applications are inefficient and error-prone when handling long lists of formulas, particularly in updating references and maintaining complex, repetitive formula patterns, leading to code duplication and manual maintenance challenges.
Innovation Solution
A method and system that allow users to define patterns of data and formula sequences using a graphical user interface (GUI), enabling automatic extrapolation and application of these patterns on a canvas, independent of formula references, to efficiently manage complex calculations and reduce manual copy-pasting.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If formulas are copied manually multiple times into target cells, then the formulas can be populated in the spreadsheet, but the references to source data are not updated automatically and errors occur
Solution Approach 1:
The patent segments the formula copying process into distinct components: source cell identification, target cell pattern definition, and automatic reference updating. By breaking down the manual copy-paste operation into these segments, the system can automatically track and update references for each segment, ensuring accuracy while maintaining ease of use.
Solution Approach 2:
The patent introduces an intermediary mechanism that acts as a bridge between the source formulas and target cells. This intermediary automatically manages the reference updating process, translating source cell references into correct target cell references based on defined patterns, thereby eliminating manual reference adjustment and preventing errors.
2Adaptability or versatility
If the relative position of cells is modified by inserting rows or columns, then the spreadsheet can be adapted to new requirements, but the references in formulas are not updated automatically
Solution Approach 1:
The patent implements dynamic reference tracking that automatically adapts when the spreadsheet structure changes. When rows or columns are inserted, the system dynamically recalculates the positions of cells and updates all formula references accordingly, maintaining both structural flexibility and formula accuracy without manual intervention.
Solution Approach 2:
The patent establishes a feedback mechanism where the system continuously monitors spreadsheet structure changes and automatically responds by updating formula references. This closed-loop approach ensures that any modification to the spreadsheet layout is immediately reflected in the formulas, eliminating the need for manual maintenance.
3Productivity
If multiple copies of formulas are created manually, then the formulas can be reused across different locations, but massive code duplication occurs and manual maintenance becomes prohibitively complex
Solution Approach 1:
The patent creates a universal formula management system where a single source formula can serve multiple target locations through pattern-based replication. The system maintains a universal reference structure that automatically adapts to different target positions, allowing one formula to fulfill multiple functions across the spreadsheet without creating separate copies that need individual maintenance.
Solution Approach 2:
The patent merges multiple formula instances into a single managed entity. Instead of creating separate copies that exist independently, the system combines them into a unified structure where changes to the source formula automatically propagate to all targets, eliminating code duplication while maintaining the ability to reuse formulas across different locations.
Data Source
AI summary
A computer implemented method includes receiving one or more data sequences and formula sequences, each formula referencing one or more of the data values of the data sequences; providing a GUI enabling a user to define a pattern including at least one data element representing a data sequence and at least one formula element representing a formula sequence, the GUI enabling the user to define the spatial relationship of the data elements and formula elements in the pattern, applying the pattern on a canvas of an electronic document multiple times, thereby filling canvas elements mapped to a data element with data values and filling canvas elements mapped to a formula element with formulas or formula results, until all data values of one of the data sequences or all formulas of one of the formula sequences have been filled once into the canvas.