Polymorphic enhanced modeling

Abstract

A polymorphic modeling method to permutate a model into one of the many alternate model forms. Data can be entered for calculating each model after it is produced. Model permutations employ a permutation process and a permutation control attribute. The permutation control attribute indicates the type of permutation including locked, protected, prompting, interactive, interdependent, single and multiple, polyversion and permutation enabled subroutine. These attributes designate a particular type of permutation behavior assigned to each node in the model. Future node content is included in permutation control by the user or automatically after user designation of initial node content defining an initial model. The permutation process responds to context-sensitive impact events and includes message enabled nodes that can compose, send, read and process messages within a model or between models at remote locations thereby enabling permutation operations that are local, networked, or external such as over the Internet. Permutation loop processing, an enhanced formula syntax, and alternate horizontal and vertical storage formats enhance the permutation process. Constraints may be entered directly into model nodes and are enforced via constrained model permutation processing.

Claims

What is claimed:

1. A machine-implemented permutation method for polymorphic modeling operations utilizing computer elements including display and memory with the capability of permuting a current model, said method comprising:

providing a permutation control for said current model for storage in memory, wherein said permutation control includes providing a data structure for said permutation control with current content, with permutation content of one or more future contents and with a permutation control attribute that signals one of many possible types of permutation operations; and

providing a permutation process for reconfiguring the current model to a new model by utilizing said permutation control.

2. A machine-implemented permutation method for polymorphic modeling operations utilizing computer elements including display and memory, said method capable of being used to build a current spreadsheet model and further having the capability of permuting said current spreadsheet model, said method comprising:

providing a permutation control for said current spreadsheet model for storage in memory, wherein said permutation control includes providing a data structure for said permutation control with current content, with permutation content of one or more future contents and with a permutation control attribute that signals one of many possible types of permutation operations; and

providing a permutation process for reconfiguring the current spreadsheet model to a new spreadsheet model by utilizing said permutation control.

3. The method of claim 2 wherein said permutation process includes the capability of initiating model permutation through initiating events, said initiating events comprising

allowing the entrance of a value into a node that contains a formula to trigger said permutation process; or

allowing the entrance of a value into an independent impact node to trigger said permutation process; and

for all initiating events, utilizing said permutation control to carry out designated changes in said current model to produce a new model thereby enabling the entrance of data into said new model to calculate results.

4. The method of claim 3 providing an enhanced standard mode of operation wherein said permutation process interactively alerts the user to said initiating events before proceeding with the permutation to allow the user options including

halting the permutation process;

allowing the permutation process to proceed; and

allowing the user opportunity to alter the permutation control before the permutation process commences.

5. The method of claim 2 wherein said permutation process includes the capability of

enabling dynamic user selection of one or more receiver nodes wherein the selection of alternative permutation paths are user controlled for interactive permutation path decisions at run-time.

6. The method of claim 2 providing a polyversion mode of operation wherein said permutation process enables the substitution of a new model version for the current model version.

7. The method of claim 2 wherein said permutation process includes

providing for display of said permutation control in a horizontal format for edit-bar interface with the user to enable editing of the permutation control and, alternatively, for display in a vertical format for an edit-list interface with the user to enable editing of the permutation control.

8. The method of claim 2 wherein said permutation process includes

providing for incremental model permutations to build a succession of new models to accommodate all possible combination and permutation of form that said current model can take in order to enable the calculation of results on each new model.

9. The method of claim 2 wherein said permutation process includes

providing for the maintenance of node references in the permutation control of each cell for operations that transfer or move node content including clipboard operations, multiple sheet selections, drag-copy and drag-move operations, insertions or deletions of node(s), row(s), column(s) and sheets and the renaming of model components.

10. The method of claim 2 including a component form with a callable public interface, said method

providing for user callable interface routines that interface with the data structure of the component's nodes to provide polymorphic operation to the component form.

11. The method of claim 2 further providing message enabled nodes capable of communicating with other nodes including nodes at remote sites, composing and sending, receiving and processing messages, said method

providing for the generation of permutations locally or at remote locations in accordance with the message.

12. The method of claim 2 which further includes

providing for an independent impact node with a permutation control attribute in its data structure which signifies an impact operation and with permutation control that is passed to one or more receiver nodes when a value is entered into the independent impact node.

13. The method of claim 2 which further includes

providing for a dependent node with a permutation control attribute in its data structure signifying an impact operation with permutation control that is passed to one or more receiver nodes when a value is entered into the dependent node.

14. The method of claim 2 wherein said permutation process includes the capability of

automatically populating the future content of a dependent node from the current content.

15. The method of claim 2 wherein said permutation process includes

providing for an enhanced formula syntax that enables parsing of said permutation control to obtain current content, future content and the permutation control attribute.

16. The method of claim 2 wherein said data structure includes a constraint control with a current constraint content, one or more future constraint contents and a constraint type identifier.

17. The method of claim 16

providing for user entry of constraints directly into a constraint control.

18. The method of claim 2 wherein a sequence of permutations stimulated by an impact event on an impact node includes the capability of

archiving the permutation control of said impact node;

updating the current content of said impact node in accordance with said impact event;

evaluating the archived permutation control attribute of said impact node to determine the type of permutation operation;

identifying one or more receiver nodes in accordance with the archived permutation control of said impact node;

for each identified receiver node, reorganizing the archived permutation control of said impact node to swap current and future contents; and

generating a message for each identified receiver node including the reorganized permutation control of said impact node and sending each said message to the corresponding receiver node.

19. The method of claim 18 further including the capability of

receiving said message at said corresponding receiver node;

archiving the permutation control of said corresponding node;

replacing the permutation control of said corresponding receiver node with the reorganized permutation control of said impact node contained in said message;

evaluating the permutation control attribute of the archived permutation control of said corresponding receiver node to determine the next type of permutation operation, if any;

if a further permutation is indicated, identifying one or more subsequent receiver nodes,

for each identified subsequent receiver node, reorganizing the archived permutation control of said corresponding receiver node to swap current and future contents;

sending a subsequent message including the reorganized permutation control of said corresponding receiver node to one of the identified subsequent receiver nodes and continuing the permutation operation; and

if a further permutation is not indicated, acting in accordance with said permutation control of said corresponding receiver node to either (1) signal an end to the permutation process; (2) load an indicated subroutine for execution; or (3) load an indicated new model version.

20. The method of claim 19 including the handling of a permutation loop, i.e., when the original impact node is passed the original permutation control, including

providing for recognition of the permutation loop event; and

providing for the reentry of the impact event in said impact node to trigger a second permutation process.

21. The method of claim 2 wherein the type of permutation operation indicated by said permutation control attribute includes single impact, multiple impact, and interactive.

22. The method of claim 21 wherein said permutation control attribute indicates a subroutine operation and wherein said subroutine operation may contain a permutation enabled macro in its associated data structure.

23. The method of claim 21 wherein said interactive operation further provides for

preventing a permutation;

prompting before permutation is allowed;

prompting to obtain the information needed to complete a permutation;

prompting the user to load a new model version, if desired; and

enabling dynamic user selection of one or more receiver nodes wherein the selection of alternative permutation paths are user controlled for interactive permutation path decisions at run-time.

24. The method of claim 21 further providing a multiple allocation operation as a type of multiple impact operation providing permutation to a group of nodes thereby enabling allocation of an amount among precedent nodes, including an amount which may be equal to all such nodes, a different percentage of the amount to such nodes, different amounts based upon weighting factors, or user-defined allocation amounts.

25. The method of claim 21 further including an interdependent impact node providing for grouping a plurality of nodes for maintenance of group synchronization when a change occurs in the content of any node in the group.

26. A machine-implemented permutation method for polymorphic modeling operations utilizing computer elements including display and memory, said method capable of being used to build a current spreadsheet model and further having the capability of permuting said current spreadsheet model, said method comprising:

providing a permutation control for said current spreadsheet model for storage in memory, wherein said permutation control includes providing a data structure for said permutation control with current content, with permutation content of one or more future contents and with a permutation control attribute that signals one of many possible types of permutation operations; and

providing a permutation process for reconfiguring the current spreadsheet model to a new spreadsheet model by utilizing said permutation control, wherein said permutation process includes providing for incremental model permutations to build a succession of new models to accomodate all possible combinations and permutations of form that said current model can take in order to enable the calculation of results on each new model, and wherein said permutation process further includes providing for the undoing of permutations made to the model by providing for undo options including reset after change, reset to original model, and incremental undo of changes to retrace steps.

Description

TECHNICAL FIELD

The present invention relates generally to the field of information processing by digital computers and particularly to the processing and presentation of information by computer program applications such as electronic spreadsheets.

BACKGROUND OF THE INVENTION

Spreadsheets enable the organization of data into columns and rows, permitting rapid accumulation of information by a user. Before spreadsheet processors were developed for computers, spreadsheet information, especially financial, was prepared on accountants' columnar pads or paper spreadsheets, using pencils and calculators. The task of preparing a spreadsheet on paper is much slower than in a computer because each entry must be calculated and hand entered in the spreadsheet. Manually prepared spreadsheets are prone to errors.

