GB2394333A

GB2394333A - Display of precedent cells in a spreadsheet

Info

Publication number: GB2394333A
Application number: GB0323612A
Authority: GB
Inventors: Iain Morrow
Original assignee: Individual
Current assignee: Individual
Priority date: 2002-10-18
Filing date: 2003-10-09
Publication date: 2004-04-21
Also published as: GB0224272D0; WO2004036453A3; WO2004036453A2; GB0323612D0

Abstract

Analysis of calculations contained within cells of spreadsheet applications by determining and displaying a list of direct and indirect precedents for the calculation in question, ignoring cells which are referenced but do not contribute to the result, having blank or zero content.

Description

1 2394333

1 "Systems and Method for Improved Data Processing" 3 The present invention relates to the field of

4 information processing by electronic means. The 5 invention is particularly concerned with the 6 processing and presentation of information by 7 computer program applications, and in particular 8 that contained in electronic spreadsheets.

10 Electronic spreadsheets, such as Microsoft Excel, 11 are known, and offer improvements in creating, 12 editing and using financial models. Electronic 13 spreadsheets permit the user to input and manipulate 14 data, through the use of standard computer input 15 devices, and to view the results of data processing 16 performed by the spreadsheet on the visual display 17 unit. Spreadsheets are essentially made up of a 18 large grid, with each box or cell in the grid having 19 a unique reference number such as Al, G8, Z15 and so 20 on. Each cell can contain one value, which is either 21 an input, or is calculated based on one or more

1 other values. Modern spreadsheets have extended 2 this, so that the values can depend on values in 3 other sheets, and can give rise to graphs or other 4 reports that do not fit in cells. There is a more 5 detailed description of what a spreadsheet is in the

6 existing US Patent 6,138,130 ("System and Method for 7 Processing Data in an Electronic Spreadsheet in 8 Accordance with a Data Type", Adler et al.).

10 The values in spreadsheets, as stated above, can be 11 defined in terms of values in other cells (or 12 elsewhere in modern spreadsheets). These values can 13 themselves depend on other values and so on. If you 14 consider the first cell to be at the base of a tree, 15 and the link to each preceding cell as a branch (and 16 the links to the preceding cell of the preceding 17 cells as branches and so on), then this gives rise 18 to a calculation tree for any particular value. The 19 term "precedents" is used here to mean the set of 20 all the cells in this tree except the first cell 21 (i.e. all the cells that the first cell explicitly 22 or implicitly depends on). The term "direct 23 precedents" is used here to mean the cells that a 24 particular cell explicitly refers to in its 25 calculation. 27 It is advantageous in certain circumstances for the 28 end user to trace the calculation of the value of a 29 particular cell. This involves displaying all or 30 part of the calculation tree for that value. Some of 31 the branches in the calculation tree are more 32 relevant for determining the value of the cell of

1 interest than others. The disadvantage of existing 2 spreadsheet programs, or other programs that analyse 3 their output, is that when tracing the calculation 4 of the value of a particular cell, a large number of 5 cells are displayed, many of which are redundant for 6 the purpose of that particular cell calculation.

7 Thus a large amount of redundant information is 8 displayed, rendering the analysis of the cell's 9 value unnecessarily time-consuming and complex for 10 the end user.

12 According to an aspect of the present invention, 13 there is provided a computer-implemented method of 14 data processing involving the steps of: 16 (i) The user selecting a cell to analyse; 18 (ii) parsing the cell's calculation rule into 19 separate elements; 21 (iii) analysing each element and determining its 22 nature; 24 ( iv) determining the cell or cells that are 25 referenced by each element determined in 26 step (ii) ; 28 (v) displaying the cells from the previous step, 29 except those which do not directly 30 contribute to the value of the cell chosen 31 by the user, including those which have a 32 blank or zero value.

2 According to a second aspect of the present 3 invention there is provided a computer based system, 4 comprising data storage means, visual display means, 5 data processing means, including computer software 6 and computer hardware, and a user interface.

8 Preferably the data storage means is any suitable 9 electronic memory unit, such as a computer disc.

11 Preferably the visual display means is a monitor.

13 Preferably the data processing means is a computer 14 program as defined according to the present 15 invention.

