Reusable Spreadsheet Function Definition via Formal Parameter Extraction

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Current spreadsheet applications do not provide intuitive means for users to reuse complex calculations on different sets of input parameter values, leading to code duplication, maintenance issues, and errors, as they lack support for specifying reusable functions not tightly coupled to specific cells or input/output parameters.

Innovation Solution

A method for specifying and reusing spreadsheet functions by identifying formal output and input parameters, and a function body within a spreadsheet document, allowing users to apply the function on multiple input sets simultaneously without duplicating cells, using graphical user interface elements and keywords like SUBCALC to represent the function.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If users specify complex calculations using multiple formula cells referencing each other, then the calculation capability is achieved, but code duplication and maintenance difficulty increase when reusing on different input data sets

Engineering Contradiction:

Improvereusability of complex calculationVSAvoidcomplexity of spreadsheet structure

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent extracts the complex calculation logic from multiple scattered formula cells and consolidates it into a single reusable function definition. This function can then be applied to multiple different input data sets without duplicating the underlying calculation structure, thereby improving reusability while reducing structural complexity.

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent creates a universal function definition that can serve multiple purposes across different input data sets. Instead of creating separate formula structures for each calculation scenario, a single function definition can be universally applied to various input parameters, enhancing adaptability and reducing redundancy.

Inventive Principle:

Principle #6Universality (Multi-functionality)

2Productivity

If users copy and paste cells to reuse complex calculations on different input data sets, then the calculation can be applied multiple times, but errors and maintenance issues increase

Engineering Contradiction:

Improveefficiency of reusing calculationsVSAvoidaccuracy of calculated results

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

The patent implements a controlled copying mechanism where a function definition can be referenced multiple times across different input data sets. Unlike simple cell copying that duplicates potentially error-prone formulas, this function-based copying ensures consistent and reliable application of the same calculation logic to different inputs, improving both productivity and reliability.

Inventive Principle:

Principle #26Copying

3Adaptability or versatility

If users employ the scenario manager to evaluate different input parameter sets, then the impact analysis is enabled, but results cannot be displayed simultaneously and cannot be reused by other functions

Engineering Contradiction:

Improvecapability to evaluate multiple scenariosVSAvoidconvenience of result display and reuse

Core Design Contradiction:

Adaptability or versatilityVSEase of operation

Solution Approach 1:

The patent creates spreadsheet functions with formal parameters that can universally accept different input data sets and produce results that can be simultaneously displayed and reused by other functions. This universal function design overcomes the scenario manager's limitations by enabling both simultaneous result display and inter-function reuse while maintaining ease of operation.

Inventive Principle:

Principle #6Universality (Multi-functionality)

4Adaptability or versatility

If users specify functions using programming languages like VBA, then reusable functions can be created, but programming skills are required which most spreadsheet users lack

Engineering Contradiction:

Improvereusability of spreadsheet functionsVSAvoidease of function specification

Core Design Contradiction:

Adaptability or versatilityVSEase of operation

Solution Approach 1:

The patent provides a simplified, accessible interface for creating reusable spreadsheet functions that does not require programming knowledge. By offering an alternative to complex programming languages like VBA, the system makes function creation accessible to the broad majority of spreadsheet users, improving ease of operation while maintaining reusability.

Inventive Principle:

Principle #27Cheap short-living objects (Disposable)

Data Source

PatentUS9075787B2Defining a reusable spreadsheet-function by extracting the function from a complex calculation in a spreadsheet document

Publication Date: 2015.07.07 THINK CELL SOFTWARE

AI summary

The invention relates to a computer-implemented method for displaying data values calculated by a spreadsheet-function, the method comprising:Specifying one or more formal output parameters by specifying, for each formal output parameter, a first cell of a spreadsheet document,Specifying one or more formal input parameters by specifying, for each formal input parameter, a second cell,Specifying a spreadsheet-function body,wherein the specified spreadsheet-function in operation provides a user with the option for:Calculating a first output parameter value,Displaying the one or more first output parameter values or a data value having been derived thereof in a 3rd cell,Calculating a second output parameter value, andDisplaying the one or more second output parameter values or a data value having been derived thereof in a 4th cell, wherein the 3rd cell and the 4th cell display their respective data content within the spreadsheet document at the same time.