System and method for a spreadsheet application supporting diverse formulaic functions

• US 20060161844A1
• Filed: 01/14/2005
• Published: 07/20/2006
• Est. Priority Date: 01/14/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Jones, Simon Loftus Peyton, Babanov, Alexander, McAmis, Monica, Pankratz, Jeremy R., Ellis, Charles D., Androski, Matthew J., Cory, Daniel P., Gainer, David F., Becker, Andrew J., Simkhay, Roy
• Granted Patent: US 8,726,143 B2
• Time in Patent Office: Days
• US Class Current: 715/212
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A spreadsheet application or other application supporting formulaic cells that automatically provides support for a number of diverse formulaic functions. The automatically provided diverse formulaic functions include a function returning the value of the formula logic if the logic can be evaluated without an error. Another function finds a value in a formulaic cell that is in the same row as a particular value from the first column and the same column as a particular value in the first row. Other functions allow a selection of a set of multiple lookup rows and columns for matching the values used as variables in the function. The spreadsheet application is further configured to automatically supply other functions in addition to those described.

Claims

1. A computer-readable medium that includes a spreadsheet application for providing a diverse set of functions within a spreadsheet document, comprising:

instantiating the spreadsheet document by launching the spreadsheet application on a computing device, wherein the spreadsheet document includes a formulaic cell; and

automatically supporting entry of at least one of a set of diverse functions in the formulaic cell such that a result is obtained in response to processing the entered function, the set of diverse functions comprising;

an IFERROR function, a NONERROR function, a TABLELOOKUP function, an HLOOKUPMULTIPLE function, a VLOOKUPMULTIPLE function, a WORD function, a WORDCOUNT function, a CONCATENATERANGE function, a DATEDIFFERENCE function, a DATEADD function, a WEEKNUMISO function, an AVERAGEIF function, an AVERAGEIFS function, a SUMIFS function, a COUNTIFS function, a WEIGHTEDAVERAGE function, a COUNTDISTINCT function, a SORTRANGE function, a DISTINCT function, and a FILTER function.

2. The computer-readable medium of claim 1, wherein the IFERROR function is further configured according to a first syntax to include:

a selectable expression that corresponds to an argument to be checked for an error; and

a selectable error type that specifies an error type to return when the error is obtained from applying the IFERROR function to the selectable expression.

3. The computer-readable medium of claim 1, wherein the NONERROR function is further configured according to a first syntax to include a selectable range that refers to a range of formulaic cells to which the NONERROR function is applied.

4. The computer-readable medium of claim 1, wherein the TABLELOOKUP function is further configured according to a first syntax to include:

a table array parameter that corresponds to a range of formulaic cells to which the TABLELOOKUP function is applied;

a vertical lookup value that corresponds to a first value to match in a left-most column of the range of formulaic cells;

a horizontal lookup value that corresponds to a second value to match in a top-most row of the range of formulaic cells; and

a nearest/exact enumeration that specifies a type of lookup to perform when the TABLELOOKUP function is applied.

5. The computer-readable medium of claim 1, wherein the VLOOKUPMULTIPLE and HLOOKUPMULTIPLE functions are further configured according to a first syntax to include:

a table array parameter that corresponds to a range of formulaic cells to which the VLOOKUPMULTIPLE and HLOOKUPMULTIPLE functions are applied;

at least one of a result row parameter and a result column parameter that corresponds to a row number and column number of the range of formulaic cells from which a matching value is returned respectively;

a lookup value that corresponds to the matching value when the matching value is located; and

at least one of a result row parameter and a result column parameter that corresponds to a row number and column number of the range of formulaic cells in which the matching value is located respectively.

6. The computer-readable medium of claim 1, wherein the WORD and WORDCOUNT functions are further configured according to a first syntax to include:

a text parameter that corresponds to a text string from which a word is extracted;

a word position parameter that corresponds to an integer specifying the number of the word when the function applied is the WORD function; and

a delimiter parameter that corresponds to specified text strings to be used as delimiters between words in the text string.

7. The computer-readable medium of claim 1, wherein the CONCATENATERANGE function is further configured according to a first syntax to include:

a first text parameter that corresponds to at least one of a text string and a first reference to a range of formulaic cells;

a second text parameter that corresponds to at least one of a text string and a second reference to a range of formulaic cells; and

a separator parameter that corresponds to a string for inclusion between the first and second text parameters returned when the CONCATENATERANGE function is applied.

8. The computer-readable medium of claim 1, wherein the DATEDIFFERENCE function is further configured according to a first syntax to include:

a start date that corresponds to a first calendar date;

an end date that corresponds to a second calendar date; and

a unit parameter that represents units of measure used to determine the difference between the start date and end date.

9. The computer-readable medium of claim 1, wherein the DATEADD function is further configured according to a first syntax to include:

a start date that corresponds to a first calendar date;

a number parameter that corresponds to a number of units to add to the start date; and

a unit parameter that represents units of measure used in adding to the start date.

