Spreadsheet Analytical Functions With Loop-Based Output Ordering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing spreadsheet applications lack the capability for users to perform repetitive calculations efficiently, as most users are not proficient in using Pivot Tables or embedded programming languages, and Pivot Tables are limited in the types of calculations they can perform.
Innovation Solution
A family of predefined formula spreadsheet functions that allow users to create programming loop equivalents using familiar range functions and algebraic operations, enabling data filtering and output ordering, and supporting single and multivariable compound or nested loops, with options for data constraints and output customization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If Pivot Tables are used for repetitive calculations, then calculations can be performed, but the device complexity increases and ease of operation decreases
Solution Approach 1:
The patent extracts the loop capability from complex Pivot Tables and embedded programming languages, creating a simplified iterative function that can be directly implemented in spreadsheet cells. This separates the repetitive calculation logic from the complex data structure setup, allowing users to perform iterative calculations without configuring Pivot Tables or writing programming code.
Solution Approach 2:
The patent introduces an intermediary iterative function that bridges the gap between simple spreadsheet functions and complex programming loops. This function acts as a mediator, providing loop capability through a simplified interface that translates high-level iterative logic into executable spreadsheet operations, eliminating the need for users to learn Pivot Tables or programming languages.
2Productivity
If Pivot Tables are used for calculations, then repetitive calculations can be performed, but the ease of operation decreases due to specialized knowledge required
Solution Approach 1:
The patent extracts the loop capability from complex Pivot Tables and embedded programming languages, creating a simplified iterative function that can be directly implemented in spreadsheet cells. This separates the repetitive calculation logic from the complex data structure setup, allowing users to perform iterative calculations without configuring Pivot Tables or writing programming code.
Solution Approach 2:
The iterative function is designed to be self-contained and self-explanatory, requiring no specialized knowledge of Pivot Tables or programming. Users can directly write iterative logic in spreadsheet cells using familiar function syntax, making the system self-sufficient and eliminating the learning curve associated with specialized Pivot Table configuration.
3Productivity
If Pivot Tables are used, then calculations can be performed, but adaptability decreases due to limited function combinations
Solution Approach 1:
The patent creates a universal iterative function framework that can accommodate any combination of spreadsheet functions, algebraic operators, and data sources. This single function serves multiple purposes: it can perform simple iterative calculations, complex multi-function combinations, data filtering, sorting, and aggregation, replacing the need for multiple specialized Pivot Table configurations.
Solution Approach 2:
The iterative function provides dynamic adaptability by allowing users to define the iteration logic, data sources, and output formats flexibly within the same function framework. Users can dynamically adjust the number of iterations, the functions applied at each step, and the sorting/ordering of results, enabling the system to adapt to various calculation scenarios without requiring separate Pivot Table setups.
Data Source
AI summary
The disclosed technology creates a family of (predefined formula) spreadsheet functions which allows users to create programming loop equivalents in their regular spreadsheet cells employing familiar range functions (e.g., SUM, COUNT, MIN, MAX, etc.) with data filtering and output selection. The data can be sourced from multiple cells within the spreadsheet or a broad spectrum of numeric, date and text data not stored in a spreadsheet, including data not discretely defined. The technology disclosed can use as inputs either cell ranges or Non-Spreadsheet Cell (NSC) data formulas. The capability allows users to specify standardized or highly custom calculations capable of executing millions of loops through a (predefined formula) spreadsheet function.