Dynamic thresholds for conditional formats

• US 7,770,100 B2
• Filed: 02/27/2006
• Issued: 08/03/2010
• Est. Priority Date: 02/27/2006
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Vigesaa, Eric B, Chamberlain, Benjamin C, Street, Christopher M, Jones, Bruce Cordell
• Primary Examiner(s): Hutton; Doug
• Assistant Examiner(s): Dyer; Andrew R
• Application Number:
  US11/364,377
• Publication Number:
  US 20070204212A1
• Time in Patent Office: 1,618 Days
• Field of Search: 715/212, 715/217, 715/219
• US Class Current: 715/217
• CPC Class Codes: G06F 40/103 Formatting, i.e. changing of presentation of documents automatic justification G06F40/189; automatic line break hyphenation G06F40/191
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

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.

Claims

1. A computer-readable storage medium having computer executable components for determining dynamic threshold values for variable formatting a range of cells within a spreadsheet, comprising:

a variable format command component maintaining a variable format for application to the range of cells, the format applied to at least one cell in the range of cells being dynamically determined;

a threshold type component identifying a threshold type that utilizes a dynamic threshold value;

a dynamic threshold value component for determining a value for use as the dynamic threshold value;

wherein the value is determined from values contained in a plurality of cells, and wherein the plurality of cells is different than the range of cells;

wherein the variable format command component dynamically determines the variable format for the at least one cell in the range of cells based upon the threshold type and the determined threshold value; and

wherein the threshold type is a lowest value, a highest value, a middle value, a percent, a percentile, or a function.

2. The computer-readable storage medium of claim 1, wherein the dynamic threshold value is determined by evaluating values contained in the plurality of cells based on the selected threshold type and based on a comparison of the values contained in the plurality of cells.

3. The computer-readable storage medium of claim 1, wherein the threshold type that utilizes a dynamic threshold value is the Lowest value threshold type;

and

wherein the dynamic threshold value is determined to be a lowest value contained in the plurality of cells.

4. The computer-readable storage medium of claim 1, wherein the threshold type that utilizes a dynamic threshold value is the Highest value threshold type;

and

wherein the dynamic threshold value is determined to be a highest value contained in the plurality of cells.

5. The computer-readable storage medium of claim 1, wherein the threshold type that utilizes a dynamic threshold value is the Middle value threshold type;

and

wherein the dynamic threshold value is determined to be a middle value contained in the plurality of cells.

6. The computer-readable storage medium of claim 1, wherein the threshold type that utilizes a dynamic threshold value is the Percent threshold type;

and

wherein the dynamic threshold value is determined to be a delta value between a minimum value in the plurality of cells and a maximum value in the plurality of cells.

7. The computer-readable storage medium of claim 1, wherein the threshold type that utilizes a dynamic threshold value is the Percentile value threshold type;

and

wherein the dynamic threshold value is determined to be a value selected from the plurality of data cells that is a percentile of the values contained in the plurality of data cells.

8. The computer-readable storage medium of claim 1, wherein the threshold type that utilizes a dynamic threshold value is the Function threshold type;

and

wherein the dynamic threshold value is determined based on a computation of a provided function that utilizes values from the plurality of cells.

9. A method implemented on a computer-readable storage medium for determining a dynamic threshold value, comprising:

maintaining a variable format for application to a range of cells within a spreadsheet, the format applied to at least one cell in the range of cells being dynamically determined;

identifying a threshold type that utilizes a dynamic threshold value;

determining a value for use as the dynamic threshold value;

wherein the value is determined from values contained in a plurality of cells, and wherein the plurality of cells is different than the range of cells;

wherein the variable format is dynamically determined for the at least one cell in the range of cells based upon the threshold type and the determined threshold value; and

wherein the threshold type is a lowest value, a highest value, a middle value, a percent, a percentile, or a function;

identifying a plurality of cells for which a dynamic threshold value is to be determined, the plurality of cells including a range of cells;

evaluating the values contained in the range of cells to identify a value matching a threshold type requirement;

selecting the identified value as the dynamic threshold value for the threshold type; and

using the dynamic threshold value to determine a variable format to be applied to the range of cells, the format applied to at least one cell in the range of cells being dynamically determined,

wherein the plurality of cells is different than the range of cells.

10. The method of claim 9, wherein the dynamic threshold value is determined in response to a change in a value contained in the range of cells.

11. The method of claim 9, wherein the dynamic threshold value is determined in response to a change in a number of cells contained in the range of cells.

12. The method of claim 9, wherein the format applied to at least one cell in the range of cells is based on values contained in the range of cells compared to the value of the dynamic threshold value.

13. The method of claim 9, wherein based on the dynamic threshold value, a formatting is applied to a plurality of the range of cells.

14. A method implemented on a computer-readable storage medium for formatting a range of cells within a spreadsheet using variable formats, the method comprising:

identifying a plurality of cells comprising three or more cells, at least two cells of the plurality of cells comprising a range of cells, each cell in the range of cells having a numerical value;

defining a variable format to be applied to the range of cells, wherein the variable format includes a first threshold point having a threshold type, a second threshold point having the threshold type, a first background fill defining a first color shading or first pattern density, a second background fill defining a second color shading or second pattern density, and a format style defining a graphical representation of data;

determining a dynamic threshold value as a value for the threshold type;

evaluating the numeric value of each cell in the range of cells and determining a first value and a second value based on the threshold type, the first value corresponding to the first threshold point and the second value corresponding to the second threshold point;

applying the first background fill to a first cell in the range of cells corresponding to the first value and applying the second background fill to a second cell in the range of cells corresponding to the second value, the first background fill and the second background fill having the format style defined by the variable format; and

applying additional background fill to each cell having a value between the first value and the second value so that the additional background fill varies in a linear manner for each cell in the range of cells from the cell having the first value to the cell having the second value, the additional background fill having the format style defined by the variable format, the additional background fill being applied without being selected by a user,

wherein the plurality of cells is different than the range of cells; and

wherein the threshold type is a lowest value, a highest value, a middle value, a percent, a percentile, or a function.

15. The method of claim 14, wherein the dynamic threshold value is determined based on values contained in the range of cells.

16. The method of claim 15, wherein the dynamic threshold value is further determined based on the threshold type.

17. The method of claim 14, wherein the variable format evaluates the values contained in the range of cells based on the dynamic threshold value and threshold type and applies a background fill representative of the evaluation.

18. The method of claim 14, wherein the dynamic threshold value may be automatically updated in a response to a change in the range of cells or a change in the values contained in the range of cells.