Spreadsheet Bucketing Functions for Grouped Cell Calculations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing spreadsheet functions lack the ability to perform bucketed or grouped data calculations using regular cell functional formulas, limiting the capabilities of PivotTables and requiring users to learn specialized features or functions, which are often avoided due to complexity.
Innovation Solution
A family of spreadsheet functions that enable users to create bucketed or grouped data values usable in cell function calculations, supporting automatic filling, label alteration, data constraining, and filtering, and compatible with a broad range of functions including SUM, MIN, and STDEV, with options for date and text bucketing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If PivotTable is used for bucketing or grouping data, then data analysis capability is improved, but device complexity and ease of operation deteriorate because it is considered too complicated by many users
Solution Approach 1:
The patent extracts the bucketing and grouping functionality from the complex PivotTable interface and implements it as a simple spreadsheet function that can be used directly in cell formulas. This allows users to access grouping capabilities without needing to learn or use the complicated PivotTable feature, effectively taking out the useful functionality and separating it from the complexity.
Solution Approach 2:
The patent creates a universal grouping function that can be used with any spreadsheet function (SUM, COUNT, AVERAGE, etc.) rather than being limited to PivotTable operations. This multi-functional approach allows the same grouping mechanism to work across different contexts and functions, making it both versatile and easy to use.
2Adaptability or versatility
If specialized functions like GETPIVOTDATA or CUBE functions are used, then bucketed data can be used in separate cell calculations, but device complexity increases and ease of operation decreases
Solution Approach 1:
The patent extracts the specialized GETPIVOTDATA and CUBE function capabilities and replaces them with a simpler grouping function that works directly with regular spreadsheet functions. This eliminates the need for specialized functions while maintaining the ability to use bucketed data in separate cell calculations.
Solution Approach 2:
The patent creates a simplified copy of the PivotTable grouping functionality that can be used directly in formulas without requiring the complex specialized functions. This copied functionality maintains the essential grouping capability while removing the complexity of the original implementation.
3Ease of operation
If traditional spreadsheet functions are used, then ease of operation is maintained, but adaptability deteriorates because none can perform bucketed or grouped data calculations
Solution Approach 1:
The patent enhances traditional spreadsheet functions by adding grouping capability that works with any function (SUM, COUNT, AVERAGE, MIN, MAX, etc.). This universal approach maintains the ease of using familiar functions while adding the adaptability to perform grouped calculations, allowing users to combine simplicity with enhanced functionality.
Solution Approach 2:
The patent modifies the parameter structure of spreadsheet functions to include grouping parameters that allow users to specify how data should be bucketed and grouped. This parameter change enables traditional functions to perform grouped calculations without changing the basic function syntax or user interface, maintaining ease of operation while adding versatility.
Data Source
AI summary
The disclosed technology creates new families of (predefined formula) spreadsheet functions which allow users to bucket values, supports use of those bucketing functions in other functions, and adds value bucketing capabilities as an option in existing spreadsheet functions. The technology disclosed can use as inputs either cell ranges or Non-Spreadsheet Cell (NSC) data formulas. The capability allows users to bucket numerical, text and time/date values.