Validation of formulas with external sources

• US 9,996,521 B2
• Filed: 08/31/2015
• Issued: 06/12/2018
• Est. Priority Date: 01/08/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cudak, Gary D., Hardee, Christopher J., Rodriguez, Adrian X.
• Primary Examiner(s): Ries, Laurie A
• Application Number:
  US14/840,333
• Publication Number:
  US 20160004684A1
• Time in Patent Office: 1,016 Days
• CPC Class Codes: G06F 11/362 Software debugging
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

A method validates a formula within a spreadsheet. A formula type, of an identified formula from a spreadsheet, is identified. A database of known accurate formulas, whose type matches a formula type of the identified formula within the spreadsheet, is identified and located. A determination is made as to whether the identified formula from the spreadsheet matches a known accurate formula from the database. In response to the identified formula from the spreadsheet failing to match the known accurate formula from the database, a predefined action is initiated.

Claims

1. A method of validating a formula within a spreadsheet, the method comprising:

identifying, by one or more processors, a formula within a spreadsheet;

identifying, by one or more processors, a formula type of an identified formula from the spreadsheet;

identifying and locating, by one or more processors, a database of known accurate formulas whose type matches the formula type of the identified formula within the spreadsheet, wherein each of the known accurate formulas calculates a correct solution to a predefined problem;

determining, by one or more processors, whether the identified formula from the spreadsheet matches a known accurate formula from the database;

in response to the identified formula from the spreadsheet failing to match the known accurate formula from the database, initiating, by one or more processors, a predefined action;

determining, by one or more processors, whether the identified formula from the spreadsheet is executable; and

in response to determining that the identified formula is not executable, initiating the predefined action.

2. The method of claim 1, wherein the predefined action comprises:

replacing, by one or more processors, the identified formula from the spreadsheet with a replacement known accurate formula from the database.

3. The method of claim 2, wherein identifying the replacement known accurate formula comprises:

identifying, by one or more processors, a most common formula whose type matches the formula type of the identified formula within the spreadsheet as the replacement known accurate formula, wherein the most common formula occurs more frequently in the database of known accurate formulas than other formulas of the formula type of the identified formula within the spreadsheet.

4. The method of claim 1, wherein an initially untyped formula within the spreadsheet is typed by:

determining, by one or more processors, that the initially untyped formula depends on a particular formula having a known type; and

assigning, by one or more processors, the known type of the particular formula to the initially untyped formula.

5. The method of claim 4, wherein the predefined action comprises:

issuing, by one or more processors, an alert stating that the initially untyped formula is invalid, wherein an invalid formula is unable to calculate the correct solution to the predefined problem.

6. The method of claim 4, further comprising:

in response to the identified formula from the spreadsheet matching the known accurate formula from the database, generating, by one or more processors, a notice indicating that the initially untyped formula is valid, wherein a valid formula is able to calculate the correct solution to the predefined problem.

7. The method of claim 4, wherein the predefined action comprises:

adding, by one or more processors, the initially untyped formula to the database.

8. The method of claim 4, wherein the predefined action comprises:

removing, by one or more processors, the initially untyped formula from the spreadsheet; and

creating, by one or more processors, a new spreadsheet that contains the initially untyped formula.

9. The method of claim 1, wherein an initially untyped formula within the spreadsheet is typed by:

associating, by one or more processors, a particular measurement unit with a particular known formula type;

determining, by one or more processors, that the initially untyped formula utilizes the particular measurement unit; and

assigning, by one or more processors, the particular known formula type to the initially untyped formula.

10. The method of claim 1, wherein identifying the database of known accurate formulas whose type matches the formula type of the identified formula from the spreadsheet comprises:

matching, by one or more processors, key words within the identified formula from the spreadsheet with metatags used to describe the database of known accurate formulas.

11. The method of claim 1, further comprising:

identifying, by one or more processors, a source of the known accurate formulas in the database;

weighting, by one or more processors, each of the known accurate formulas based on their respective sources.

12. A method of validating a formula within a spreadsheet, the method comprising:

identifying, by one or more processors, a formula within a spreadsheet;

identifying, by one or more processors, a formula type of an identified formula from the spreadsheet;

identifying and locating, by one or more processors, a database of known accurate formulas whose type matches the formula type of the identified formula within the spreadsheet, wherein each of the known accurate formulas calculates a correct solution to a predefined problem;

determining, by one or more processors, whether the identified formula from the spreadsheet matches a known accurate formula from the database;

in response to the identified formula from the spreadsheet failing to match the known accurate formula from the database, initiating, by one or more processors, a predefined action;

identifying, by one or more processors, a source of the known accurate formulas in the database; and

weighting, by one or more processors, each of the known accurate formulas based on their respective sources.

13. A method of validating a formula within a spreadsheet, the method comprising:

identifying, by one or more processors, a formula within a spreadsheet;

identifying, by one or more processors, a formula type of an identified formula from the spreadsheet;

identifying and locating, by one or more processors, a database of known accurate formulas whose type matches the formula type of the identified formula within the spreadsheet, wherein each of the known accurate formulas calculates a correct solution to a predefined problem;

determining, by one or more processors, whether the identified formula from the spreadsheet matches a known accurate formula from the database;

in response to the identified formula from the spreadsheet failing to match the known accurate formula from the database, initiating, by one or more processors, a predefined action;

determining, by one or more processors, that an initially untyped formula depends on a particular formula having a known type;

assigning, by one or more processors, the known type of the particular formula to the initially untyped formula; and

adding, by one or more processors, the initially untyped formula to the database.