A spreadsheet processor (hereafter, spreadsheet) is an interactive computer processor that consists of a collection of rows and columns displayed on a computer monitor screen in a scrollable window. The intersection of each row and column is called a cell or a node. A cell can hold a number, a text string, or a formula that does calculations using the entries in one or more other cells. A spreadsheet can copy cells, move cells, and modify formulas. Spreadsheets may save a cell in a file for later use and may discard a cell after it has served its purpose. Spreadsheets can format cells in a spreadsheet presentation in a variety of ways and the presentation may be printed for hard-copy reference. In addition, groups of cells can be used to generate charts and maps.

Perhaps the most significant advantage of an electronic spreadsheet is the easy, immediate recalculation of a formula's results when a change is made in any of the entries in cells. Once a spreadsheet is set up by defining formulas, it can be used as a model to try different possibilities by varying entries to show the effect of changes.

A typical spreadsheet processor configures the memory of a computer to resemble the grid format of an accountant's columnar pad, providing a visible presentation for a user. Because the pad exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be expressed to the computer in a format which it can use. A common scheme for accomplishing that is to assign a number to each row and a letter to each column. For example, the reference A1 designates the address of a location at column A and row 1, i.e., the cell in the upper-left-hand corner. In that manner, the spreadsheet reference defines an addressable storage location or cell at each intersection of a row with a column.

Data entry into an electronic spreadsheet is done in much the same manner that information is entered on an accountant's pad. A screen cursor is positioned at a desired location and a user enters alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions such as formulas specifying calculations to be done on values stored in other spreadsheet cells. Cell addresses can be used as variables in an equation, allowing mathematical relationships to be defined among cells. The structure and operation of a spreadsheet processor, including functions and macros, are well known in the art.

Electronic spreadsheets offer many advantages. They can be much larger to hold more information than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet processors also allow users to do what-if scenarios. After they have stored a set of computational relationships into a worksheet, the spreadsheet information can be recalculated using different sets of assumptions with the results of each recalculation appearing almost instantaneously. Doing this operation manually with paper and pencil might require recalculating every relationship in the model with each change made.

Spreadsheets can be multidimensional rather than being restricted to two-dimensional models. In a three-dimensional spreadsheet, they uniquely identify each cell by a row, a column, and a page. This is analogous to a notebook or workbook. A notebook can hold any number of spreadsheets or worksheet pages. A notebook, therefore, depicts a three-dimensional representation of information cells. Using this metaphor, the user can leaf through many pages of information or to go to a specific page of information within the notebook.

The present state-of-the-art electronic spreadsheets have limitations and do not support additional forms of analysis that many users would find very valuable. For example, in current art spreadsheets, a value entered into a formula cell will override the formula, place the value in the cell, erase the formula, and invalidate the existing model logic. This result is counter productive. The spreadsheet should respond in a more intelligent, context-sensitive, fashion. First, it should not simply erase the formula and invalidate the model without further interaction with the user. Second, it should seek to correctly satisfy the user. The correct response when the user enters a value on a formula cell is to change the model's form, i.e., from one that cannot be calculated into one that can be calculated. Since many cells can contain formulas, the correct response will also include the ability for the model to take on any one of many possible forms. It should accept an entry into any formula cell present in the model, without breaking it.

It is an object of the present invention to both prevent a corrupt model and to provide a correct response when the user enters a value on a formula cell. The spreadsheet should generate one of a potentially unlimited number of new model forms that can be correctly calculated. The result is an event driven, polymorphic spreadsheet, that can reconfigure itself into one of several model formats dynamically during runtime operation.

Current art spreadsheets bind data and formulas together. When a model is loaded, it replaces both the current data and the current formulas in computer memory. This limits the user to a single spreadsheet model that operates on a single set of spreadsheet data. Commercially available spreadsheets at the current time also provide only a single standard calculation method that operates on a single model and a single set of data. Thus, the model's logic and data are bound together. It is, however, an advantage to be able to apply more than one model version to a single set of spreadsheet data. There are many desirable, but currently unsolved forms of analysis, that require the ability to apply multiple model versions to a single set of data.

Therefore, it is an object of the invention to uncouple spreadsheet data from its formulas, so that the data and the model formulas are independent. In so doing, an unlimited number of model versions can be applied to a single set of spreadsheet data.

U.S. Pat. No. 5,339,410 shows the use of more than a one-way calculation method. However, its bidirectional method is limited in scope to a forward and a backward direction. In a complex spreadsheet model with a large number of interrelated formula cells, a plurality of directions, i.e., combinations and permutations, exist. At every formula node directional decisions can be made that are equal in number to the number of precedent cells contained in the cell's formula. So, while many directions can exist for each cell, an almost unlimited number of directions can result from the combinations and permutations produced by many formula cells. Unfortunately, the bidirectional method is limited to a single directional decision for each cell. Also, since a bidirectional computation method is fixed in computer logic it is limited to a single alternate computational path. For each cell affected by a first change event, it derives a single inverse formula, using algebra, that it uses to calculate a single result cell. As a result, for any particular one of the model's formula cells, it can calculate only one alternate computational path. However, many alternate computational paths exist for each formula cell, i.e., one for each precedent cell found in the cell's formula. Therefore, the need exists to be able to select any one of the formula's precedent cells as the target cell and develop an inverse formula for that cell.

There is also a need for the user to control the computations. Every formula cell is a node in the spreadsheet that can make a directional decision. However, no method is currently provided for the user to control and to select computational permutations with multiple directions, both statically at design-time and dynamically at runtime. Both design-time and runtime interactive directional control require an intelligent, interactive cell type. The cells need the capability to interact with the user giving the user a list of options. The user must be enable to select from those options, and the cells must be able to correctly perform the selected option.

In view of the foregoing, it is an object of the present invention to provide an interactive cell type where directional decisions can be made for every node. Such decisions can be made in formula nodes and also in independent trigger nodes. Such decisions can be made both statically at design-time and dynamically at runtime. The invention provides directional decision making for a limitless number of computational directions.

The need exists for handling formulas that cannot be inverted such as non-algebraic formulas that contain functions or constructs using the logical values of cells. Also, even when a formula is an algebraic equation, there remain many complex forms that cannot be inverted.

It is, therefore, an object of the present invention to eliminate fatal errors resulting from total reliance upon the derivation of an inverse formula; to provide a means for user input of impact formulas, values or other acceptable cell contents; to provide a means for accessing and using user provided content in the model permutation process, and simultaneously providing enhancements to the inverse formula process so that it can be of use where it is appropriate and only where it is successful.

It is a further object of the present invention to eliminate the need for right-side to left-side value comparisons of bi-directional equations to eliminate fatal errors resulting from floating point inequalities. This is accomplished with the polymorphic focus of the present invention. When a model is permutated from one form that cannot be calculated into another form that can be calculated, the result is a standard spreadsheet calculation of a valid spreadsheet model. As a consequence, there is no right-side nor left-side to evaluate what-so-ever in the permutated model.

An independent cell is a cell that is not found in the formula of a formula cell. The independent cell, however, is one that in certain cases should be changed when the formula cell changes value. In the current art there are no means to detect the existence of an independent cell from the formula cell. It is an object of the present invention to provide an impact cell that can effect a change to an independent cell when needed.

There is a need for a multiple impact cell, one where a change to a formula cell will result in a change to more than one direct or indirect precedent cell and/or independent cells. It is an object of the present invention to provide for multiple impact cells that can impact a plurality of precedent and independent cells with various forms of contents during model permutation events.

A special case of the multiple impact cell is the multiple allocation cell which enables an allocation of an amount entered into a multiple impact cell among the precedent cells. Such allocation may be equal to all cells, based upon a percentage calculated from the previous value as a percent of the total value, or the amount based upon weighting factors, such as seasonal sales factors found in a separate range of cells within the spreadsheet. There is no provision for multiple allocation cells in current art spreadsheets.

It is an object of the present invention to provide multiple allocation impact cells, including but not limited to split even, split percent, split using weights stored in a named range of cells, or split to a partial list of precedent cells.

There are special cases involving multiple impact and multiple allocation cells where precedent cells require group processing. Because each of the children cells receive part of their parent cell's value, they require interdependent processing to maintain model synchronization so that if any one of the group of sibling cells subsequently change content, the parent and all of the sibling cells receive notification and change instructions. Interdependent sibling cells are not provided in current art spreadsheets. It is an object of the present invention to provide for interdependent impact cells for the grouping and callback processing required for proper model synchronization as the result of multiple impact parent cells.

There is also a need for the user to be able to provide independent processing logic to create permutations in the model which may take the form of an algorithm beyond the scope of standard processing provided by the spreadsheet or its formulas. Therefore, the need exists for an algorithm control mechanism to provide for alternative forms of processing as defined by the user. In that manner, user defined subroutines can satisfy the unlimited range of computational results that a user may want.

It is an object of the present invention to provide for user defined subroutines in the form of independent processing logic that can be stored and referenced by any impact cell type to provide unlimited computational alternatives that may go beyond the scope of standard spreadsheet operations.

