Dynamic Thresholds for Spreadsheet Conditional Formatting
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conditional formatting in spreadsheets is limited by fixed threshold values, restricted format options, and a maximum of three conditions, which restricts its ability to fully analyze and represent data effectively.
Innovation Solution
Implementing dynamic thresholds and variable formatting rules that allow for the use of 'Highest value', 'Lowest value', 'Middle value', 'Percentile', 'Percent', and 'Formula' threshold types, enabling more flexible and dynamic formatting based on cell values, and allowing multiple formats to be applied to a range of cells.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If fixed threshold values are used in conditional formatting, then the formatting rules are simple and easy to implement, but the formatting cannot adapt to varying data distributions and loses analytical nuance
Solution Approach 1:
The patent transforms static fixed threshold values into dynamic thresholds that automatically adjust based on data distribution characteristics. The system calculates thresholds dynamically using statistical methods (percentiles, standard deviations) rather than relying on predetermined fixed values, enabling the formatting rules to adapt to varying data distributions while maintaining automated operation.
Solution Approach 2:
The patent changes the parameter basis for threshold determination from fixed user-defined values to dynamically calculated statistical parameters. By computing thresholds based on data characteristics such as percentiles, mean, and standard deviation, the system enables formatting rules to adapt to different data distributions without requiring manual reconfiguration.
2Adaptability or versatility
If multiple formats are applied to cells based on complex conditions, then the data representation becomes more nuanced and dynamic, but the number of conditions is limited to a maximum of three in traditional systems
Solution Approach 1:
The patent segments the conditional formatting system into multiple independent format rules that can be applied simultaneously to cell ranges. Instead of being limited to three conditions, the system allows numerous format rules with different threshold types (percentile, standard deviation, fixed values) to be layered and combined, enabling nuanced multi-dimensional data representation through stacked formatting effects.
3Loss of information
If traditional conditional formatting with fixed thresholds is used, then the implementation is straightforward, but the visual analysis capability is limited and cannot provide rich reporting insights
Solution Approach 1:
The patent enables the conditional formatting system to automatically calculate and adjust its own threshold values based on the underlying data distribution. The system performs self-service by computing statistical parameters (percentiles, mean, standard deviation) and using these to dynamically set formatting thresholds, eliminating the need for manual threshold configuration while enhancing analytical insight.
Data Source
AI summary
Generally described, embodiments of the present invention provide the ability to utilize dynamic thresholds and dynamic threshold values when generating variable formatting rules to be applied to a range of cells. Dynamic thresholds include, but are not limited to, “Highest Value,”“Middle Value,”“Lowest Value,”“Number,”“Percent,”“Percentile,” and “Formula.” When using a dynamic threshold, dynamic threshold values are determined based on values contained in a selected range of cells.