Method, apparatus, and computer program product for solving equation system models using spreadsheet software

• US 9,892,108 B1
• Filed: 01/22/2016
• Issued: 02/13/2018
• Est. Priority Date: 01/03/2015
• Status: Active Grant

• Current Assignee: Chahid Kamel Ghaddar
• Original Assignee: Chahid Kamel Ghaddar
• Inventors: Ghaddar, Chahid Kamel
• Primary Examiner(s): Baderman, Scott T
• Assistant Examiner(s): Lee, Sookil J
• Application Number:
  US15/003,848
• Time in Patent Office: 753 Days
• CPC Class Codes: G06F 17/11 for solving equations , e.g. nonlinear equations, general mathematical optimization problems optimization specially adapted for a specific administrative, business or logistic context G06Q10/04
• CPC Class Codes: G06F 40/166 Editing, e.g. inserting or deleting
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

The present disclosure provides a method, an apparatus, and a computer program product to interact with a spreadsheet application for computing the value of a formula that depends explicitly or implicitly on one or more cells designated as variables, based on provided values for the variables, and without modifying any data of the spreadsheet application. In one aspect, the present disclosure provides a method for computing numerical solution of an equation system modelled by one or more variables, parameters, and dependent formulas in a spreadsheet application, using a spreadsheet function of the spreadsheet application.

Claims

1. A method for computing a numerical solution to an equation system using a spreadsheet software application, the method comprising:

A) displaying and operating a tabular datasheet by executing the spreadsheet software application on a computer device, the tabular datasheet having a plurality of cells each being designated with a column identifier and a row identifier, the cells being configured to receive input and display output;

B) receiving, from a first group of the cells, one or more formula expressions representing the equation system and, from a second group of the cells, one or more variables of the formula expressions;

C) receiving, from a third group of the cells, a solver function of the spreadsheet software application having one or more first input arguments, the first input arguments at least referencing the formula expressions in the first group of the cells, the variables in the second group of the cells and, any parameters of the equation system;

D) computing the solver function to obtain the solution and displaying the solution in the third group of the cells;

wherein computing the solver function comprises;

D1) constructing, for each of the formula expressions, an evaluable tree-structured graph of relational nodes containing an equivalent sequence of evaluable sub-expressions of said formula expressions;

D2) evaluating, by a graph evaluator, the relational nodes in an order of their interdependence to obtain values of the relational nodes based on supplied values of the variables of the formula expressions, and aggregating the values of the relational nodes to obtain the values of the formula expressions in accordance with the supplied values of the variables of the formula expressions;

D3) using an underlining algorithm of the solver function to compute the solution in accordance with the formula expressions in the first group of the cells, the variables in the second group of the cells and, the parameters;

wherein the underlining algorithm of the solver function utilizes the graph evaluator to evaluate the formula expressions for values of the variables thereby computing the solution.

2. The method of claim 1, wherein the spreadsheet software application comprises a native spreadsheet software application.

3. The method of claim 1, wherein the spreadsheet software application comprises a web-based spreadsheet software application.

4. The method of claim 1, wherein said one or more formula expressions depend implicitly on said one or more variables via interdependence on additional nested formulas in another group of the cells.

5. The method of claim 1, wherein the solver function is a pure function.

6. The method of claim 1, wherein computing the solver function computes and displays the solution value of an integral of said one or more formula expressions.

7. The method of claim 1, wherein computing the solver function computes and displays the solution value of a multiple integral of said one or more formula expressions comprising recursively computing nested integrals of said one or more formula expressions.

8. The method of claim 1, wherein computing the solver function computes and displays the solution value of a derivative of said one or more formula expressions.

9. The method of claim 1, wherein computing the solver function computes and displays the solution value of a mixed partial derivative of said one or more formula expressions comprising recursively computing nested derivatives of said one or more formula expressions.

10. The method of claim 1, wherein the equation system comprises differential algebraic equations.

11. The method of claim 1, wherein computing the solver function computes and displays the solution to an ordinary differential-algebraic equations system.

12. The method of claim 1, wherein computing the solver function computes and displays the solution to an initial value ordinary differential-algebraic equations system comprising receiving initial values from said one or more variables in the second group of the cells.

13. The method of claim 1, wherein computing the solver function computes and displays the solution to a multipoint boundary value ordinary differential-algebraic equations system comprising:

receiving boundary points values and corresponding boundary conditions formula expressions for the multipoint boundary value system from an additional group of the cells; and

utilizing the graph evaluator to evaluate said boundary conditions formula expressions in accordance with the underlining algorithm.

14. The method of claim 11, wherein displaying the values of the solution comprises displaying values of the solution in a tabular array of the datasheet;

wherein values of the equations system'"'"'s independent variable are listed in a first column of the tabular array; and

wherein corresponding values of the equations system'"'"'s dependent variables are listed in subsequent columns of the tabular array; and

wherein corresponding labels for the equations system'"'"'s variables are displayed for each of the columns in the first row of the tabular array; and