The bidirectional method mentioned above is limited to triggering its single alternate computational method by formula type cells. However, it is also advantageous to have the capability of storing a permutation trigger within an input type cell. In the present invention, provision is made for a permutation trigger. That trigger consists of cell contents that are passed to an impact cell when data is entered into the input cell. This action results in an additional process beyond a simple edit to the value of an input cell. It results in an additional model permutation, i.e., an action, triggered in an independent cell, that can impact one or more additional cells. There is no provision for an independent cell containing a permutation trigger in current art spreadsheets.

It is a further object of the present invention to provide independent impact cells with permutation triggers (independent trigger cells) that can impact or effect changes in one or more other model cells while simultaneously functioning as an input cell.

In the current art, a model is loaded into the spreadsheet, calculations are performed, and the model always returns to the same original form. However, it is advantageous in many circumstances to not return to the original model form. In fact, it is more advantageous to be able to assume many model forms, i.e., not just the starting model form, but a series of many model forms, as many possible forms as may be derived from the combinations and permutations present in the starting model equations. Thus, an accumulation of changes can occur so that the resulting model may be quite different from the model loaded by the user, while requiring little or no additional input from the user. There is, however, no provision in current art spreadsheets for model permutations of this kind.

It is an object of the present invention to provide incremental or cumulative model permutations to accommodate every possible combination and permutation of form that a model can take. And further, to provide for model versions that are independent of all current cell content so that unlimited possibilities can exist for model permutation and the application of a plurality of models to a single set of spreadsheet data.

It is a further object of the present invention to support undo options such as, reset after change, no reset after change, i.e., so that incremental changes can accumulate, reset to the original model, or incremental undo of changes to retrace steps. While the undo of edits made by the user in the current art will undo only the individual edits made, undo in a permutation enhanced spreadsheet will also undo the permutations made to the model as directed by the user.

Current art spreadsheets enable users to copy cells, move cells, and modify formulas. The relative cell references that are found within the cell content is maintained during those operations. However, because the current art spreadsheets are not permutation-enabled there are many operations that will fail to produce the correct cell content when a model permutation occurs. For example, a cells' permutation content is not maintained for clipboard operations, multiple sheet selections, drag-copy or drag-move operations, insertions or deletions of cell(s), row(s), column(s), and sheet(s), and renaming model components. The polymorphic spreadsheet cell is permutation-enabled, and therefore, the inability of current art spreadsheets to maintain its permutation content during content transfer or move operations is overcome.

Therefore, it is an object of the present invention to "permutation-enable" every operation that transfers or moves cell content, i.e., not only for the original content, but also the polymorphic content used in model permutation operations.

Current art spreadsheets provide built-in functions such as sum and average, as well as more complex functions that perform sophisticated operations such as the correlation coefficient for two sets of data. However, these built-in functions are not permutation-enabled. Such functions may not properly operate when used in a permutation-enabled cell. For example, the simple sum command may become an impact targeting mechanism for a plurality of cells in a model permutation. Therefore, it is an object of the present invention to provide permutation-enabled functions so that such functions can change from one form into another form, for interacting with the permutation behaviors of the invention to yield valid alternative model forms.

Current art spreadsheets are available in multiple delivery formats, i.e., in a standalone form and in a component form. The component form encapsulates the behaviors of the spreadsheet and provides a callable public interface to the operations contained in the component. However, because current art spreadsheet components are not polymorphic, their callable interface does not include operations that can correctly interface with the permutation enabled methods of a polymorphic spreadsheet. Therefore, it is an object of the present invention to provide a component format that is permutation enabled with user callable interface routines. These interface routines provide polymorphic operations that interface with the component's data structure and are beyond the capabilities of the current art.

Formulas are the quintessential spreadsheet capability that perform the analysis that makes them so useful. Formulas are entered into the spreadsheet cell using a format that can be parsed by the spreadsheet and translated into a form that can be calculated in computer memory. The format of the spreadsheet formula however, is inadequate to provide essential permutation content to the cell. Therefore, it is an object of the invention to provide an enhanced formula syntax, i.e., a syntax that can be parsed by the polymorphic spreadsheet into current and future cell content or other permutation control behaviors as are necessary to achieve both current cell calculation and future cell permutation.

Circular references are managed in current art spreadsheets via an iteration control with settings for the maximum number of iterations or a maximum change value. However, the circular reference of a cell or its precedents or indirect precedents referring to itself in a formula takes on new meaning when that formula is permutated or inverted. There are two distinctions that can be made. First, the formula in a permutated form may contain a circular reference. The iteration control of the current art is adequate to handle the computational iterations of that reference. Second, the sequence of cell permutations that can occur when the inverse content is moved between cells, can lead to a permutation loop. In a permutation loop, the content that is moved out of the cell gets moved back into the cell. A permutation loop is a different event from a circular reference because it involves content, not computation. Current art spreadsheets do not provide a means for handling the permutation loop. An object of the current invention is to handle permutation loops.

Optimization and constraint processing in spreadsheets is most often achieved using a solver-add-in capability. The solver-add-in is a separate computational engine that is fixed in computer programming logic and takes a limited set of input from the user. With the single exception of a data entry validation rule, the user is not able to enter constraints directly into the spreadsheet cells. The user is given limited control over the methods of the computational processing. Because the solver technology was built using the single model paradigm it is not adequate for a polymorphic paradigm. The polymorphic paradigm enables a new approach to the user interface with constraint and optimization processing. First, the user should be able to enter constraints directly into the cells in a context sensitive fashion. Second, the user should be given access to and control over the model permutation behaviors that accomplish the constraint processing. The result of an optimization or constraint process is simply the reallocation of values among cells. The restricted access to the methods used for this allocation by the solver-add-in is a significant limitation that is removed in a polymorphic paradigm. It is an object of this invention to provide a polymorphic spreadsheet enabling the user to enter constraints directly into the cells and to control constraint processing.

Current art spreadsheets and spreadsheet components do not support a polymorphic paradigm. Therefore, the component interface of current art does not have polymorphic interaction with a polymorphic data structure. The eventuality of permutation content as a part of a cell formula syntax is also a short coming. The functions used in equations are unaware of their meaning in the context of permutations and polymorphic behaviors. The eventuality of permutation loops found in the polymorphic paradigm are not addressed. Current art spreadsheets do not support in-place editing of constraints, nor user access to control the form or sequence of processing for the allocations that occur during constraint processing.

Current art techniques do not anticipate, nor do they provide for interactive impact cells, cell selection for multiple single impact cell computational pathways, independent impact cells, multiple impact cells, multiple allocation cells, interdependent impact cells, user-provided algorithms that can be triggered by context-sensitive cell-based events, independent trigger cells, nor cumulative model permutations.

Current art techniques do not address the issue of applying an unlimited number of independent model versions to a single set of spreadsheet data, nor do they permit the user to have direct access and control over model permutation. In fact, they offer no permutation at all. They suffer from fatal errors that invalidate the model when a value is entered on a formula cell and lack the intelligence to respond in an intelligent, context-sensitive manner with a permutated model format.

The above shortcomings are avoided in the present invention by providing more than one mode of operation in combination with the ability to operate on a single spreadsheet's data with an unlimited number of alternative models.

The user is given direct access to the cell's enhanced data structure where each display cell can store more than one processing cell in computer memory as well as mode control, model permutation control and constraint algorithms. Each cell is enhanced so that it can simultaneously support a plurality of cell behaviors or functions to provide advanced capabilities for model permutation.

The user is provided with a hierarchy of impact cell types that support a plurality of model permutation behaviors that result in a substantially unlimited range of model permutation and computational results.

The result is a one-to-many spreadsheet that is polymorphic, i.e., can change from one form into another one of many alternative forms. Thus, a broader spectrum of modeling solutions is available while maintaining the speed and ease of the familiar spreadsheet human interface.

SUMMARY OF THE INVENTION

The present invention is a multidimensional polymorphic spreadsheet that can apply an unlimited number of models to a single set of spreadsheet data. The invention uses a permutation control attribute to store a plurality of cell content in the cell's data structure in computer memory. So, the spreadsheet cell of the present invention is polymorphic.

Many modes of operation are supported in the invention. Some modes of operation are stored at the component level with a corresponding attribute at the cell level. The component level and cell level operating modes cooperate to provide the polymorphic, enhanced standard, polyversion, permutation locked, permutation reset, and undo modes of operation.

The reset and undo operations reset permutations incrementally. The last permutation can be reset to the previous equations, a particular version can be reset as selected by the user, or the entire model can be reset to the original computational format entered by the user. The cell data structure additionally includes the original state control that stores the original cell content to support these reset and undo operations.

Using the various modes, behaviors and controls of the invention, the user can define the scope of permutation allowed within a given model. Polymorphism may be totally enabled, totally restricted, or of any in between scope.

The permutation control also uses "enhanced formula syntax." The enhanced formula syntax is used to parse the content of the permutation control into its component parts. The component parts are the current cell content, pluralities of future cell content and the permutation type identifier. The current content can hold an equation to calculate the cell's value, text, number or the like. Permutation control uses the current cell content, future cell contents and the permutation type identifier in combination to generate model permutations. It uses the type identifier to interpret the content of the permutation control and apply it correctly to the cell's permutation operations.

