Error correction mechanisms in spreadsheet packages

• US 6,986,099 B2
• Filed: 06/28/2001
• Issued: 01/10/2006
• Est. Priority Date: 07/07/2000
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Todd, Stephen James
• Primary Examiner(s): Hong, Stephen
• Assistant Examiner(s): Ludwig, Matthew
• Application Number:
  US09/894,746
• Publication Number:
  US 20020004801A1
• Time in Patent Office: 1,657 Days
• Field of Search: 715/504, 715/503, 715/508, 715/509, 705/30
• US Class Current: 715/212
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A spreadsheet package allows a base formula to be copied to at least one adjacent cell to form a referencing array. The package maintains a list of bound arrays, at least one cell of a bound array being referred to from a referencing array formula. If a formula within a referencing array cell includes a reference to a cell outside a bound array, the package indicates an error in the formula.

Claims

1. A computer-implemented spreadsheet package comprising computer program code stored on a computer readable medium for, when executed on a computing device, processing spreadsheets comprising:

means for enabling copying of a base formula to at least one adjacent cell of said spreadsheet to form a referencing array;

means for maintaining a list of bound arrays, at least one cell of a bound array being referred to from a referencing array formula;

means, responsive to a formula within a referencing array cell including a reference to a cell outside a bound array, for indicating an error in said formula; and

means for highlighting said bound arrays.

2. A spreadsheet package as claimed in claim 1, in which said list of said bound arrays comprises at least one bound array name and an associated range defining a plurality of cells comprising said bound array.

3. A spreadsheet package as claimed in claim 2, in which references from referencing array cell formulae into a bound array are defined relative to a bound array range and said indicating means is responsive to a relative bound array reference comprising a reference to a cell outside a bound array, for indicating an error in said formula.

4. A spreadsheet package as claimed in claim 3, further comprising:

means, responsive to a user selection of a range for new bound array, for checking the range of said new bound array against said list of bound arrays to determine if said new bound array is allowable.

5. A spreadsheet package as claimed in claim 4, further comprising:

means for adding the new bound array to said list of bound arrays if the range of the new bound array does not overlap with the list of bound arrays;

means for searching the spreadsheet to identify any standard references into the new bound array; and

means, responsive to identifying such a reference, for converting any identified references into references relative to the new bound array.

6. A spreadsheet package as claimed in claim 5, further comprising:

means for rejecting the addition of the new bound array to said list of bound arrays if the range of the new bound array overlaps with the list of bound arrays.

7. A spreadsheet package as claimed in claim 3, in which said list of bound arrays is adapted to include a first bound array having an associated first range included within a second range associated with a second bound array and in which references from referencing arrays into said first range are held relative to second bound array.

8. A spreadsheet package as claimed in claim 2, in which references from referencing array cell formulae into a bound array are maintained in a standard spreadsheet manner and said indicating means is responsive to any referencing array cell formula comprising a reference to a cell outside a bound array, for indicating an error in said formula.

9. A spreadsheet package as claimed in claim 2, in which said list of bound arrays is adapted to include a first bound array having an associated first range included within a second range associated with a second bound array, said spreadsheet package including means for interpreting references from a referencing array within said first range as references to said second bound array.

10. A spreadsheet package as claimed in claim 1, comprising means for defining bound arrays as named ranges of a spreadsheet.

11. A computer-implemented spreadsheet package comprising computer program code stored on a computer readable medium for, when executed on a computing device, processing spreadsheets comprising:

means for enabling copying of a base formula to at least one adjacent cell of said spreadsheet to form a referencing array;

means for maintaining a list of bound arrays, at least one cell of a bound array being referred to from a referencing array formula, wherein said list of said bound arrays comprises at least one bound array name and an associated range defining a plurality of cells comprising said bound array;

means, responsive to a formula within a referencing array cell including a reference to a cell outside a bound array, for indicating an error in said formula, wherein references from referencing array cell formulae into a bound array are defined relative to a bound array range and said indicating means is responsive to a relative bound array reference comprising a reference to a cell outside a bound array, for indicating an error in said formula; and

means for calculating cell formulae, said means for calculating comprising means for identifying a reference to a bound array in a formula, means for determining if said reference is contained within the range of the plurality of cells associated with said identified bound array, and means, responsive to said reference being contained within said range for returning a value for said reference and responsive to said reference being outside said range for indicating an error in said reference.

12. A spreadsheet package as claimed in claim 11, further comprising means responsive to an indication of an error in said reference, for invoking error prompting, reporting and correction mechanisms.

13. A spreadsheet package as claimed in claim 12, in which said error prompting mechanism comprises means for generating a pop up window.

14. A spreadsheet package as claimed in claim 13, in which said error reporting mechanism comprises means for displaying an error result for said referencing array cell formula.

15. A spreadsheet package as claimed in claim 12, comprising means, responsive to a reference to a cell outside a bound array within a referencing array cell formula, for returning a value for said reference and an error indication.

16. A spreadsheet package as claimed in claim 15, in which said error reporting mechanism comprises means for displaying a result for said referencing array cell formula according to said returned value and an indication that said result is an error.

17. A computer-implemented spreadsheet package comprising computer program code stored on a computer readable medium for, when executed on a computing device, processing spreadsheets comprising:

means for enabling copying of a base formula to at least one adjacent cell of said spreadsheet to form a referencing array;

means for maintaining a list of bound arrays, at least one cell of a bound array being referred to from a referencing array formula, wherein said referencing array cell formulae include a plurality of references to respective bound array cells; and

means, responsive to a formula within a referencing array cell including a reference to a cell outside a bound array, for indicating an error in said formula.

18. A spreadsheet package as claimed in claim 17, further comprising:

means for detecting referencing arrays, each referencing array comprising a plurality of adjacent cells including respective copies of the same base formula that reference at least one cell of a bound array.

19. A spreadsheet package as claimed in claim 18, further comprising means for highlighting cells of referencing arrays correctly referencing a bound array differently from cells of referencing arrays including any incorrect references to bound arrays.

20. A spreadsheet package as claimed in claim 18, comprising means, responsive to a copy operation of a referencing array through user interaction, for determining first references in the formulae of said referencing array referring into a bound array and second references in the formulae of the copied referencing array referring outside the bound array and wherein said highlighting means is adapted to highlight cells comprising formulae including only first references differently from formulae including second references.

21. A spreadsheet package as claimed in claim 18, comprising means, responsive to user interaction extending a referencing array, to automatically extend any bound arrays referenced by said referencing array accordingly.

22. A spreadsheet package as claimed in claim 21, comprising means for setting empty cells of said extended bound arrays to indicate an unset condition.

23. A spreadsheet package as claimed in claim 21, further comprising means for automatically moving populated cells of the spreadsheet in the direction of extension of a bound array into available empty cells of said spreadsheet.

24. A spreadsheet package as claimed in claim 18, further comprising means for maintaining a list of referencing arrays, each element of the list comprising a range corresponding to the plurality of adjacent cells comprising said referencing array.