Dynamic thresholds for conditional formats

• US 8,914,717 B2
• Filed: 06/07/2010
• Issued: 12/16/2014
• Est. Priority Date: 02/27/2006
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Chamberlain, Benjamin C., Jones, Bruce Cordell, Street, Christopher M., Vigesaa, Eric B.
• Primary Examiner(s): DYER, ANDREW R
• Application Number:
  US12/794,876
• Publication Number:
  US 20100251090A1
• Time in Patent Office: 1,653 Days
• Field of Search: 715/212, 715/215, 715/217
• US Class Current: 715/212
• 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 method for determining dynamic threshold values for variable formatting a range of cells, comprising:

determining a range of cells;

determining a format style, wherein the format style is one of an icon set;

data bars;

or a color scale;

determining a first threshold type, wherein the first threshold type is one of a value;

a percent;

a percentile;

a number;

or a formula;

determining a second threshold type, wherein the second threshold type is one of a value;

a percent;

a percentile;

a number;

or a formula;

evaluating the content of all of the cells in the range of cells to determine a first dynamic threshold value that is within the range of cells based on the first threshold type and a first threshold point; and

evaluating the content of all of the cells in the range of cells to determine a second dynamic threshold value that is within the range of cells based on the second threshold type and a second threshold point;

evaluating the content of all the cells in the range of cells to determine a format value for each cell in the range of cells; and

comparing the format value of each cell in the range of cells to the determined first dynamic threshold value and the determined second dynamic threshold value; and

applying the format style to each cell in the range of cells based on the comparison between the format value of each cell in the range of cells and the determined first dynamic threshold value and the determined second dynamic threshold value such that the format style of each cell in the range of cells varies according to the format value of the cell in relation to the determined first dynamic threshold value and the determined second dynamic threshold value.

2. The method of claim 1, wherein the first dynamic threshold value and the second dynamic threshold value are determined based on the selected threshold type and based on a comparison of the values within the range of cells.

3. The method of claim 1, wherein at least one of the first threshold type and second threshold type is a lowest value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a lowest value contained in the range of cells.

4. The method of claim 1, wherein at least one of the first threshold type and second threshold type is a highest value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a highest value contained in the range of cells.

5. The method of claim 1, wherein at least one of the first threshold type and second threshold type is a middle value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a middle value contained in the range of cells.

6. The method of claim 1, wherein at least one of the first threshold type and second threshold type is a percent threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a delta value between a minimum value in the range of cells and a maximum value in the range of cells.

7. The method of claim 1, wherein at least one of the first threshold type and second threshold type is a percentile value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a value selected from the range of cells that is a percentile of the values contained in the range of cells.

8. The method of claim 1, wherein at least one of the first threshold type and second threshold type is a function threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined based on a computation of a provided function that utilizes values from the range of cells.

9. The method of claim 1, wherein when the format style is the color style, the color applied to each of the cells in the range of cells is dynamically determined.

10. A computer storage device, having a processor and computer-executable instructions that, when executed by a processor, perform a method for determining dynamic threshold values for variable formatting a range of cells, the method comprising:

determining a range of cells;

determining a format style, wherein the format style is one of an icon set;

data bars;

or a color scale;

determining a first threshold type, wherein the first threshold type is one of a value;

a percent;

a percentile;

a number;

or a formula;

determining a second threshold type, wherein the second threshold type is one of a value;

a percent;

a percentile;

a number;

or a formula;

evaluating the content of all of the cells in the range of cells to determine a first dynamic threshold value that is within the range of cells based on the first threshold type and a first threshold point; and

evaluating the content of all of the cells in the range of cells to determine a second dynamic threshold value that is within the range of cells based on the second threshold type and a second threshold point;

evaluating the content of all the cells in the range of cells to determine a format value for each cell in the range of cells;

comparing the format value of each cell in the range of cells to the determined first dynamic threshold value and the determined second dynamic threshold value; and

applying the format style to each cell in the range of cells based on the comparison between the format value of each cell in the range of cells and the determined first dynamic threshold value and the determined second dynamic threshold value such that the format style of each cell in the range of cells varies according to the format value of the cell in relation to the determined first dynamic threshold value and the determined second dynamic threshold value.

11. The computer storage device of claim 10, wherein at least one of the first threshold type and second threshold type is a middle value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a middle value contained in the range of cells.

12. The computer storage device of claim 10, wherein at least one of the first threshold type and second threshold type is a percent threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a delta value between a minimum value in the range of cells and a maximum value in the range of cells.

13. The computer storage device of claim 10, wherein at least one of the first threshold type and second threshold type is a percentile value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a value selected from the range of cells that is a percentile of the values contained in the range of cells.

14. The computer storage device of claim 10, wherein at least one of the first threshold type and second threshold type is a function threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined based on a computation of a provided function that utilizes values from the range of cells.

15. The computer storage device of claim 10, wherein when the format style is the color style, the color applied to each of the cells in the range of cells is dynamically determined.

16. A system for determining dynamic threshold values for variable formatting a range of cells, the system comprising:

at least one processor;

a memory, communicatively coupled to the at least one processor and containing instructions that, when executed by the at least one processor, perform a method, the method comprising;

determining a range of cells;

determining a format style, wherein the format style is one of an icon set;

data bars;

or a color scale;

determining a first threshold type, wherein the first threshold type is one of a value;

a percent;

a percentile;

a number;

or a formula;

determining a second threshold type, wherein the second threshold type is one of a value;

a percent;

a percentile;

a number;

or a formula;

evaluating the content of all of the cells in the range of cells to determine a first dynamic threshold value that is within the range of cells based on the first threshold type and a first threshold point; and

evaluating the content of all of the cells in the range of cells to determine a second dynamic threshold value that is within the range of cells based on the second threshold type and a second threshold point;

evaluating the content of all the cells in the range of cells to determine a format value for each cell in the range of cells;

comparing the format value of each cell in the range of cells to the determined first dynamic threshold value and the determined second dynamic threshold value; and

applying the format style to each cell in the range of cells based on the comparison between the format value of each cell in the range of cells and the determined first dynamic threshold value and the determined second dynamic threshold value such that the format style of each cell in the range of cells varies according to the format value of the cell in relation to the determined first dynamic threshold value and the determined second dynamic threshold value.

17. The system of claim 16, wherein the first dynamic threshold value and the second dynamic threshold value are determined based on the selected threshold type and based on a comparison of the values within the range of cells.

18. The system of claim 16, wherein at least one of the first threshold type and second threshold type is a lowest value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a lowest value contained in the range of cells.

19. The system of claim 16, wherein at least one of the first threshold type and second threshold type is a highest value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a highest value contained in the range of cells.

20. The system of claim 16, wherein at least one of the first threshold type and second threshold type is a middle value threshold type;

and

wherein at least one of the first dynamic threshold value and the second dynamic threshold value is determined to be a middle value contained in the range of cells.