In addition to direct editing of cell content by users, the methods of the present invention also provide for the algebraic derivation of inverse formula content that represent permutations and combinations of the formulas entered by the users. It stores these permutations as future cell content in the permutation control. It also provides means for reordering the permutation control contents and moving cell contents during permutation operations.

The cell also stores a constraint control in the cell data structure. The constraint control has a format similar in structure to the permutation control. The invention uses the constraint control to enable constraint processing via iterative model permutations, rather than relying only on fixed computational algorithms. Therefore, the polymorphic cell can store pluralities of cell contents that are used in model permutation operations and each individual cell in the invention is simultaneously calculation enabled, permutations enabled, and constraint enabled.

The invention identifies many context-sensitive permutation behaviors via a permutation type identifier that it stores within the cell's permutation control. It broadly defines these permutation behaviors as locked, protected impact, prompt impact, single impact and multiple impact.

Various subtypes of behaviors can occur. Single impact subtypes are dependent and independent. Multiple impact subtypes are polyversion subroutine impact, multiple allocation and user defined. For example multiple allocation subtypes are allocate equally, as a percent, using a weight cell, using a weight cell percent, partial allocation and constraint. The polyversion subtype provides a means to load a full or partial model version into active memory, thus, numerous models can apply to a single set of data.

Intelligent interoperable cells are capable of sending, receiving, composing and processing email messages both within one workbook and externally between multiple workbooks. Inter-workbook communications are possible even when those workbooks are located at different remote sites. Inter-cell and inter-workbook model permutations are accomplished in the invention via email-enabled cells that can send change instructions locally or via the operating system to external workbooks, thus generating additional permutations in external, precedent workbooks, as well as within the local workbook.

The invention also provides permutation-aware functions enhanced to perform permutation-enabled operations. For example, the @sum function, that. can result in single impact cell targeting or multiple impact allocation permutation behaviors.

Both design time and runtime interactive operations provide user selections among pluralities of computational pathways that exist within the permutations of the model stored in the cell's permutation control.

The invention supports impact event conflict resolution. An impact event occurs when the current contents of a cell conflict with the intended content for the cell. The incoming content can come from many sources. For example, the user, the system, or cell behaviors can generate impact events. To resolve the conflict caused by the impact event the model must change forms, i.e., be permutated from a form that cannot be calculated into a form that can be calculated.

The invention supports a permutation loop behavior. The loop occurs when the origination cell receives a return email message. When this occurs it initiates a second model permutation to finalize the computational result of the model.

The invention also provides alternate formats for data storage in computer memory for the permutation, constraint and original state controls. Each format has a particular advantage, i.e., a horizontal format reduces memory use while a vertical format reduces processing time. The former is best for the edit-bar interface and the latter is best for the edit-list interface. The invention uses these alternate formats interchangeably and can convert from one to the other instantaneously. A variant memory structure uses a case identifier to select the current format. Therefore, it can store either type but it stores only the selected type in the current memory location for the cell. Consequently, the user controls the tradeoff between processing speed and efficient use of memory.

The component architecture of the invention provides for delivery in two alternate forms, i.e., a standalone polymorphic spreadsheet and a polymorphic spreadsheet component. The component interface allows the user or developer to use the operations of the component via a published interface that is `callable` by program or subroutine operations written by the user or developer. Therefore, when doing input-output operations to the polymorphic cells, the correct actions are done to insure adherence to the standards for polymorphic behaviors defined in the methods of the invention.

Polymorphic modeling in the present invention also extends into every domain of spreadsheet operation which includes optimization using constraints. The cell validation rule and the solver add-in constraint processing of the current art are replaced in the invention with a constraint control attribute located in the cell data structure. The activation of the constraint controls is optional and user selections can start their operation for individual cells, groups of cells, or for the entire model. The constraint control operates with the other permutation behaviors of the invention.

Additional capabilities of the present invention recognize that each spreadsheet operation that has the effect of transferring or moving information between or among cells must be permutation-enabled. When cells are cut-and-pasted or dragged-and-dropped, their relative cell references must be maintained, not only in the current content portion of the cell's permutation control, but also in the future content of the cell, as well as in the entire content of the permutation control, the constraint control, and the original state control. Therefore, the methods of the present invention "permutation-enable" every standard operation of the spreadsheet to realize polymorphic operation throughout the preferred embodiment of the invention.

Clipboard operations for cut-paste and copy-paste will result in model permutations that maintain a valid model structure. Multiple worksheet selection operations, where the contents of visible cells are transferred to invisible cells on many sheets, are permutation-enabled. Renaming sheets, or other model components such as the workbook title, that appear in formulas are updated throughout the cell's permutation and constraint storage areas. Insertions or deletions of cells, row(s), columns, and sheet(s), drag-copy and drag-move operations, and the like, are permutation-enabled in support of polymorphic modeling.

It should be emphasized that the permutation enabled, polymorphic-spreadsheet of the present invention, does not use an alternative computational method. It relies solely upon the standard recalculation method. The model is first permutated and then it is calculated.

In summary, the invention delivers polymorphic, permutation enhanced modeling using a familiar spreadsheet interface via numerous operating modes; numerous permutation behaviors; multiple-cell storage in the cell's permutation control; constraint control and original state control; enhanced formula syntax for parsing these controls; methods for changing the order of the content of these controls; moving content among these controls; inverting equations to generate automated permutation content to store in these controls; permutation enabled functions; as well as direct access to the cell data structure using alternative storage formats, optimized for processing speed versus memory utilization; a public callable component interface for user defined interaction with the polymorphic component format of the preferred embodiment; as well as enabling multiple delivery formats, i.e., standalone modeling for individual users or component form for developers to use; add to these, email-enabled cells, the abilities to maintain, store, and retrieve unlimited model versions to be applied to the same set of spreadsheet data; design time and runtime enabled interactions with the user for unlimited computational pathways combined with unlimited model permutations; and user controlled constraint processing.

This plurality of operating modes, data structures, permutation-enabled controls, cells and behaviors yield a polymorphic modeling environment with broad application that is vast in its analytical scope. A familiar spreadsheet interface is maintained for ease of operation, while simultaneously minimizing the complexities of such operations via simple user selections for individual cells, sheets or at the component level. The ease, sophistication, power, and smooth operation of the present invention provide surprising and beneficial results that are achieved only via the polymorphic operations of the present invention. These objectives and others are accomplished in the present invention as disclosed herein below.

BRIEF DESCRIPTION OF THE DRAWING

The invention is described in detail by referring to the various figures which illustrate specific embodiments of the invention, and wherein like numerals refer to like elements.

FIG. 1 is a block diagram of a computer system in which the invention can be implemented and carried out.

FIG. 2 is a block diagram of the relations of polymodal operations.

FIGS. 3A-3C are a block diagram of the cell data structure, the link to the previous cell instance on the undo list, and the communications link between multiple workbooks using the cell data structure and undo list in combination.

FIG. 4 is a block diagram of impact cell types and subtypes.

FIGS. 5A-5C are a block diagram of the relationships between impact cell types and subtypes.

FIG. 6 is a block diagram of a hierarchical relationship among the polymorphic modeling permutation behaviors of the invention.

FIGS. 7A-7C are a block diagram of hierarchical relationships among the event processes of the invention.

FIG. 8 is a flowchart of a cell event process.

FIGS. 9A and 9B are a flowchart of an event routing process.

FIG. 10 is a flowchart of the undo and reset to previous process.

FIGS. 11A and 11B are a flowchart of a new formula process.

FIG. 12 is a flowchart of a constraint control update process.

FIG. 13 is a flowchart of a independent permutation control update process.

FIGS. 14A and 14B are a flowchart of a permute model process.

FIGS. 15A-15C are a flowchart of a read email process.

FIG. 16 is a flowchart of a send email process.

FIGS. 17A-17D are a flowchart of a compose email process.

FIG. 18 is a flowchart of a compose multiple impact process.

FIGS. 19A-19E are a flowchart of a send multiple email routine.

FIG. 20 is a flowchart of a multiple callback routine.

FIGS. 21A-21E are a flowchart of a user defined multiple impact routine.

FIGS. 22A and 22B are a flowchart of a constraint process.

FIG. 23 is a block diagram of interactive single impact permutation behaviors.

FIGS. 24A and 24B are a flowchart of a model event process.

FIGS. 25A and 25B are a flowchart of a permutation control process.

FIG. 26 is a flow diagram of a cut or copy to clipboard processor.

FIG. 27 is a flow diagram of a paste cells from clipboard processor.

FIGS. 28A-28G is a flow diagram of a delete processor.

FIG. 29 is a flow diagram of an insert processor.

FIG. 30 is a flow diagram of an insert right or column processor.

FIG. 31 is a flow diagram of an insert down or row processor.

FIGS. 32A-32B is a flow diagram of a relative cell insert.

FIG. 33 is a flow diagram of a relative cell sheet insert processor.

FIG. 34 is a flow diagram of an exchange cell processor.

