System and method for performing over time statistics in an electronic spreadsheet environment
A method and system for computing statistical parameters for sets of data items, by executing instructions of a computer program that is coded within a spreadsheet. Each set is generated in a time sequence that is specific to each set. For each time sequence, each data item is one data value or a pair of data values. The data items appears one-at-a-time in only one cell structure of the spreadsheet at each time in the time sequence. The one cell structure is a single cell or two cells. A loop of iterations is performed for each set. In each iteration, a command is responded to by updating the statistical parameters based on the latest data item in the one cell structure in the spreadsheet. The updated statistical parameter are stored in a parameter field of the spreadsheet assigned to each statistical parameter.
Latest IBM Patents:
- AUTOMATIC DETECTION OF ROBOTIC PROCESS AUTOMATION TRIGGER EVENTS
- NETWORK BANDWIDTH DETERMINATION FOR ADAPTIVE WEB-CONFERENCE RENDERING AND TRANSMISSION
- Incorporating feedback in network graph hotspot identification
- Global prosody style transfer without text transcriptions
- Road icing condition prediction for shaded road segments
1. Technical Field
The present invention relates generally to the field of information processing by digital computers, and more particularly to a method and system, in an electronic spreadsheet, for computing statistical data on a set of values taken over time either by a given cell or by a pair of cells.
2. Related Art
Before computers, numerical analyses, particularly financial analyses, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerized replacement for the traditional financial modelling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what word processors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this “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 communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-left-hand corner), for example, the user types in “A1”. In this manner, the spreadsheet defines an addressable storage location or “cell” at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or “formulas” specifying calculations to be performed on the numbers stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-OsbomeIMcGraw-Mll, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform “what-if” scenarios. After a set of computational relationships has been entered into a worksheet, thanks to imbedded formulas for instance, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require the recalculation of every relationship in the model with each change made. Electronic spreadsheet systems are well suited to solve “what-if problems’ that is, changing an input and seeing what happens to an output.
Electronic spreadsheets have become a tool of choice for computing statistics from a set of data. For a set of data arranged in a column, conventional electronic spreadsheet tools include means, in the form of statistical functions, to compute the average of the data set, or the maximum value of the data set, or the minimum value of the data set, or the standard deviation of the data set. For a couple of data ranges arranged for instance as a pair of columns, conventional electronic spreadsheet tools include means, in the form of statistical functions, to compute the covariance of the two data sets, or the correlation of the two data sets. When electronic spreadsheets are used to perform simulations of a model (business model, or industrial model, or nature life model), these statistical functions are very useful for deriving statistical information relative to the different simulation instances of the modelled process. Typically such simulations are performed by feeding a model with inputs which can either be randomly generated, or imported from external sources. Unfortunately these statistical functions operate on the base of a set of data, recorded within data ranges, so that each simulation must be first properly recorded onto a simulation result array, to eventually build a complete array of results upon which the statistical functions can operate. When the number of simulations increases (this is required to increase the confidence in the statistical results), the volume of information that may result may be prohibitive (and even reach the built-in limitations of the commercially available electronic spreadsheet tools). As the desired information relies to the statistical data, and not to each individual simulation output, the conventional statistical means available in electronic spreadsheet environment, are not suited for computing simulation statistics.
SUMMARY OF THE INVENTIONThe present invention provides a method for computing at least one statistical parameter for at least one set of data items, said method comprising performing a loop having a plurality of iterations for each set of data items, said method being performed by execution of instructions by a processor of a computer system, said instructions comprised by a computer program that is coded within a spreadsheet, said spreadsheet being stored in a memory of the computer system, each set of data items having been generated in a time sequence such that each data item corresponds to a unique time in the time sequence, said time sequence being specific to each set of data items, for each time sequence each data item consisting of one data value or a pair of data values, the data items for each time in the time sequence appearing one-at-a-time in only one cell structure C of the spreadsheet in accordance with the time sequence, the one cell structure C consisting of a single cell or both a first cell and a second cell corresponding respectively to the one data value or the pair of data values, each iteration of each loop comprising:
receiving a command to update the at least one statistical parameter for a set S of the at least one set, said command identifying the cell structure C pertaining to the set S, said cell structure C comprising a latest data item of the set S, said spreadsheet not comprising any other data item of the set S, said latest data item corresponding to a latest time in the time sequence of the set S;
responsive to said command, updating the at least one statistical parameter for the set S based on the latest data item in the cell structure C to generate an updated at least one statistical parameter value for the set S; and
storing each updated statistical parameter value for the set S in a parameter field of the spreadsheet assigned to each statistical parameter.
The present invention provides a computer system comprising a processor and a memory, a spreadsheet being stored in the memory, said spreadsheet having a computer program coded therein, said computer program comprising instructions that when executed by the processor perform a method for computing at least one statistical parameter for at least one set of data items, each set of data items having been generated in a time sequence such that each data item corresponds to a unique time in the time sequence, said time sequence being specific to each set of data items, for each time sequence each data item consisting of one data value or a pair of data values, the data items for each time in the time sequence appearing one-at-a-time in only one cell structure C of the spreadsheet in accordance with the time sequence, the one cell structure C consisting of a single cell or both a first cell and a second cell corresponding respectively to the one data value or the pair of data values, said method comprising performing a loop having a plurality of iterations for each set of data items, each iteration of each loop comprising:
receiving a command to update the at least one statistical parameter for a set S of the at least one set, said command identifying the cell structure C pertaining to the set S, said cell structure C comprising a latest data item of the set S, said spreadsheet not comprising any other data item of the set S, said latest data item corresponding to a latest time in the time sequence of the set S;
responsive to said command, updating the at least one statistical parameter for the set S based on the latest data item in the cell structure C to generate an updated at least one statistical parameter value for the set S; and
storing each updated statistical parameter value for the set S in a parameter field of the spreadsheet assigned to each statistical parameter.
The present invention provides a computer program comprising instructions that when executed by a processor of a computer system perform a method for computing at least one statistical parameter for at least one set of data items, said computer program being coded within a spreadsheet, said spreadsheet being stored in a memory of the computer system, each set of data items having been generated in a time sequence such that each data item corresponds to a unique time in the time sequence, said time sequence being specific to each set of data items, for each time sequence each data item consisting of one data value or a pair of data values, the data items for each time in the time sequence appearing one-at-a-time in only one cell structure C of the spreadsheet in accordance with the time sequence, the one cell structure C consisting of a single cell or both a first cell and a second cell corresponding respectively to the one data value or the pair of data values, said method comprising performing a loop having a plurality of iterations for each set of data items, each iteration of each loop comprising:
receiving a command to update the at least one statistical parameter for a set S of the at least one set, said command identifying the cell structure C pertaining to the set S, said cell structure C comprising a latest data item of the set S, said spreadsheet not comprising any other data item of the set S, said latest data item corresponding to a latest time in the time sequence of the set S;
responsive to said command, updating the at least one statistical parameter for the set S based on the latest data item in the cell structure C to generate an updated at least one statistical parameter value for the set S; and
storing each updated statistical parameter value for the set S in a parameter field of the spreadsheet assigned to each statistical parameter.
From a memory consumption and computing power consumption standpoint, the present invention is much more efficient than conventional means available in electronic spreadsheet environments.
BRIEF DESCRIPTION OF THE DRAWINGSThe present invention will best be understood by reference to the following detailed description of an illustrative detailed embodiment when read in conjunction with the accompanying drawings.
The present invention is directed to a method, system, and computer program for managing and/or computing statistical information based on a set of values taken by a given cell or by a given pair of cells of a spreadsheet. The present invention comprises means for controlling, for a given cell or a given pair of cells, the set of values upon which the statistical computing is performed.
When invoked in a first mode called “Reset mode”, the method re-initializes the computing of statistical information, to get rid of any past history.
When invoked in a second mode, called “Run mode”, the method computes the statistical information based on the current value of the given cell or of the given pair of cells, and of the past historical values taken during the current “Run mode”.
The method according to the present invention, for use in a multi-dimensional spreadsheet comprising a plurality of cells identified by a cell address along each dimension, comprises the steps of:
receiving a command for computing statistical data based on successive values of a first cell over a time period, said command comprising: (1) means for identifying said first cell, (2) means for determining the beginning and the end of the time period, and (3) a reference to a statistical function for computing statistical data based on the successive values of said first cell over said time period; and
computing statistical data based on the successive values of said first cell over said time period, said computing statistical data comprising the further steps of, each time the value of said first cell is updated: (1) computing said statistical data by means of said statistical function, based on: the current value of said first cell, and the previously computed statistical data based on the successive values taken by the first cell from the beginning of the time period; and (2) storing the computed statistical data in place of the previously computed statistical data.
The present invention discloses an Over Time Statistic Manager (OTSM) handling the management and computation of statistical information, based on the values taken either by a single cell, or by a pair of cells.
The present invention solves the problem of the related art by recursively evaluating the desired statistical information, only on the base of the last simulation, thus avoiding to keep a high volume array of all simulations results.
Hardware
As shown in
Illustrated in
Interface
The following description will focus on the embodiments of the present invention, which are embodied in spreadsheet applications operative in the Microsoft Windows environment. The present invention, however, is not limited to any particular application or any particular environment. Instead, those skilled in the art will find that the system and methods of the present invention may be advantageously applied to a variety of system and application software, including database management systems, word processors, and the like. Moreover, the present invention may be embodied on a variety of different platforms, including Macintosh, UNIX, NextStep, and the like. ‘Therefore, the description of the exemplary embodiments which follows is for purposes of illustration and not limitation.
Referring now to
Windowing interface 160 includes a screen cursor or pointer 185 for selecting and otherwise invoking screen objects of interest. In response to user movement signals from the pointing device 105, the cursor 185 floats (i.e., freely moves) across the screen 106 to a desired screen location. During or after cursor movement, the user may generate user-event signals (e.g., mouse button “clicks” and “drags”) for selecting and manipulating objects, as is known in the art. For example, Window 160 may be closed, resized, or scrolled by “clicking” (selecting) screen components 172, 174/5, and 177/8, respectively.
In an embodiment, screen cursor 185 is controlled with a mouse device. Single-button, double-button, or triple-button mouse devices are available from a variety of vendors, including Apple Computer of Cupertino, Calif., Microsoft Corporation of Redmond, Wash., and Logitech Corporation of Fremont, Calif., respectively. The screen cursor control device 105 may be a two-button mouse device, including both right and left “mouse buttons.”
Programming techniques and operations for mouse devices are well documented in the programming and hardware literature; see e.g., Microsoft Mouse Programmer's Reference, Microsoft Press, 1989. The general construction and operation of a GUI event-driven system, such as Windows, is also known in the art. See, e.g., Petzold, C., Programming Windows, Second Edition, Microsoft Press, 1990. The disclosures of each are hereby incorporated by reference.
Shown in
The toolbar 220, shown in further detail in
The notebook, which provides an interface for entering and displaying information of interest, includes a plurality of spreadsheet pages. Each page may include conventional windowing features and operations, such as moving, resizing, and deleting. In an embodiment, the notebook includes 256 spreadsheet pages, all of which are saved as a single disk file on the mass storage 107. Workspace 200 may display one or more notebooks, each sized and positioned (e.g., tiled, overlapping, and the like) according to user-specified constraints.
Each spreadsheet page of a notebook includes a 2-D spread. Page A from the notebook 200, for example, includes a grid in row and column format, such as row 3 and column F. At each row/column intersection, a box or cell (e.g., cell C4) is provided for entering, processing, and displaying information in a conventional manner. Each cell is addressable, with a selector being provided for indicating a currently active cell (i.e., the cell that is currently selected).
As shown in
In addition to aiding in the selection of an appropriate page of information, the user-customizable page identifiers serve to aid in the entry of spreadsheet formulas. For example, when entering a formula referring to cells on another page, the user may simply use the descriptive page name in the formula itself (as described herein below), thus making it easier for the user to understand the relationship of the cell(s) or information being referenced.
A general description of the features and operation of the spreadsheet notebook interface may be found in Quattro Pro for Windows (Getting Started, User's Guide and Building Spreadsheet Applications), available from Borland International.
Management Over Time Statistics
In contrast with just-described conventional tools, the present invention provides a more powerful, less resource-demanding approach for managing and computing statistical data based on the set or sequence of values taken by a given cell or a pair of cells. The method according to the present invention comprises the following five steps:
The first step is building a model of the desired process, based on formulas and functions used in selected cells or ranges of cells. Such a model may be fed by input cells which can correspond to random number generators (also available as standard built-in functions).
The second step is identifying the one or plurality of cells of interest, referred to as “Output Cells” (OC), which deserve a statistical analysis. For instance it may be desired to compute, for the sequence of simulations, the standard deviation of a cell OC1, the standard deviation of a cell OC2, and the covariance between the cells OC1 and OC2.
The third step is initializing selected cells with a new set of functions, Over Time Statistical Functions (OTSF), computing the desired statistical information. Such functions are easy to master as being direct derivatives of the conventional statistical functions. If the invocation of a conventional statistical function is “@FCT(argument_list)”, then the invocation of the corresponding OTS function is “@OTFCT(Reset, argument_list)”, where the additional argument “Reset” points to a Boolean cell playing the role of a function reset. In the example herein: (1) a first cell will be filled with the formula “@OTSTD(Reset, OC1)”; (2) a second cell will be filled with the formula “@OTSTD(Reset, OC2)”; and (3) a third cell will be filled with the formula “@OTCOV(Reset, OC1, OC2)”. The value of the cell “Reset” is set to
In the fifth step, should the user want to run another set of simulations, then the user should first set the value of the cell named “Reset” equal to
With this scenario, no simulation table is created and managed. Therefore much less computing resources are required, and the associated capacity constraints are removed. Furthermore the loop of the fourth step above is quite simple, typically of the form: For I=1 to N; Application.recalc; EndFor.
The Over Time Statistic Manager (OTSM) is the entity managing the Over Time Statistical Functions. In an embodiment of the present invention assuming a Lotus 123 environment, the following OTSF are considered:
-
- @OTMAX(reset, cell1): for computing the maximum value taken over time by cell1,
- @OTMIN(reset, cell1): for computing the minimum value taken over time by cell1,
- @OTAVG(reset, cell1): for computing the average value taken over time by cell1,
- @OTSTD(reset, cell1): for computing the standard deviation of the values taken over time by cell1,
- @OTCOV(reset, cell1, cell2): for computing the covariance between the values taken over time by cell1 and cell2,
- @OTCORREL(reset, cell1, cell2): for computing the correlation between the values taken over time by cell1 and cell2,
where: - “reset” is the name or the address of an individual cell used to reset the computation of statistical information,
- “cell1” and “cell2” are the names or addresses of two individual cells whose set or sequence of values is the base for computing statistical information.
Other similar OTSF could be defined without departing from the spirit of the present invention.
The OTSM takes control each time an OTSF is invoked. The OTSM handles two types of internal tables:
-
- a Single Over Time Working Zone (SOTWZ) and
- a Double Over Time Working Zone (DOTWZ),
as described in the following section.
For each pair (reset, cell1) passed as argument of an OTSF is created a SOTWZ 300 the structure of which is described in
-
- a “Ptr” field 301 pointing to the address of the cell “cell1” associated to the SOTWZ 300
- an “Index” field 302 counting the number of samples used to compute the output of the OTSF,
- “max” field 303 recording the highest value of the cell “cell1” associated to the SOTWZ 300,
- a “min” field 304 recording the lowest value of the cell “cell1” associated to the SOTWZ 300,
- an “avg” field 305 recording the average value of the cell “cell1” associated to the SOTWZ 300,
- a “sqavg” field 306 recording the average of the square value of the cell “cell1” associated to the SOTWZ 300,
- A “reset” field 307 pointing to the cell “reset” associated to the SOTWZ 300.
For each triple (reset, cell1, cell2) passed as argument of an OTSF is created a DOTWZ 310 the structure of which is described in
-
- a “Ptr1” field 311 pointing to the address of the cell “cell1” associated to the DOTWZ 310,
- a “Ptr2” field 312 pointing to the address of the cell “cell2” associated to the DOTWZ 310,
- an “Index” field 313 counting the number of samples used to compute the output of the OTSF,
- a “reset” field 314 pointing to the cell “reset” associated to the DOTWZ 310,
- an “avg1” field 315 recording the average value of the cell “cell1” associated to the DOTWZ 310,
- an “avg2” field 316 recording the average value of the cell “cell2” associated to the DOTWZ 310,
- a “sqavg1” field 317 recording the average of the square value of the cell “cell1” associated to the DOTWZ 310,
- a “sqavg2” field 318 recording the average of the square value of the cell “cell2” associated to the DOTWZ 310,
- a “prod” field 319 recording the average of the product of the values of the cells “cell1” and “cell2” associated to the DOTWZ 310.
The method for managing OTDF according to the present invention is summarized in flowchart 400 of
At step 401, the method is in its default state, waiting for an event to initiate the process.
At step 402, an event is detected, as a result of an invocation of an OTSF.
At step 403, the arguments of the OTSF are retrieved (first argument under the name “reset”, the second argument under the name “cell1”, the third argument, if present, under the name “cell2”). The address or cell name contained in “cell1” (and “cell2” if present) identifies the particular sequence (S) of data items being statistically processed in the current iteration of the loop.
A cell structure C stores a data item of the latest data value(s) in the set or sequence S. The cell structure C for storing the latest data value(s) is “cell1” for a SOTWZ. The cell structure C for storing the latest data value(s) is both “cell1” and “cell2” for a DOTWZ. Since the variables “cell1” (and “cell2” if present) are received in each iteration of the loop, different iterations of the loop may pertain to the same or different sequences of data being statistically processed depending on whether the values of “cell1” (and “cell2” if present) are the same or different in the different iterations.
For example, consider a first set or sequence (S1) of data items and a second set or sequence (S2) of data items being statistically processed by looping in accordance with
At step 404, a test is performed to check if a third argument cell2 was present. If it is the case, then control is given to step 411 for implementing a DOTWZ; otherwise control is given to step 405 for implementing a SOTWZ.
At step 405 a test is performed to check if an SOTWZ 300 associated to the cell pair (reset, cell1) is already defined. If it is the case, then control is given to step 407; otherwise control is given to step 406.
At step 406, a new SOTWZ 300 is created and initialized with the following fields:
-
- the “Ptr” field 301 is initialized with a pointer to the cell cell1,
- the “Index” field 302 is initialized with a zero value,
- the “max” field 303 is initialized with the value of the cell cell1,
- the “min” field 304 is initialized with the value of the cell cell1,
- the “avg” field 305 is initialized with the value of the cell cell1,
- the “sqavg” field 306 is initialized with the square of the value of the cell cell1,
- the “reset” field 307 is initialized with a pointer to the cell “reset”.
At step 407, a test is performed to check if reset is equal to
At step 408, the SOTWZ 300 is reset with the following actions on the fields:
-
- the “Index” field 302 is initialized with a zero value,
- the “max” field 303 is initialized with the value of the cell cell1,
- the “min” field 304 is initialized with the value of the cell cell1,
- the “avg” field 305 is initialized with the value of the cell cell1,
- the “sqavg” field 306 is initialized with the square of the value of the cell cell1.
At step 409, the SOTWZ 300 is refreshed with the following actions on the fields:
-
- the “max” field 303 is set equal to max(max, cell1),
- the “min” field 304 is set equal to min(min, cell1),
- the “avg” field 305 is set equal to (index*avg+cell1)/(index+1),
- the “sqavg” field 306 is set equal to (index*sqavg+cell1*cell1)/(index+1),
- the “Index” field 302 is set equal to index+1.
At step 410, the method returns the result of the OTSF function, depending on this function, as listed below:
-
- @OTMAX(reset,cell1)=max;
- @OTMIN(reset,cell1)=min;
- @OTAVG(reset, cell1)=avg
- @OTSTD(reset, cell1)=sqrt(sqavg−avg*avg)
- @OTCOV(reset, cell1, cell2)=prod−avg1*avg2
- @OTCORREL(reset, cell1, cell2)=(prod−avg1*avg2)/sqrt(sqavg1−avg1*avg1)/sqrt(sqavg2−avg2*avg2)
Then control is returned back to the initial step 401 for handling another OTSF command for initiating execution of another iteration of a loop for a set or sequence of data items.
At step 411, a test is performed to check if an DOTWZ 310 associated with the cell triple (reset, cell1, cell2) is already defined. If it is the case, then control is given to step 413; otherwise control is given to step 412.
At step 412, a new DOTWZ 310 is created and initialized with the following fields:
-
- the “Ptr1” field 311 is initialized with a pointer to the cell cell1,
- the “Ptr2” field 312 is initialized with a pointer to the cell cell2,
- the “Index” field 313 is initialized with a zero value,
- the “reset” field 314 is initialized with a pointer to the cell reset,
- the “avg1” field 315 is initialized with the value of the cell cell1,
- the “avg2” field 316 is initialized with the value of the cell cell2,
- the “sqavg1” field 317 is initialized with the square of the value of the cell cell1,
- the “sqavg2” field 318 is initialized with the square of the value of the cell cell2, and
- the “prod” field 319 is initialized with the product of the values of the cells cell1 and cell2.
At step 413, a test is performed to check if reset is equal to
At step 414, the DOTWZ 310 is reset with the following actions on the fields:
-
- the “Index” field 313 is initialized with a zero value,
- the “avg1” field 315 is initialized with the value of the cell cell1,
- the “avg2” field 316 is initialized with the value of the cell cell2,
- the “sqavg1” field 317 is initialized with the square of the value of the cell cell1,
- the “sqavg2” field 318 is initialized with the square of the value of the cell cell2, and
- the “prod” field 319 is initialized with the product of the values of the cells cell1 and cell2.
At step 415, the DOTWZ 310 is refreshed with the following actions on the fields:
-
- the “avg1” field 315 is set equal to (index*avg1+cell1)/(index+1),
- the “avg2” field 316 is set equal to (index*avg2+cell1)/(index+1),
- the “sqavg1” field 317 is set equal to (index*sqavg1+cell1*cell1)/(index+1),
- the “sqavg2” field 318 is set equal to (index*sqavg2+cell2*cell2)/(index+1),
- the “prod” field 319 is set equal to (index*prod+cell1*cell2)/(index+1), and finally
- the “Index” field 313 is set equal to index+1.
Then control is given to step 410.
What has been described is merely illustrative of the application of the principles of the present invention. Other arrangements and methods can be implemented by those skilled in the art without departing from the spirit and scope of the present invention.
Claims
1. A method for computing at least one statistical parameter for at least one set of data items, said method comprising performing a loop having a plurality of iterations for each set of data items, said method being performed by execution of instructions by a processor of a computer system, said instructions comprised by a computer program that is coded within a spreadsheet, said spreadsheet being stored in a memory of the computer system, each set of data items having been generated in a time sequence such that each data item corresponds to a unique time in the time sequence, said time sequence being specific to each set of data items, for each time sequence each data item consisting of one data value or a pair of data values, the data items for each time in the time sequence appearing one-at-a-time in only one cell structure C of the spreadsheet in accordance with the time sequence, the one cell structure C consisting of a single cell or both a first cell and a second cell corresponding respectively to the one data value or the pair of data values, each iteration of each loop comprising:
- receiving a command to update the at least one statistical parameter for a set S of the at least one set, said command identifying the cell structure C pertaining to the set S, said cell structure C comprising a latest data item of the set S, said spreadsheet not comprising any other data item of the set S, said latest data item corresponding to a latest time in the time sequence of the set S;
- responsive to said command, updating the at least one statistical parameter for the set S based on the latest data item in the cell structure C to generate an updated at least one statistical parameter value for the set S; and
- storing each updated statistical parameter value for the set S in a parameter field of the spreadsheet assigned to each statistical parameter.
2. The method of claim 1,
- wherein if the latest data item is not a first data item in the time sequence, then the latest data item in the cell structure C has replaced an immediately preceding data item in the cell structure C and each updated statistical parameter value has replaced an immediately preceding statistical parameter value in the parameter field assigned to each statistical parameter, and wherein the immediately preceding data item and the immediately preceding statistical parameter value correspond to an immediately preceding time relative to the latest time in the time sequence; and
- wherein if the latest data item is the first data item in the time sequence, then said updating the at least one statistical parameter value for the set S comprises initializing said at least one statistical parameter value for the set S in the parameter field assigned to each statistical parameter.
3. The method of claim 1, wherein the at least one set of data items comprises a first set (S1) of data items and a second set (S2) of data items, and wherein performing the loop for the sets S1 and S2 comprises:
- after performing a first iteration of the loop for the set S1 and after performing a first iteration of the loop for the set S2, performing a last iteration of the loop for the set S1 to end the loop of the set S1 and performing a last iteration of the loop for the set S2 to end the loop of the set S2.
4. The method of claim 3, wherein each data item in the set S1 consists of one data value, and wherein each data item in the set S2 consists of a pair of data values.
5. The method of claim 1, wherein after performing a last iteration for the set S to end the loop of the set S:
- copying the updated at least one statistical parameter value from the parameter field of the spreadsheet to a corresponding at least one designated output cell in the spreadsheet.
6. The method of claim 1, wherein the method comprises randomly generating a first set of the at least one set of data items in the time sequence.
7. The method of claim 1, wherein the method comprises generating a first set of the at least one set of data items in the time sequence from a source external to the spreadsheet.
8. The method of claim 1, wherein each data item in a first set of the at least one set of data items consists of one data value.
9. The method of claim 1, wherein each data item in a first set of the at least one set of data items consists of a pair of data values.
10. The method of claim 9, wherein a first statistical parameter at the least one statistical parameter comprises an arithmetic average of the product of a first data value of the pair of data values and a second data value of the pair of data values.
11. A computer system comprising a processor and a memory, a spreadsheet being stored in the memory, said spreadsheet having a computer program coded therein, said computer program comprising instructions that when executed by the processor perform a method for computing at least one statistical parameter for at least one set of data items, each set of data items having been generated in a time sequence such that each data item corresponds to a unique time in the time sequence, said time sequence being specific to each set of data items, for each time sequence each data item consisting of one data value or a pair of data values, the data items for each time in the time sequence appearing one-at-a-time in only one cell structure C of the spreadsheet in accordance with the time sequence, the one cell structure C consisting of a single cell or both a first cell and a second cell corresponding respectively to the one data value or the pair of data values, said method comprising performing a loop having a plurality of iterations for each set of data items, each iteration of each loop comprising:
- receiving a command to update the at least one statistical parameter for a set S of the at least one set, said command identifying the cell structure C pertaining to the set S, said cell structure C comprising a latest data item of the set S, said spreadsheet not comprising any other data item of the set S, said latest data item corresponding to a latest time in the time sequence of the set S;
- responsive to said command, updating the at least one statistical parameter for the set S based on the latest data item in the cell structure C to generate an updated at least one statistical parameter value for the set S; and
- storing each updated statistical parameter value for the set S in a parameter field of the spreadsheet assigned to each statistical parameter.
12. The computer system of claim 11,
- wherein if the latest data item is not a first data item in the time sequence, then the latest data item in the cell structure C has replaced an immediately preceding data item in the cell structure C and each updated statistical parameter value has replaced an immediately preceding statistical parameter value in the parameter field assigned to each statistical parameter, and wherein the immediately preceding data item and the immediately preceding statistical parameter value correspond to an immediately preceding time relative to the latest time in the time sequence; and
- wherein if the latest data item is the first data item in the time sequence, then said updating the at least one statistical parameter value for the set S comprises initializing said at least one statistical parameter value for the set S in the parameter field assigned to each statistical parameter.
13. The computer system of claim 11, wherein the at least one set of data items comprises a first set (S1) of data items and a second set (S2) of data items, and wherein performing the loop for the sets S1 and S2 comprises:
- after performing a first iteration of the loop for the set S1 and after performing a first iteration of the loop for the set S2, performing a last iteration of the loop for the set S1 to end the loop of the set S1 and performing a last iteration of the loop for the set S2 to end the loop of the set S2.
14. The method of claim 13, wherein each data item in the set S1 consists of one data value, and wherein each data item in the set S2 consists of a pair of data values.
15. The computer system of claim 11, wherein after performing a last iteration for the set S to end the loop of the set S:
- copying the updated at least one statistical parameter value from the parameter field of the spreadsheet to a corresponding at least one designated output cell in the spreadsheet.
16. The computer system of claim 11, wherein the method comprises randomly generating a first set of the at least one set of data items in the time sequence.
17. The computer system of claim 11, wherein the method comprises generating a first set of the at least one set of data items in the time sequence from a source external to the spreadsheet.
18. The computer system of claim 11, wherein each data item in a first set of the at least one set of data items consists of one data value.
19. The computer system of claim 11, wherein each data item in a first set of the at least one set of data items consists of a pair of data values.
20. The method of claim 19, wherein a first statistical parameter at the least one statistical parameter comprises an arithmetic average of the product of a first data value of the pair of data values and a second data value of the pair of data values.
21. A computer program comprising instructions that when executed by a processor of a computer system perform a method for computing at least one statistical parameter for at least one set of data items, said computer program being coded within a spreadsheet, said spreadsheet being stored in a memory of the computer system, each set of data items having been generated in a time sequence such that each data item corresponds to a unique time in the time sequence, said time sequence being specific to each set of data items, for each time sequence each data item consisting of one data value or a pair of data values, the data items for each time in the time sequence appearing one-at-a-time in only one cell structure C of the spreadsheet in accordance with the time sequence, the one cell structure C consisting of a single cell or both a first cell and a second cell corresponding respectively to the one data value or the pair of data values, said method comprising performing a loop having a plurality of iterations for each set of data items, each iteration of each loop comprising:
- receiving a command to update the at least one statistical parameter for a set S of the at least one set, said command identifying the cell structure C pertaining to the set S, said cell structure C comprising a latest data item of the set S, said spreadsheet not comprising any other data item of the set S, said latest data item corresponding to a latest time in the time sequence of the set S;
- responsive to said command, updating the at least one statistical parameter for the set S based on the latest data item in the cell structure C to generate an updated at least one statistical parameter value for the set S; and
- storing each updated statistical parameter value for the set S in a parameter field of the spreadsheet assigned to each statistical parameter.
22. The computer program of claim 11,
- wherein if the latest data item is not a first data item in the time sequence, then the latest data item in the cell structure C has replaced an immediately preceding data item in the cell structure C and each updated statistical parameter value has replaced an immediately preceding statistical parameter value in the parameter field assigned to each statistical parameter, and wherein the immediately preceding data item and the immediately preceding statistical parameter value correspond to an immediately preceding time relative to the latest time in the time sequence; and
- wherein if the latest data item is the first data item in the time sequence, then said updating the at least one statistical parameter value for the set S comprises initializing said at least one statistical parameter value for the set S in the parameter field assigned to each statistical parameter.
23. The computer program of claim 11, wherein the at least one set of data items comprises a first set (S1) of data items and a second set (S2) of data items, and wherein performing the loop for the sets S1 and S2 comprises:
- after performing a first iteration of the loop for the set S1 and after performing a first iteration of the loop for the set S2, performing a last iteration of the loop for the set S1 to end the loop of the set S1 and performing a last iteration of the loop for the set S2 to end the loop of the set S2.
24. The method of claim 23, wherein each data item in the set S1 consists of one data value, and wherein each data item in the set S2 consists of a pair of data values.
25. The computer program of claim 11, wherein after performing a last iteration for the set S to end the loop of the set S:
- copying the updated at least one statistical parameter value from the parameter field of the spreadsheet to a corresponding at least one designated output cell in the spreadsheet.
26. The computer program of claim 11, wherein the method comprises randomly generating a first set of the at least one set of data items in the time sequence.
27. The computer program of claim 11, wherein the method comprises generating a first set of the at least one set of data items in the time sequence from a source external to the spreadsheet.
28. The computer program of claim 11, wherein each data item in a first set of the at least one set of data items consists of one data value.
29. The computer program of claim 11, wherein each data item in a first set of the at least one set of data items consists of a pair of data values.
30. The method of claim 29, wherein a first statistical parameter at the least one statistical parameter comprises an arithmetic average of the product of a first data value of the pair of data values and a second data value of the pair of data values.
Type: Application
Filed: Sep 13, 2005
Publication Date: Jun 1, 2006
Patent Grant number: 7810032
Applicant: International Business Machines Corporation (Armonk, NY)
Inventors: Frederic Bauchot (Saint-Jeannet), Gerard Marmigere (Drap)
Application Number: 11/225,424
International Classification: G06F 17/24 (20060101); G06F 3/00 (20060101);