10. The computer-readable medium of claim 1, wherein the WEEKNUMISO function is further configured according to a first syntax to include a date parameter that corresponds to a serial date from which a week number according to an ISO standard is determined when the WEEKNUMISO function is applied.

11. The computer-readable medium of claim 1, wherein the AVERAGEIF function is further configured according to a first syntax to include:

a criteria parameter that corresponds to a criterion in a form of at least one of a number, expression, and text that defines which formulaic cells are averaged;

a range parameter that corresponds to a range of formulaic cells analyzed according to the criterion; and

an average range parameter that corresponds to another range of formulaic cells that are averaged when the AVERAGEIF function is applied.

12. The computer-readable medium of claim 1, wherein the AVERAGEIFS, SUMIFS, and COUNTIFS functions are further configured according to a first syntax to include:

a first range parameter that corresponds to a range of formulaic cells that are averaged when the-AVERAGEIF function is applied, summed when the SUMIFS function is applied, and counted when the COUNTIFS function is applied;

a first criteria parameter that corresponds to a first criterion in a form of at least one of a number, expression, and text that determines which formulaic cells corresponding to the first range parameter are one of averaged, summed, and counted;

a second criteria parameter that corresponds to a second criterion in a form of at least one of a number, expression, and text that defines which formulaic cells corresponding to the first range parameter are one of averaged, summed, and counted;

a first range parameter that corresponds to a range of formulaic cells analyzed according to the first criterion; and

a second range parameter that corresponds to a range of formulaic cells analyzed according to the second criterion.

13. The computer-readable medium of claim 1, wherein the WEIGHTEDAVERAGE function is further configured according to a first syntax to include:

a range parameter that corresponds to a range of formulaic cells that are averaged when the WEIGHTEDAVERAGE function is applied; and

a weight parameter that corresponds to a range of weights applied to the range of formulaic cells associated with the range parameter.

14. The computer-readable medium of claim 1, wherein the DISTINCT and COUNTDISTINCT functions are further configured according to a first syntax to include a range parameter that corresponds to a range of formulaic cells where distinct values are at least one of counted and returned when at least one of the COUNTDISTINCT and DISTINCT functions are applied.

15. The computer-readable medium of claim 1, wherein the SORTRANGE function is further configured according to a first syntax to include:

a range parameter that corresponds to a range of formulaic cells that are sorted when the SORTRANGE function is applied; and

an index number that corresponds to one of a row and column by which to sort.

16. The computer-readable medium of claim 1, wherein the FILTER function is further configured according to a first syntax to include:

a table array parameter that corresponds to a range of formulaic cells to which the FILTER function is applied;

a criteria parameter that corresponds to the criterion in the form of a number expression or text that defines which of the formulaic cells to filter; and

a criteria column parameter that corresponds to a column in the range of formulaic cells in which to evaluate the criterion.

17. A computer-implemented method for providing a diverse set of functions within a spreadsheet document, comprising:

instantiating a user interface representation of the spreadsheet document on a computing device, wherein the spreadsheet document includes a formulaic cell; and

automatically supporting entry of at least one of a set of diverse functions in the formulaic cell such that a result is obtained in response to processing of the entered function, the set of diverse functions comprising;

an IFERROR function, a NONERROR function, a TABLELOOKUP function, an HLOOKUPMULTIPLE function, a VLOOKUPMULTIPLE function, a WORD function, a WORDCOUNT function, a CONCATENATERANGE function, a DATEDIFFERENCE function, a DATEADD function, a WEEKNUMISO function, an AVERAGEIF function, an AVERAGEIFS function, a SUMIFS function, a COUNTIFS function, a WEIGHTEDAVERAGE function, a COUNTDISTINCT function, a SORTRANGE function, a DISTINCT function, and a FILTER function.

18. The computer-implemented method of claim 17, wherein each of the functions in the diverse set of functions is supported according to a specified syntax such that selected parameters entered by a user into one of the functions produces a predetermined result.

19. A system for providing a diverse set of functions within a spreadsheet document, comprising:

A computing device having memory, the memory having stored thereon a data structure, the data structure comprising;

an array of formulaic cells corresponding to the spreadsheet document, wherein each of the formulaic cells automatically supports entry of at least one of a set of diverse functions such that a result is obtained in response to processing of the entered function, the set of diverse functions comprising;

an IFERROR function, a NONERROR function, a TABLELOOKUP function, an HLOOKUPMULTIPLE function, a VLOOKUPMULTIPLE function, a WORD function, a WORDCOUNT function, a CONCATENATERANGE function, a DATEDIFFERENCE function, a DATEADD function, a WEEKNUMISO function, an AVERAGEIF function, an AVERAGEIFS function, a SUMIFS function, a COUNTIFS function, a WEIGHTEDAVERAGE function, a COUNTDISTINCT function, a SORTRANGE function, a DISTINCT function, and a FILTER function.

20. The system of claim 19, wherein each of the functions in the diverse set of functions is supported according to a specified syntax such that selected parameters entered by a user into one of the functions produces a predetermined result.