FIG. 35 is a flow diagram of a replace string processor.

FIG. 36 is a flow diagram of an insert sheet processor.

FIG. 37 is a flow diagram of a delete sheet processor.

FIG. 38 is a flow diagram of an rename sheet processor.

FIG. 39 is a flow diagram of a single impact target cell selection using a scrolling display.

FIG. 40 is a flow diagram of an interactive targeting procedure for the selection of a target cell at runtime.

FIG. 41 is a flow diagram of a copy cell to many sheets operation.

FIGS. 42A-42E are a flow diagram of a drag-drop operation for moving or copying cell contents.

FIG. 43 is a flow diagram of a convert processor for converting a conventional spreadsheet file into a polymorphic spreadsheet.

FIG. 44 is a screen bitmap of an example polymorphic financial model.

DETAILED DESCRIPTION

The present invention provides a polymorphic modeling environment. It takes two simultaneously deliverable forms, a standalone polymorphic spreadsheet and a polymorphic spreadsheet component. In both formats, users can apply limitless numbers of spreadsheet models to a single set of spreadsheet data. It also provides interactive user selections of multiple computational paths dynamically at runtime. So, users can realize limitless numbers of computational directions.

The present invention is a polymodal spreadsheet giving users the ability to select from a plurality of operating modes. Each mode addresses different needs at different times.

The polymorphic spreadsheet of the present invention is permutation-enabled or permutable. Users can permute the model from one form into another form, where limitless numbers of possible model formats exist.

Providing a permutation enabled spreadsheet cell achieves polymorphism in the invention. A permutation-enabled cell replaces the cell formula attribute found in current art spreadsheets with a permutation control. The permutation control not only provides for cell recalculation it also provides a means for model permutations. The permutation control can contain a plurality of permutation behaviors. Permutation behaviors are operations or actions taken or executed to change the model regarding order of the model's contents.

Typical spreadsheet cell behaviors are calculating a formula, entering text or a number into a cell, and logging onto a web site. Polymorphic cell behaviors include entering a permutation control into a cell, performing model permutations, retrieving alternate model versions without replacing data values, and starting a named subroutine or macro, and the like, from within the cell itself. Each item in the permutation control can, therefore, contain any valid type of cell content that it can use to permute the model, including the polymorphic behaviors provided by the invention.

The invention also enhances syntax to use "enhanced formula syntax" to extend functions beyond that of the current art formula parsing. The enhanced formula syntax provides a means of parsing the contents of the formula edit-bar or cell edit field. The enhanced parsing allows entry of a plurality of cell content. It separates each element in the edited string into individual parts and places the parsed information into the cell's permutation control. It also establishes the order of precedence for these contents. The first position contains the current cell contents. The next positions contain a plurality of future cell contents in order of precedence. The last position contains the permutation control identifier. The identifier defines the selected one of many possible permutation behaviors. The selected permutation behavior executes when a permutation event triggers it and it uses the contents stored in the permutation control to accomplish inter-cell permutations.

The enhanced formula syntax supports two interchangeable storage formats, one horizontal and the other vertical; each storage format suits a particular set of tasks and each produces a different set of performance characteristics.

The horizontal format is appropriate for formula bar editing. It also optimizes memory use. The vertical format is appropriate for line by line editing in a list box or edit control. It also optimizes processing speed. The storage format for the permutation control is selectable on a cell by cell basis. Cells can inherit the storage format from another cell or they can assign them globally for the entire model. These options give the user direct control over the performance tradeoff of speed versus space and enhance the user interface to provide smooth transitions from edit-bar to edit list interactions with the user.

The cells of the invention are both permutations enabled and email enabled. Each cell can compose, send, receive and operate upon their own contents and the contents of other cells via inter-cell email messages.

Email messages can go beyond the active workbook, and can also go to external workbooks. Workbook cells with links to cells in another workbook send email messages to the external workbook to affect changes. The external workbook sends the message to the cell that will process it. Thus, the local cell permutes the external workbook into a new form via inter-cell email messages. The result is support for multiple external polymorphic spreadsheets via inter-cell communication, and thus execution of an external model permutation is possible. The cells can also send the permutation email messages via batch processing to a remote site using a network such as the world wide web.

The invention further achieves polymorphic modeling via an array of permutation behaviors. These behaviors include preventing a permutation, prompting before it allows permutation, and prompting to complete the information needed to complete the permutation. Interactive behaviors let users select the target cell for a particular cell's permutation. Single impact behaviors affect a single cell, multiple impact behaviors affect multiple cells, and allocation behaviors split values between many cells. Interdependent behaviors treat cells as a group for interdependent permutations. User-defined subroutine behaviors execute a macro and polyversion behaviors retrieve partial or complete sets of cell contents from a storage location.

Context sensitive events, or "impact" events trigger the permutation behaviors within the cell. An impact event is a collision between an entry made into a cell with the current contents of the cell. For example, entering a value into a dependent cell creates a collision. Use the formula to calculate a value or use the value to replace the formula. Which takes precedence? The two are in conflict. Various types of impacts can occur. Each impact-type represents a class or type designation for each distinct kind of permutation behavior. The permutation behaviors, therefore, resolve the conflict created by an impact or collision between an entry made into a cell with the current contents of the cell. As a result, they change the model from one form, that they cannot calculate, into another form that they can calculate.

A cell in the enhanced standard mode of operation protects the cell from permutation until it receives additional information. A current art spreadsheet cell does not protect the contents of a dependent cell unless the user "protects" it. However, the user cannot edit a protected cell, so they do not protect the cells at design time. Therefore, users can type over and lose the contents of a dependent cell which invalidates the model contents. The permutation-enabled cell of the present invention prevents that error. It uses the enhanced standard mode of operation to prompt before it takes any action. It offers the user three options: to protect the cell's contents, update and permute the model, or select a model version, i.e., a set of contents (sans data), to retrieve into active memory. These options prevent the user from inadvertently breaking the model with a typeover of valid cell contents and offer permutation-enhanced alternative behaviors as well. These behaviors apply universally at design time and at runtime.

When an impact event triggers a cell permutation, it archives the current cell contents, including the permutation control, to a previous instance of the cell. This instance of the cell becomes the controlling agent of the model permutation process. It then updates the current cell with the new contents. Then, it will either leave the permutation control unchanged, clear the control, or update the control with a new set of contents. The calling agent governs the choice of actions.

The impact event processor then evaluates the previous instance of the impact cell to decide if a valid permutation control is present. If it finds a valid control, it composes an email message. It uses the content of the permutation control to derive the content of the message. Then it sends the message to the next impact cell for processing. This process continues for each impact cell until the final cell to receive an email message has no valid permutation control. The sequence of a cell permutation generally goes from direct precedents to indirect precedents. However, it can include independent cells. It can invoke user-defined subroutines and it can retrieve alternate model versions that it finds named in the permutation control.

It is not possible for a loop that returns to the first impact cell to continue. The first impact cell has its permutation control cleared. Therefore, no further email message can be composed or sent. In addition, the impact event processor identifies the origin cell as the first cell on the undo list and updates the origin cell. Since the origin cell does not allow further permutations, i.e., it does not send out any additional email messages, the process ends. An infinite loop cannot occur.

A permutation loop, however, can occur. Normally the origin cell only sends an email message and does not receive one. The permutation loop occurs when the origin cell receives a return email message. In this case the origin cell first becomes independent, and then, in response to the return message, it changes back into a dependent cell. This event is easy to identify but it does require one additional processing step to complete the permutation process.

When the permutation loop event occurs, the impact event processor identifies it and re-enters the original user entry into the cell. Thus, a second permutation of the model takes place that is system generated for the user.

The reason for the second permutation event is the result of mathematical consequences that derive from the first permutation in which the loop occurred. In the first loop, the trail leading up to the loop consists of cells that the impact event has changed. The loop itself also consists of cells that have changed. However, when the loop encounters the trail leading into it, it retraces that trail back to the origin and changes the origin cell back into its original form. The loop cells are not changed back. Conceptually, two models coexist, the loop model and the trail model, but they are unsynchronized. They each have a different idea about how they should organize the model, and their mathematical result is incomplete. To complete the mathematical model the system resubmits the change event. As a result, it changes the trail cells, it changes the loop cells, and it changes the origin cell, so that the model correctly synchronizes. Calculating both the trail cells and the loop cells in the order of the permutation process is important. Simply resetting or undoing the model formulas does not result in the same mathematical result. The impact event must re-enter the change instruction into the cell and the permutation process is then completed to result in a synchronized model.

The permutation loop is different from a circular reference in a current cell formula. A circular reference occurs when a cell's formula or one of its indirect precedent cell's formulas refers to it. Circular references are controlled using the iteration controls of the current art spreadsheet. The circular reference relates to calculations and the permutation loop relates to permutations. It is, in fact, the circular reference in the cell that can cause the permutation loop. However, the impact event processor must handle the two events separately. The permutation loop gets handled first, then the iteration controls handle the circular reference. Next the reinitiated permutation gets handled and then the iteration controls handle the circular reference when it recalculates the permutated model.