17 According to a further aspect of the present 18 invention there is provided a computer programmed to 19 store given alphanumeric information, and to perform 20 mathematical calculations and specialized functions 21 in accordance with user requests, such that only 22 information related to said request is displayed to 23 the user, and redundant information is hidden from 24 the user.

26 According to a still further aspect of the present 27 invention there is provided a computer program 28 product comprising a computer readable medium, 29 having thereon computer program code means, said 30 program when loaded performing the method as defined 31 by the computer program described herein.

1 For the avoidance of doubt, the program as described 2 herein may be either loaded independently from a 3 suitable spreadsheet program or may perform the 4 operations of the program as part of a suitable 5 spreadsheet program. Said program, if a separate 6 program, then integrates with the spreadsheet 7 program such that it may be accessed directly when 8 running the spreadsheet program by way of, for 9 example, an icon on the toolbar.

11 The present invention will now be described by way 12 of example only with reference to the following 13 drawing in which: 15 Fig 1 illustrates a basic spreadsheet containing 16 alphanumeric information.

18 Fig 2 illustrates an operational model of one 19 embodiment of a data processing method in accordance 20 with the present invention.

22 Referring now to Fig 1, each cell within the 23 spreadsheet has a value 10; this value may be 24 either: a value 12 which is input by the user or a 25 value which is the result of one or more 26 mathematical or other calculations 14,or it may be a 27 combination thereof. A simple calculated cell may 28 be the result of, for example, a multiplication 16 29 or an addition 18. Cell G8, 18, is calculated as As, 30 14, plus B5, 16. Cell A3, 14, is calculated as 31 A1*A2, and cell B5,16 is calculated as C1*D6. If the 32 value of a particular cell B5, is zero, 16, then

1 this cell does not contribute to the value of G8, 2 and thus constitutes redundant data for this 3 calculation tree. Each cell's calculation rule is 4 parsed for the purpose of determining the 5 calculation tree, that is, broken down into discrete 6 elements. If the element is a reference to a cell or 7 a block of cells, then the program analyses each 8 cell in that block. If the value of the cell is not 9 blank or zero then it is added to the list of direct 10 precedents for the cell of interest.

12 Referring now to Fig 2, if the element, when parsed 13 is a function, rather than a simple mathematical 14 calculation, then it has zero or more arguments. If 15 the element is the IF function 20, then it has three 16 arguments: a condition, a TRUE part and a FALSE 17 part. The program determines if the condition is 18 true or false 22, and then analyses the cell or 19 cells that the true or false part respectively 20 refers to 24. Cells with a value that is not blank 21 or zero are added to the list of direct precedents.

22 The cells referred to by the condition and the other 23 argument are not added to the list of direct 24 precedents.

26 If the element is the HLOOKUP or VLOOKUP function 27 26, then the program determines the range of cells 28 that are contained in the lookup table 28. The 29 column number 30 and the row number 32 are 30 determined, and the single relevant cell is 31 displayed 34. This cell is then added to the list of

1 direct precedents, unless its value is blank or zero 2 36. 4 If the element is the INDIRECT function 38, then the 5 program will determine a second cell reference from 6 a first cell 40 (that reference is the value of the 7 first cell) and from the cell or block of cells 8 referred to by the second cell reference, determine 9 the value 42. Cells with a value that is blank or 10 zero are not added to the list of direct precedents.

12 In use, cells contain either a value which has been 13 input by a user or a value which has been calculated 14 in some manner. The user chooses a cell of 15 interest. If the cell contains a calculated value, 16 the calculation rule for the cell of interest is 17 parsed into elements. Each element is then analysed 18 and its nature determined. If the element is a 19 reference to a particular cell or block of cells, 20 then each cell with relevant information is added to 21 a list of direct precedents for the cell of 22 interest. Any cells with a value that is blank or 23 has a value of zero is not added to the list of 24 precedents. The program then moves on to the next 25 element of the calculation, until such time as all 26 elements in the calculation have been analyzed.

28 If the element is a function, rather than a simple 29 mathematical calculation, then each separate 30 function involves a different series of steps, in 31 order to determine the list of direct precedents for 32 the cell of interest.

1 Such functions may include the IF function, the 2 VLOOKUP function, the HLOOKUP function, and the 3 INDIRECT function.

