Method and system for improved ordering of output from spreadsheet analytical functions
The disclosed technology creates a family of (predefined formula) spreadsheet functions which allows users to create programming loop equivalents in their regular spreadsheet cells employing familiar range functions (e.g., SUM, COUNT, MIN, MAX, etc.) with data filtering and output selection. The data can be sourced from multiple cells within the spreadsheet or a broad spectrum of numeric, date and text data not stored in a spreadsheet, including data not discretely defined. The technology disclosed can use as inputs either cell ranges or Non-Spreadsheet Cell (NSC) data formulas. The capability allows users to specify standardized or highly custom calculations capable of executing millions of loops through a (predefined formula) spreadsheet function.
1. A method of generating a result using a spreadsheet functional formula that has a defined set of named arguments that can be specified in an argument list without a predefined ordering of the named arguments, including:
providing a spreadsheet function delivered prebuilt with the spreadsheet with a defined set of keywords that are required or optional and that serve as named arguments;
wherein the name of at least one named argument determines what the at least one named argument does and how it does it;
where the spreadsheet prebuilt function has a name and a first opening and closing character between which the named arguments and any unnamed arguments are located;
where the keywords are uniquely named and do not require a predetermined location among arguments located between the opening and closing character;
where delimiters separate a named argument from other named arguments or non-named arguments;
receiving and parsing at least two arguments, including at least one named argument, as part of the spreadsheet functional formula; and
generating a result consistent with the spreadsheet functional formula and the arguments located between the opening and closing character.
2. The method of claim 1 , wherein a named argument at an end of an argument list following unnamed arguments is recognized without empty argument group delimiters, when trailing unnamed arguments are omitted.
3. The method of claim 1 ,
wherein at least one named argument has a name and a second opening and closing character within which one or more nested arguments reside.
4. The method of claim 1 , wherein the named arguments and non-named arguments are separated by a single delimiter.
5. The method of claim 1 , wherein some or all of the named arguments are within one or more argument groups with a first type of delimiter separating named or non-named arguments within argument groups and a second type of delimiter separating named or non-named arguments in two different argument groups.
6. A non-transitory computer readable medium holding program instructions that, when executed on a processor a computer-implemented system including at least one processor and memory coupled to the processor, the memory holding program instructions that implement a method of generating a result using a spreadsheet functional formula that has a defined set of named arguments that can be specified in an argument list without a predefined ordering of the named arguments, including:
providing a spreadsheet function delivered prebuilt with the spreadsheet with a defined set of keywords that are required or optional and that serve as named arguments;
wherein the name of at least one named argument determines what the at least one named argument does and how it does it;
where the spreadsheet prebuilt function has a name and a first opening and closing character between which the named arguments and any unnamed arguments are located;
where the keywords are uniquely named and do not require a predetermined location among arguments located between the opening and closing character;
where delimiters separate a named argument from other named arguments or non-named arguments;
receiving and parsing at least two arguments, including at least one named argument, as part of the spreadsheet functional formula; and
generating a result consistent with the spreadsheet functional formula and the arguments located between the opening and closing character.
7. The non-transitory computer readable medium of claim 6 , wherein a named argument at an end of an argument list following unnamed arguments is recognized without empty argument group delimiters, when trailing unnamed arguments are omitted.
8. The non-transitory computer readable medium of claim 6 ,
wherein at least one named argument has a name and a second opening and closing character within which one or more nested arguments reside.
9. The non-transitory computer readable medium of claim 6 , wherein the named arguments and non-named arguments are separated by a single delimiter.
10. The non-transitory computer readable medium of claim 6 , wherein some or all of the named arguments are within one or more argument groups with a first type of delimiter separating named or non-named arguments within argument groups and a second type of delimiter separating named or non-named arguments in two different argument groups.
11. A computer-implemented system including at least one processor and memory coupled to the processor, the memory holding program instructions that implement a method of generating a result using a spreadsheet functional formula that has a defined set of named arguments that can be specified in an argument list without a predefined ordering of the named arguments, including:
providing a spreadsheet function delivered prebuilt with the spreadsheet with a defined set of keywords that are required or optional and that serve as named arguments;
wherein the name of at least one named argument determines what the at least one named argument does and how it does it;
where the spreadsheet prebuilt function has a name and a first opening and closing character between which the named arguments and any unnamed arguments are located;
where the keywords are uniquely named and do not require a predetermined location among arguments located between the opening and closing character;
where delimiters separate a named argument from other named arguments or non-named arguments;
receiving and parsing at least two arguments, including at least one named argument, as part of the spreadsheet functional formula; and
generating a result consistent with the spreadsheet functional formula and the arguments located between the opening and closing character.
12. The computer-implemented system of claim 11 , wherein a named argument at an end of an argument list following unnamed arguments is recognized without empty argument group delimiters, when trailing unnamed arguments are omitted.
13. The computer-implemented system of claim 11 ,
wherein at least one named argument has a name and a second opening and closing character within which one or more nested arguments reside.
14. The computer-implemented system of claim 11 , wherein the named arguments and non-named arguments are separated by a single delimiter.
15. The computer-implemented system of claim 11 , wherein some or all of the named arguments are within one or more argument groups with a first type of delimiter separating named or non-named arguments within argument groups and a second type of delimiter separating named or non-named arguments in two different argument groups.