The permutation loop is not problematic and it cannot continue indefinitely. The permutation loop is an acceptable and anticipated form of permutation behavior correctly handled in the invention.

The preferred embodiment of the present invention comprises not only a standalone polymorphic spreadsheet delivery format, but also simultaneously is available as a polymorphic spreadsheet component.

A component is an object that encapsulates a set of behaviors such as the behaviors of a spreadsheet. A developer in an application can include the component to deliver the services of the component to the end user. A component also has a public interface part so that certain of its operations are accessible or callable by the developer or user. The polymorphic spreadsheet component of the preferred embodiment of the present invention is built using a component architecture with user callable operations. By using the user callable operations to update cell contents from within user-defined macros or subroutines, the subroutines can correctly apply the cell content to the polymorphic model according to the behaviors of the invention. Because the standalone version is based upon the component architecture, the polymorphic component is simultaneously available in an independent form.

The component architecture of the preferred embodiment, therefore, serves a dual purpose. On one hand the interface part is accessible to the application developer using the component during application development. On the other hand the interface part is accessible to the user of the standalone polymorphic spreadsheet when writing macros or subroutines that require polymorphically and permutations enhanced behaviors when entering information into individual spreadsheet cells.

Polymorphic modeling in the present invention also extends into every domain of spreadsheet operation which includes optimization using constraints. The cell validation rule and the solver add-in constraint processing of the current art are replaced in the invention with a constraint control attribute located in the cell data structure.

The activation of the constraint controls is optional and user selections can start their operation for individual cells, groups of cells, or for the entire model. The constraint control operates with the other permutation behaviors of the invention.

The constraint control is analogous to the permutation control. However, it contains both constraint behaviors and permutation behaviors. For an independent cell, a simple constraint can accomplish the same task as a validation rule, i.e., test a value and restrict the accepted values entered. However, since a user can enter a value into a dependent cell in a polymorphic spreadsheet, there is a need for a validation rule to apply to the dependent cell as well. Therefore, the invention is replaces the independent cell validation rule of present art with constraint control. The constraint control processor, however, does not have the limits of just a simple validation rule. The first position in the constraint control is the current constraint behavior which can be a complex structure using behaviors joined with "and" or "or" operators, etc. The following positions contain future behaviors in precedence order, followed in the last position by a constraint type identifier, i.e., such as maximize, minimize, converge, and the like.

When a user selection triggers constraint processing, the constraint processor evaluates each cell's constraint control to identify binding constraints. When a constraint is binding, the constraint processor builds the appropriate constraint-defined impact rule and places it in its own first available future position. Then, in a series of model permutation events, it places the binding constraints into the cells, one at a time, triggering permutation events, in the precedent order of the permutation process. When all binding constraints are in place, it iterates recalculations by resubmitting values into the model until it matches the value, they reach a maximum or minimum number or the number of iterations in the iteration control is completed.

Since the constraint control is editable using the formula edit-bars or a list box display, users can view the exact constraint content of the solution that the constraint operation applied to each cell. If users choose to edit the constraint, they can reconstruct the model with their own variations applied. Thus, users have complete control over the constraint version of the permutation model contained in the constraint control. Put simply, a constraint is nothing more than a multiple allocation method. The system will provide a first probable solution, but the user has access to and control over the outcome. Therefore, constraint processing becomes reclassified in the invention. It is no longer an alternate computation method in fixed processing logic. The constraint process is a multiple impact allocation method of the polymorphic model of the invention.

The permutation behaviors of the permutation-enabled and email-enabled cells of the invention, in combination with the permutation-enabled operation of the constraint processor provides fast, reliable, predictable modeling alternatives unlimited in scope.

In the invention, every spreadsheet activity that can affect the contents of a cell is permutation-enabled. Entering any type of data into a spreadsheet cell is a permutation-enabled event that can trigger a permutation control and/or a constraint control for both independent and dependent cells. Thus every entry into every cell can potentially require multiple functions to be performed, i.e., the multifunction cells evaluate and maintain complex relationships that make the cells interoperable with various other elements of the model.

Beyond the cell permutation control and constraint control interfacing is the broader issue of spreadsheet operations that transfer or move cell contents from one location to another. For example, entering data while multiple sheets are selected is permutation-enabled because the invention maintains relative address references for not just the current cell contents but for the entire contents of the permutation control, the constraint control, and the original state control.

Clipboard operations such as cut, copy or paste that operate with cell contents are permutation-enabled. First, the invention enhances the clipboard itself to store and retrieve, not just the normal cell contents, but also the contents of the permutation control. Second, the invention maintains the relative cell references when retrieving cell content from the enhanced clipboard during the paste event for every cell control in the cell data structure.

Insert and delete operations such as insertion or deletion of cell(s), row(s), column(s) or sheet(s) are permutation-enabled because the movement of cell contents will change all relative references in each of the cell's controls, i.e., the permutation control, the constraint control and the original state control.

Drag-copy and drag-move operations are permutation-enabled because when content is copied or moved the invention maintains the relative references in the cell's controls.

In addition, the invention permutation-enables many functions so that they become permutation aware. For example, the sum function can act as both a single or a multiple impact targeting trigger and a table of functions and their inverse forms is maintained as a lookup table for use during the derivation of permuted equations.

The invention also maintains cell controls for sheet operations of insertions, deletions, renames, and other spreadsheet elements that appear within model equations. It correctly maintains the references to these elements that appear throughout the model.

Undo operations are permutation-enhanced since not only are the cell contents affected but the cell controls must also be correctly reset according to the undo or reset options selected by the user.

The present invention provides multifunction cells where every cell is simultaneously able to receive a value as input to the cell, contain a formula with which to calculate the cell's value or contents, and contain a permutation control that, when triggered, will result in a permutation of the model into a new form, and optionally contain a processing constraint control. Support for these multiple functions, individually and in combination, provides a multifunction cell with advanced capabilities for permutation-enhanced polymorphic modeling.

The invention provides a more intelligent cell that is capably interoperable with other cells, other objects, interactive controls, user-defined macros or algorithms, and so forth. These smart cells can send, receive and process email messages. These messages take the form of: from (a caller cell), to (a receiver cell), the message (any valid cell contents), the message content type, and postscripts (keywords or other required information for permutation events such as a node list, a subroutine name, a version name, or the like.)

The user can interact directly with the cell's contents at design-time or at runtime through dynamic interactions based on user controllable selections.

The present invention enhances the cell data structure in computer memory to provide for more than one set of cell contents for each cell that appears on the display screen. These cell contents comprise pairs of cell contents: a pair of current and previous cell contents provide access to the essential pair of current and previous permutation controls. The current and previous cell contents allow the user to undo an action and reset the model during various stages of operation. The current and previous permutation control content facilitate a model permutation.

Another pairing is the current cell and one or more impact cells that when combined each comprise the from-to content of the inter-cell email messages. The cell's data structure also provides analytical contents such as a constraint control, permutation controls, and the original state control.

The present invention also links the constraint control to the standard spreadsheet iteration controls that control intentional circular references among spreadsheet formulas. In so doing, the model permutation process remains synchronized with the standard calculation method. Thus, user selected iteration controls are coordinates with the model constraint controls.

In addition, the invention permutation-enables many functions so that they become permutation aware. For example, the sum function can act as both a single or a multiple impact targeting trigger and a table of functions and their inverse forms is maintained as a lookup table for use during the derivation of permuted equations.

These and other objectives are achieved in the invention by providing more than one mode of operation in combination with permutation-enabled and email-enabled cells with the ability to operate on a single spreadsheet model's data with an unlimited number of alternative models. The user is given a plurality of impact cell types for complete control over model permutation behaviors, multifunction cell operations for context-sensitive modeling, interoperable cell behaviors to execute intelligent inter-cell communication, iteration, synchronization, and constraint enabled analytical operations. The user is also given direct access to the cell's enhanced data structure where each display cell can store more than one processing cell in computer memory, as well as mode control and model permutation control and callable user interfaces.

The result is a one-to-many spreadsheet that is polymorphic, i.e., can change from one form into another one of many alternative forms. Thus, a broader spectrum of modeling solutions is available while maintaining the speed and ease of the familiar spreadsheet human interface.

DEFINITIONS

The following terms are used in the ensuing description:

Behavior is an operation or action to be taken or executed.

Cell is an addressable storage unit for information. It is interchangeable with the term "node,"

Cell address is the location of an editable field (node) denoted according to a specified row and a specified column.

In a spreadsheet the cell address row is denoted by a number and the column is denoted by a letter (although sometimes this arrangement is reversed). B3 would be the node in the second column and third row, for instance. In a non-spreadsheet modeling environment each cell address is the intersection of a variable name and a period identifier, i.e., the modeling alias for cell B3 might be "expenses" in period "three".

Cell attribute is any property, quality, or characteristic that can be ascribed to a cell.

Cell locator is an additional address specification for arrays with more than two dimensions. It can be designated as a page, a book, a library, a volume, inter alia.

Constraint control is a current constraint equation, a list of operations or actions to be taken or executed to change the model as regards node contents, and a constraint type designation, i.e., operation control parameters such as maximize, minimize, converge on value and the like, taken together.

