Spreadsheet Functions with Named Range Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current spreadsheet systems are limited in their ability to handle array functions, which return multiple values as a single block of contiguous numbers without descriptive labels, making it difficult to create flexible user interfaces and requiring users to manually specify cell locations for input parameters.
Innovation Solution
The implementation of automatic parameter mappings in spreadsheet worksheet functions allows for multi-output functions, where input and output values are mapped to named ranges, enabling flexible layouts and automatic population of input parameters, with output values displayed next to descriptive labels.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Extent of automation
If array functions return multiple values as a single block of contiguous numbers, then the function can return multiple calculated values, but the user interface becomes inflexible and requires users to refer to documentation to understand what each value represents
Solution Approach 1:
The patent segments the contiguous array output into individually addressable named ranges, allowing each output value to be separately identified and accessed. This segmentation enables the array function to maintain its multi-output capability while providing flexible access to individual values through named ranges rather than requiring users to interpret positional indices in a contiguous block.
Solution Approach 2:
The patent introduces named ranges as an intermediary layer between the array function output and the user interface. These named ranges act as mediators that map abstract array positions to meaningful labels, allowing users to reference outputs by name rather than position, thereby improving interface flexibility without changing the underlying array computation.
2Productivity
If users must explicitly specify cell locations for each input parameter, then the function can process the data, but the operation becomes time-consuming and error-prone
Solution Approach 1:
The patent implements automatic parameter population where the spreadsheet system automatically retrieves and populates input parameters based on named ranges defined in the workbook. This self-service mechanism eliminates the need for users to manually specify cell locations for each parameter, allowing the data processing to proceed efficiently while reducing time loss and potential errors from manual input.
Solution Approach 2:
The patent requires users to pre-define named ranges for input parameters before calling the array function. This preliminary action of naming ranges allows the system to automatically map parameters during function execution, saving time during the actual data processing operation. The upfront effort of naming ranges enables subsequent automatic population without manual cell location specification.
3Device complexity
If array functions return contiguous blocks of numbers, then the calculation is simple, but it is impossible to create flexible user interfaces with descriptive labels
Solution Approach 1:
The patent adds a naming dimension to the array output by mapping array positions to named ranges. This dimensional addition allows the system to maintain simple contiguous array calculations while providing versatile named access points. The named ranges create an additional layer of abstraction that enables flexible interfaces without complicating the underlying calculation structure.
Data Source
AI summary
A system and methods for defining spreadsheet worksheet functions capable of returning more than one output value to a worksheet with the layout of the returned values being customizable through the use of named ranges on the worksheet. The mapping of an output value to a destination cell (or range) is achieved by using a naming convention, wherein the name of the output parameter as defined in the worksheet function or in an interactive coding environment is automatically mapped to a worksheet range having the same name.