wherein corresponding error bounds on the computed values for the equations system'"'"'s dependent variables are listed in the last row of the tabular array.

15. The method of claim 1, wherein computing the solver function computes and displays the solution to a partial differential-algebraic equations system.

16. The method of claim 1, wherein computing the solver function comprises, computing and displaying the solution to a partial differential-algebraic equations system;

receiving initial values from said one or more variables in the second group of the cells; and

receiving boundary points values and corresponding boundary conditions formula expressions for the partial differential-algebraic equation system from an additional group of the cells; and

utilizing the graph evaluator to evaluate said boundary conditions formula expressions in accordance with the underlining algorithm.

17. The method of claim 15, wherein displaying the values of the solution comprises displaying values of the solution in a tabular array of the cells;

wherein values of the equations system'"'"'s first independent variable are listed in ascending order in a first column of the tabular array;

wherein values of the equations system'"'"'s second independent variable are listed in blocks of ascending order in a first row of the tabular array; and

wherein values of the equations system'"'"'s dependent variables are listed in multiple columns, such that values in each dependent variable column correspond to the values of the first independent variable in the first column and the values of the second independent variable in the first row; and

wherein corresponding label for the equations system'"'"'s second independent variable is displayed in the first row of the tabular array; and

wherein corresponding labels for each of the remaining equations system'"'"'s variables are displayed in a second row of the tabular array.

18. The method of claim 1, wherein computing the solver function computes and displays the solution to a system of algebraic equations and zero, or more inequalities.

19. The method of claim 1, wherein the solver function receives a selection and control settings for the underlining algorithm from optional input arguments.

20. The method of claim 1, wherein the solver function receives from an additional group of the cells, formula expressions representing analytical Jacobian of said one or more formula expressions with respect to said one or more variables.

21. A non-transitory computer-readable storage medium storing instructions for a computer program product in combination with a spreadsheet software program for computing a numerical solution to an equation system, wherein execution of the instructions by a computer device, causes the computer device to perform a method comprising:

A) displaying and operating a tabular datasheet, the tabular datasheet having a plurality of cells each being designated with a column identifier and a row identifier, the cells being configured to receive input and display output;

B) receiving, from a first group of the cells, one or more formula expressions representing the equation system and, from a second group of the cells, one or more variables of said formula expressions;

C) receiving, from a third group of the cells, a solver function of the spreadsheet software application having one or more first input arguments, the first input arguments at least referencing the formula expressions in the first group of the cells, the variables in the second group of the cells and, any parameters of the equation system;

D) computing the solver function to obtain the solution and displaying the solution in the third group of the cells;

wherein computing the solver function comprises

D1) constructing, for each of the formula expressions, an evaluable tree-structured graph of relational nodes containing an equivalent sequence of evaluable sub-expressions of said formula expressions;

D2) evaluating, by a graph evaluator, the relational nodes in an order of their interdependence to obtain values of the relational nodes based on supplied values of the variables of the formula expressions, and aggregating the values of the relational nodes to obtain the values of the formula expressions in accordance with the supplied values of the variables of the formula expressions;

D3) using an underlining algorithm of the solver function to compute the solution in accordance with the formula expressions in the first group of the cells, the variables in the second group of the cells and, the parameters;

wherein the underlining algorithm of the solver function utilizes the graph evaluator to evaluate the formula expressions for values of the variables thereby computing the solution.

22. A computer apparatus for computing a numerical solution to an equation system using a spreadsheet software program, the computer apparatus comprising a display monitor and, a hardware processor configured to perform a method comprising:

A) displaying and operating a tabular datasheet by executing the spreadsheet software program on the computer apparatus, the tabular datasheet having a plurality of cells each being designated with a column identifier and a row identifier, the cells being configured to receive input and display output;

B) receiving, from a first group of the cells, one or more formula expressions representing the equation system and, from a second group of the cells, one or more variables of said formula expressions;

C) receiving, from a third group of the cells, a solver function of the spreadsheet software application having one or more first input arguments, the first input arguments at least referencing the formula expressions in the first group of the cells, the variables in the second group of the cells and, any parameters of the equation system;

D) computing the solver function to obtain the solution and displaying the solution in the third group of the cells;

wherein computing the solver function comprises

D1) constructing, for each of the formula expressions, an evaluable tree-structured graph of relational nodes containing an equivalent sequence of evaluable sub-expressions of said formula expressions;

D2) evaluating, by a graph evaluator, the relational nodes in an order of their interdependence to obtain values of the relational nodes based on supplied values of the variables of the formula expressions, and aggregating the values of the relational nodes to obtain the values of the formula expressions in accordance with the supplied values of the variables of the formula expressions;

D3) using an underlining algorithm of the solver function to compute the solution in accordance with the formula expressions in the first group of the cells, the variables in the second group of the cells and, the parameters;

wherein the underlining algorithm of the solver function utilizes the graph evaluator to evaluate the formula expressions for values of the variables thereby computing the solution.