Constraint controls are triggered in the invention by context-sensitive event processing. Constraint controls are evaluated to determine the actions to be taken in order to permute the current model into a new form when the constraint control is triggered.

Constraint data structure, "CDS", is a term used to denote a vertical storage format for the node's constraint control which replaces the node's validation attribute in the node data structure.

In CDS format, the constraint control is stored in a vertical format such as a string list or linked list. The CDS storage format uses list processing (add, delete, index) or pointer operations to store and extract the individual items contained within it.

Constraint formula structure, "CFS", is a term used to denote a horizontal storage format for the node's constraint control.

In CFS format, the constraint control is stored in a horizontal format such as a long string type.

Current content is the first component part of the permutation control. It contains the current node content.

Dependent cell, formula cell or dependent variable is a node that contains a formula used to calculate the node's value.

Dependent interactive impact cell is a formula type node that contains an interactive permutation control.

Dependent non-sibling is a content type receiver node that can be passed an equation by a user defined multiple impact cell that is not a precedent cell.

Dependent sibling is a content type receiver node that can be passed an equation by a user defined or multiple impact cell that is a precedent cell and thus the cell is a sibling, i.e., the cell is a member of a group of cells.

Dependent single impact cell is a formula node that contains a single impact permutation control attribute. It will impact only one receiver node.

Dependent user defined multiple impact cell is a formula node with a user defined permutation control that will impact a plurality of receiver nodes.

Direct precedent cell or variable value node is a node that appears in the right-hand side of a node's formula.

Edit field is an editable memory location, often called a cell or node in a spreadsheet application and often called a node in a multidimensional modeling application.

Enhanced data structure, "EDS", is a designation for a vertical storage format for the node's permutation control.

In EDS mode, the permutation control is stored in a vertical format such as a string list or possibly a linked list. The EDS storage format uses list processing (add, delete, index) or pointer operations to store and extract the individual items contained within it and is best used for table displays and to enhance processing speed.

English language syntax refers to the use of English language identifiers for nodes in a model. For example, variable names and period names in place of row and column identifiers.

Enhanced formula structure, "EFS", is a designation for a horizontal storage format for the node's permutation control.

In EFS mode, the permutation control is stored in a horizontal format such as a long string type. This format is best used in formula edit-bar application and to minimize memory use.

Enhanced formula syntax is an order of precedence that is used to parse the contents of the permutation control, the constraint control, or the original state control into its component parts.

Enhanced standard mode is a mode of operation of the present invention wherein the contents of an impact node are protected.

When an entry is made into an enhanced standard mode cell, an interactive display warns the user and provides several options, protect the cell contents and stop, proceed with the permutation process, or take another action such as load a model version selected from a list of model versions that are available.

Execute is to follow to the end, as in sequence, to perform, to carry into complete effect, to complete; to accomplish; to finish; to give effect to; to perform what is required to give validity to (the model); accomplish; complete.

Extended cell address is the concatenation of a cell address with cell locators, e.g., library1:book1:sheet1:B3.

Formula cell, dependent cell or variable node is a node that contains a formula that is used to calculate the node's value.

Formula subtype is a designation for a subtype of the permutation control attribute that passes an equation or formula during the permutation process.

Formula variable or dependent variable is a field whose value is calculated from a formula. It is equivalent to a dependent cell in a spreadsheet.

Future contents is the second component part of the permutation control where alternative future node contents are stored for use during the permutation process.

Impact or impact event is a collision between an entry made into a node with the current contents of the node that will trigger a model permutation.

Impact cell or impact node is a node that contains a permutation control, an original state control, and may contain a constraint control.

Impact cell type or impact cell sub-types are synonymous with the node's permutation type identifier or permutation control attribute and the corresponding permutation control attribute sub-types.

The types and subtypes represent a hierarchy of permutation behaviors that can be triggered by the invention's context-sensitive impact event processing. Thus, each permutation behavior represents a particular impact cell type. Some impact cell types, in turn, have subtypes.

Impact event or impact is a collision between an entry made into a node with the current contents of the node that will trigger a model permutation.

Impact type is a class type or type-subtype designation for each distinct kind of executable permutation behavior that resolves the conflict created by an impact or collision between an entry made into a cell with the current contents of the cell and is synonymous with the cell's permutation control attribute or permutation type identifier.

Independent cell, input cell or independent variable is a node that contains a numerical or text value, i.e., it does not contain a formula or equation that calculates its value.

Independent content subtype is a permutation control attribute subtype. Independent content is generally a number or text.

Independent impact cell is an independent node with a permutation control.

Independent interactive impact cell is an input node with an interactive permutation type identifier.

Independent single impact cell is an input type node that contains a single impact permutation type identifier. Independent single impact cells can consist of two sub-types, a value subtype or a formula subtype.

Indirect precedent cell is a node that does not appear specifically in a cell's formula, i.e., is not a direct precedent cell, but that does appear in a direct precedent cell's formula.

Input cell or independent cell is a node that contains a numerical or text value, i.e., it does not contain a formula.

Input variable or independent variable are synonymous terms in a modeling application and are equivalent to an independent cell or input cell in a spreadsheet.

Interactive impact cell is a node that contains an interactive permutation type identifier stored in the cell's permutation control.

An interactive impact cell causes interaction with the user at runtime. This interaction provides for the selection of the pathway through the model that the permutation process will follow. Thus the user can interactively select among alternate permutation paths.

Interdependent cell type or sibling type, is a group of nodes with a common parent that must act as group during the permutation process.

Sibling cells are interdependent upon one another and upon the parent cell. A permutation of the parent cell causes a permutation of all of the sibling cells. In turn, a permutation to a single sibling cell causes a permutation not only to the parent, but also to each of the siblings.

Interoperable cell is a node that is able to send and receive email messages and thus operate in conjunction with several spreadsheets including user interface objects, other cells, and formulas not in the active spreadsheet but available from memory storage or in a spreadsheet stored on a network.

Model, a set of equations used to calculate a set of results.

Model rules or Rules are equations or formulas that are assigned to variables and periods in the modeling environment and are equivalent to the term formula in a spreadsheet. Rules or formulas in the modeling application are often expressed with English language syntax while formulas in a spreadsheet application use cell references. Both perform the same function.

Multifunction cell is a node that can behave as an input node, a formula node, an impact node, a constrained node, or a combination of these node types, in a context-sensitive manner, i.e., depending on the circumstances, one or more of several node functions is enacted.

Thus, in the present invention, a multifunction cell is able to receive a value as input to the cell, contain a formula with which to calculate the cell's value or contents, and contain a permutation control that, when triggered, will result in a permutation of the model into a new form. It may contain a constraint control that when triggered will result in a constrained version of the permutated model. Support for all of these functions, individually and in combination, provides a multifunction cell with advanced capabilities for a model permutation.

Multiple allocation is a type of permutation behavior in which sibling nodes receive an equation from a set of equations located in the permutation control of a parent node.

Multiple allocation impact cell is a parent node that contains a permutation control that, when triggered, will cause permutations to multiple sibling nodes during the permutation process.

Multiple impact cell is a node that contains a permutation control that, when triggered, will cause permutations to multiple nodes during the permutation process.

Node is the location of an editable field in a multidimensional data structure. It is often called a cell in a spreadsheet application, and sometimes referred to as an editable field in a modeling application.

Order of precedence indicates the sequence of a series of behaviors.

Original state control is an archive of a node's original permutation control that is used to undo or reset the node to its original state when triggered in the invention by context-sensitive event processing.

Original state data structure, "ODS", is a designation for a vertical storage format for the node's original state control.

In ODS format, the original state control is stored in a vertical format such as a string list or linked list. The ODS storage format uses list processing (add, delete, index) or pointer operations to store and extract the individual items contained within it.

Original state formula structure, "OFS", is a designation for a horizontal storage format for the node's original state control. In CFS format, the original state control is stored in a horizontal format such as a long string type.

Parent cell is the node whose permutation control determines the permutation operations performed on a group of sibling cells during a permutation process.

Permutation behaviors are operations or actions to be taken or executed to change the model as regards order or arrangement of the model's contents.

Permutable means capable of being permutated; exchangeable. In a polymorphic model or spreadsheet, a model that is capable of being permutated; one model form is exchangeable with another model form.

Permutation means to interchange; to change among various things at once; any of the different ways a set of quantities can be arranged. In a polymorphic model, any of the different ways a set of model equations can be arranged.

Permutation content is one or more future node contents, i.e., next node contents. It is the second component of the permutation control.

Permutation control is a term designating the combination of the current node content, permutation content of one or more future node contents and a permutation control attribute.

Permutation controls are read and acted upon in the invention by context-sensitive event processing. Permutation controls are evaluated to determine the actions to be taken in order to permute the current model into a new form when the permutation control is triggered.

Permutation control attribute or permutation type identifier is the third component part of the permutation control and stores an identifier that indicates the type of permutation behavior that has been assigned to the node either by the software or by a user selection.

Permutation control storage format is either a vertical or horizontal storage format for the node's permutation control.