5 If the element is the IF function then it has three 6 arguments: a condition, a TRUE part and a FALSE 7 part. For example, the function could be "IF (A1=3, 8 B1, C1) n which means "if the value of Al is 3 then 9 this function returns the value in B1, otherwise it 10 returns the value in C1". The program determines 11 whether the condition is true or false, and then 12 takes the appropriate argument and analyses the cell 13 or cells that it refers to. Cells with a relevant 14 value are added to the list of direct precedents, 15 and cells with blank or zero values are ignored.

17 If the element is the HLOOKUP or VLOOKUP function, 18 then it has four arguments: a value to lookup in the 19 first row or column of a table, a reference to the 20 table, the column or row to use and possibly a value 21 saying whether to find an exact match when doing the 22 lookup. For example, the function could be "HLOOKUP 23 ("John", A1:C5, D1, FALSE). This will look for the 24 text "John" in the top row of the table of cells 25 A1:C5. If a match is found, this will give the 26 number of the column to use. The function then 27 evaluates D1 to give the row number to use, and 28 picks the single element out of A1:C5 that is 29 specified by the row and column number it has just 30 worked out. The final argument, FALSE, is to specify 31 that the function should look for an exact match in 32 the top row of the table. If this argument is TRUE,

1 then the function will find the nearest match (in 2 alphabetical or numerical order) and use that 3 column. VLOOKUP works in the same way except that 4 "row" and "column" are reversed in the above 5 explanation. The present invention determines the 6 row and column that the function will use, and then 7 adds the relevant cell from the table to the list of 8 direct precedents. The other cells in the table, and 9 any cells referred to by the other arguments, are 10 not added.

12 If the element is the INDIRECT function, then it has 13 two arguments. This function is unusual in that it 14 does not return a value that is specified in one of 15 its arguments. Instead, what it does is look in the 16 cell specified in the first argument and get the 17 value of that cell. It then uses that value as a 18 reference to a second cell or block of cells, and it 19 returns the value in that cell or block of cells.

20 For example, if Al contains the text "C5" and cell 21 C5 contains the value 100, then INDIRECT (Al) gives 22 the value 100. The second argument (which is 23 optional) specifies what type of address is 24 contained in the cell mentioned in the first 25 argument. The present invention evaluates the first 26 argument and work out what cell it refers to. It 27 then looks in that cell and uses its value to 28 determine what the cell or block of cells is that is 29 really being referred to. It then analyses that 30 block of cells ignoring cells with values of blanks 31 or zeros.Once it has finished analysing the cell or 32 block of cells, or the specialist function and its

1 arguments, the invention moves on to the next 2 element. If there is no next element then it 3 displays the list of direct precedents that it has 4 gathered.

6 The invention offers significant advantages in the 7 ease of navigating complex spreadsheets and avoids 8 redundant data display. The history of calculations 9 and equations calculated in a spreadsheet is 10 accessible with speed and accuracy.

12 It will be appreciated that improvements and 13 modifications may be incorporated without departing 14 from the scope of the invention as defined herein.

Claims

1 Claims

3 1. A computer-implemented method of data processing 4 involving the steps of: 5 (i) a user selecting a cell to analyse; 6 (ii) parsing the cell's calculation rule into a 7 plurality of separate elements; 8 (iii) analysing each element and determining 9 whether the element is a reference, a 10 mathematical calculation or a function; 11 (iv) determining a precedent cell or precedent 12 cells that are referenced, directly or 13 indirectly, by each element determined in 14 step (ii); 15 (v) determining a precedent cell or precedent 16 cells that are a result of an element which 17 is a function; 18 (vi) displaying the precedent cell or cells from 19 steps (iv) and (v), except those which do 20 not directly contribute to the value of the 21 cell chosen by the user, including those 22 which have a blank or zero value.

24

2. A method as claimed in claim 1 wherein the step 25 of displaying the precedent cell or cells further 26 comprises organizing the precedent cell or cells 27 in a tree structure to enable a hierarchical 28 display of precedent cells.

30

3. A computer based system for implementing the 31 method of claim 1, comprising data storage means, 32 visual display means, data processing means,

1 including computer software and computer 2 hardware, and a user interface.

4

4.A computer programmed to implement the method of 5 claim 1.

7

5.A computer program product comprising a computer 8 readable medium, having thereon computer program 9 code means, said program when loaded performing 10 the method as claimed in claim 1.

12

6. A computer-implemented method of data processing 13 substantially as herein described with reference 14 to Figure 1 and 2.