Validation of formulas with external sources

• US 9,292,486 B2
• Filed: 01/08/2014
• Issued: 03/22/2016
• Est. Priority Date: 01/08/2014
• Status: Expired due to Fees

• 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
• Application Number:
  US14/150,251
• Publication Number:
  US 20150193422A1
• Time in Patent Office: 804 Days
• US Class Current: 1/1
• 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 system and/or computer program product 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 computer program product for validating a formula within a spreadsheet, the computer program product comprising a computer readable storage medium having program code embodied therewith, the program code readable and executable by a processor to perform a method comprising:

identifying a formula within a spreadsheet;

identifying a formula type of an identified formula from the spreadsheet;

identifying and locating 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 whether the identified formula from the spreadsheet matches a known accurate formula from the database;

determining whether the identified formula from the spreadsheet is executable; and

in response to determining that the identified formula is not executable and in response to the identified formula from the spreadsheet failing to match the known accurate formula from the database, initiating a predefined action.

2. The computer program product of claim 1, wherein the predefined action comprises:

replacing the identified formula from the spreadsheet with a replacement known accurate formula from the database.

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

identifying 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. A computer system comprising:

a processor, a computer readable memory, and a computer readable storage medium;

first program instructions to identify a formula within a spreadsheet;

second program instructions to identify a formula type of an identified formula from the spreadsheet;

third program instructions to identify and locate 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;

fourth program instructions to determine whether the identified formula from the spreadsheet matches a known accurate formula from the database;

fifth program instructions to, in response to the identified formula from the spreadsheet failing to match the known accurate formula from the database, initiate a predefined action;

sixth program instructions to determine that an initially untyped formula within the spreadsheet depends on a particular formula having a known type;

seventh program instructions to assign the known type of the particular formula to the initially untyped formula; and

eighth program instructions to add the initially untyped formula to the database;

and wherein

said first, second, third, fourth, fifth, sixth, seventh, and eighth program instructions are stored on said computer readable storage medium, and wherein said program instructions are executed by said processor via said computer readable memory.

5. The computer system of claim 4, further comprising:

ninth program instructions to replace the identified formula from the spreadsheet with a replacement known accurate formula from the database; and

wherein

said ninth program instructions are stored on said computer readable storage medium for execution by said processor via said computer readable memory.

6. The computer system of claim 5, further comprising:

tenth program instructions to identify 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; and

wherein

said tenth program instructions are stored on said computer readable storage medium for execution by said processor via said computer readable memory.

7. The computer system of claim 4, further comprising:

ninth program instructions to type an initially untyped formula within the spreadsheet by determining that the initially untyped formula depends on a particular formula having a known type; and

tenth program instructions to assign the known type of the particular formula to the initially untyped formula; and

wherein

said ninth and tenth program instructions are stored on said computer readable storage medium for execution by said processor via said computer readable memory.

8. The computer system of claim 4, further comprising:

ninth program instructions to type an initially untyped formula within the spreadsheet by associating a particular measurement unit with a particular known formula type;

tenth program instructions to determine that the initially untyped formula utilizes the particular measurement unit; and

eleventh program instructions to assign the particular known formula type to the initially untyped formula; and

wherein

said ninth, tenth, and eleventh program instructions are stored on said computer readable storage medium for execution by said processor via said computer readable memory.