Permutation enabled cell is a node that contains a valid permutation control and which exists within the context of the polymorphic method of the present invention that is capable of performing the node by node permutation process. The permutation control is used to permute the model when an impact or collision occurs between an entry into the cell and the current contents of the cell.

Permutation enabled clipboard is a clipboard that is able to store and retrieve permutation enabled node contents that include a permutation control, a constraint control and an original state control in addition to conventional node attributes.

Permutation enabled clipboard behaviors are the activities of cut, copy, paste and the like that are able to operate using permutation enabled nodes to exchange information between the clipboard and the model nodes in a way that maintains the validity of the entire node contents which includes a permutation control, a constraint control and an original state control in addition to conventional node attributes.

Permutation enabled component architecture is an implementation strategy that produces a software component with public interface routines for users and developers.

In the polymorphic, permutation enabled spreadsheet component of the present invention, the permutation enabled operations of the component are declared as public operations that are accessible to the user or developer. These routines should be used when the contents of a cell are being updated. This insures that the polymorphic principles of the present invention are correctly enforced for user defined program or subroutine controlled input operations.

Permutation enabled constraint control is a constraint control that is able to operate on permutation enabled nodes so that the execution of a constraint operation interfaces with the node's permutation control to insure proper model permutations when entries are made into the node by the constraint operations.

Permutation enabled insert and delete operations are operations that maintain relative node references and other node interrelated attributes that can affect the permutation control, the constraint control, and the original state control during insert or delete operations affecting cell(s), row(s), column(s), or sheet(s).

Permutation enabled macros are subroutines that perform a sequence of operations, automatically, in a step-wise fashion on permutation enabled nodes and that specifically use the callable permutation interface of the invention so that the permutation behaviors of the invention are employed to insure a valid model permutation outcome during the execution of the macro.

Permutation enabled multiple sheet selections represent the ability of behaviors that affect multiple sheets, when multiple sheet selections have been made, to exchange information between permutation enabled nodes on multiple sheets to correctly store permutation controls appropriate to each sheet selected and/or correctly operate during the permutation process.

Permutation enabled sheet operations are insert, delete, and rename sheet operations that correctly maintain the node permutation control, constraint control and original state controls on nodes that are affected during these sheet operations.

Permutation enabled subroutines or permutation enabled macros are small, specialized programs or a list of behaviors, operations, or actions to be taken or executed that use the callable permutation interface routines of the invention, so that permutation behaviors of the invention are applied during the operation of the subroutine to insure proper permutation behaviors throughout the operation of the subroutine.

Permutation locked cell is a node that contains a permutation control attribute that prevents any attempt to change the contents of the node, what-so-ever. Thus, the cell is locked and no permutation can occur to it.

Permutation process is a process triggered by context-sensitive event processing causing a permutation of the model.

Permutation control attributes of the cells in the model are evaluated in the permutation process to determine the actions to be taken in order to permute the current model into a new form.

Permutation of a model, any of the different ways a set of node contents, i.e., text, equations and values, can be arranged.

Permutation prompt cell type is a node that does not contain all of the necessary information to complete a valid permutation control. The cell will interactively prompt the user for the information that is needed to finalize the content of the permutation control.

Permutation protected cells, sheets and models are nodes, sheets or models that have permutation control attributes that call for the protection of node, sheet or model contents.

Protected cells, sheets, or models will prompt the user before a permutation process is allowed to proceed to completion.

Permute is to interchange. To change as regards order or arrangement. In a polymorphic model, to change the model from one form into another one of a plurality of forms.

Permute mode is one of four polymorphic operating modes, viz., permute mode, polyversion mode, constraint mode and reset mode.

In permute mode, user selected permutation control options are stored in the node's data structure to control model permutation. Event processing triggers the activation of these controls.

Polyalgorithmic cell is a node that stores and triggers a user-supplied permutation enabled subroutine or algorithm when the node's permutation process is triggered.

Polymodal is the ability of the present invention to provide the user with several operating modes.

Each operating mode provides additional options for model permutation in order to achieve a substantially unlimited set of permutation capabilities.

Polymorphic mode is an operating mode of the present invention.

The polymorphic mode provides four operating options, viz., permute mode, polyversion mode, constraint mode, and reset mode.

Polymorphic spreadsheet is a spreadsheet model having many forms; a spreadsheet model assuming many forms.

Polymorphism is the property of existing in different forms. Polymorphous, polymorphic, having many forms; assuming many forms. In a polymorphic model or spreadsheet model, a model that can assume a plurality of forms.

Polyversion cell is a node whose permutation control stores and triggers a retrieval of a user-supplied partial or complete model version during a permutation process.

The named version (sans data) is retrieved into the active model when the cell's permutation process is triggered.

Polyversion mode is one of four polymorphic operating modes, viz., permute mode, polyversion mode, constraint mode and reset mode.

Polyversion mode provides the ability to apply more than a single set of model logic to a single set of spreadsheet data. In polyversion mode, an alternate one of several model versions is loaded into active memory of a spreadsheet, using a single set of data that remains unchanged until the alternate model is loaded and calculated.

Protection control attribute is an attribute stored in the model data structure that controls model, sheet, and node level protection operations.

Receiver cell or receiver node is the target node in a permutation process.

Reset model mode is one of four polymorphic operating modes, viz., permute mode, version mode, constraint mode and reset mode.

In reset model mode, the user is able to control model reset events to isolate or accumulate a series of model permutations and reset those changes in the model to one of its previous states. Reset mode affects only model logic but has no impact on model data.

Sibling subtype is a designation for a group of nodes which are found in the permutation control content of multiple impact nodes that share a common parent.

Single impact cell is a node that contains a permutation control that results in a permutation of only one other node during the permutation process.

Snap shot is the ability to capture the entire contents of the model at a given point in time so that the entire model (not the data) can be reset to that point in time by an event-driven command received from the user.

Spreadsheet model is a set of node contents comprised of text, equations and values used to calculate a spreadsheet's nodes.

A target cell is the receiver node in a permutation process. Thus, it is a cell that is being instructed to change the contents of its permutation control.

Trigger cell is a dependent node or independent node that contains a valid permutation control.

Undo mode is an application level control that provides the user with the ability to undo the effects of a recent set of changes, independent of the reset mode.

User callable interface routines are a public interface to the executable operations of the invention in a component form, that take a set of parameters as input to the routines and perform operations that are useful in permutation enabled macros and permutation enabled subroutines or in application development using the component form of the invention.

The user or developer may execute these routines by addressing them by name and including the correct parameter list enclosed in parenthesis and separated by commas.

A user defined multiple impact cell is a node that contains user defined contents in the node's permutation control. It impacts multiple cells during a permutation process.

User defined subroutine is an additional multiple impact node type where a node's permutation control contains the name of a permutation enabled user defined algorithm or subroutine that is to be stored as the permutation control and is executed during a permutation process when triggered.

Value is a number or text. It is not a formula.

Value subtype is a numerical value that is contained within a node's permutation control that will be passed to another node during a permutation process.

Variant structured control attributes are controls with a variant record structure.

The variant structure may be used, for example, for the permutation control, the constraint control and the original state control attributes. The variant structure is useful when multiple interchangeable record formats are desirable. Thus one format may be use in one instance and another format in another instance. Because there are two useful formats for each of these controls, i.e., for the permutation control the EFS and EDS formats, for the constraint control the CFS and CDS formats, and for the original state control the OFS and ODS formats the variant structure may be useful. The variant structure may be used so that a single memory location can be used for one or the other format. The selection of the format type is then designated by an index value such that the format of the variant structure permutation control attribute is as follows for the permutation control and like structure for the constraint and original state controls.

              Case permutation_control_structure of
                    Efs: (Efs_Horizontal_Format: String);
                    Eds: (Eds_vertical_format: TstringList);
              end;

                 PERMUTATION CONTROL STRUCTURE OF
                  HORIZONTAL:     EFS: STRING;
                  VERTICAL:       EDS: TSTRING;
                  END.
                 CONSTRAINT CONTROL STRUCTURE OF
                  HORIZONTAL:     CFS: STRING;
                  VERTICAL:       CDS: TSTRING;
                  END.
               ORIGINAL STATE CONTROL STRUCTURE OF
                  HORIZONTAL:     OFS: STRING;
                  VERTICAL:       OFS: TSTRING;
                  END.

    INITIALIZE                   INITIALIZES CELL CONTENT
    INITIALIZE P CONTROL         INITIALIZES PERMUTATION CONTROL CONTENT
    INITIALIZE C CONTROL         INITIALIZES CONSTRAINT CONTROL CONTENT
    INITIALIZE O CONTROL         INITIALIZES ORIGINAL CONTROL CONTENT
    GET I/O CONTENT              RETURN I/O FORMATTED CONTENT
    SET I/O CONTENT              UPDATE WITH I/O FORMATTED CONTENT
    GET PERMUTE CONTENT          RETURN PERMUTATION CONTROL CONTENT
    SET PERMUTE CONTENT          SET PERMUTATION CONTROL CONTENT
    GET CONSTRAIN