US-20260127362-A1 - METHOD AND SYSTEM FOR AUTOMATICALLY CONVERTING FORMULAS USING CELL-SCOPED NOMENCLATURE

US20260127362A1US 20260127362 A1US20260127362 A1US 20260127362A1US-20260127362-A1

Abstract

Methods and systems are provided for giving a name to an expression within a cell formula so that the said expression can be represented by the given name in all subsequent references within the cell formula in a spreadsheet application. In the edit mode, a user may enter any expression within a formula by its given name after defining it once anywhere in that formula. In the view mode, the cell formula may display all named expressions by their given names, with the definitions of the said names displayed as sub-formulas along with the computed value of the expressions. Features may be implemented as an add-in, a macro, a function, a direct modification of the spreadsheet application, or any combination thereof. Implementations may be used with electronic spreadsheet applications generally, including programs such as Microsoft Excel, Google Sheets, Lotus 123, but not limited to such.

Inventors

Sandeep Dayal

Assignees

Dayal Family LLC

Dates

Publication Date: 20260507
Application Date: 20260105

Claims (20)

1 . A method for automatically converting a first formula to another formula for use in a cell of a data processing application, the data processing application comprising an electronic spreadsheet program or database program, the method comprising: searching the first formula to identify a first common expression, the first common expression having a plurality of instances in the first formula; in response to identifying the first common expression, automatically generating a first nomenclature object comprising a first name, a first unique symbol or unique combination of symbols, and the first common expression, wherein the first unique symbol or unique combination of symbols creates a first association between the first common expression and the first name so that the first name is mathematically equivalent to the first common expression; and automatically generating a new formula, the new formula including the first nomenclature object, and utilizing the first name in place of at least one instance of the first common expression; wherein, when the new formula is entered and evaluated in the cell of the data processing application, the first association is utilized to evaluate the first name in the new formula, and the first association is not available to evaluate the first name in any other cell of the data processing application.
2 . The method of claim 1 , wherein automatically generating the new formula comprises replacing every instance of the first common expression with the first name, except for within the first nomenclature object.
3 . The method of claim 1 , further comprising: searching the first formula to identify a second common expression in the first formula, the second common expression having a plurality of instances in the first formula and being different from the first common expression; in response to identifying the second common expression, automatically generating a second nomenclature object comprising a second name, the first unique symbol or unique combination of symbols, and the second common expression, wherein the second name is different from the first name, the first unique symbol or unique combination of symbols further creates a second association between the second common expression and the second name so that the second name is mathematically equivalent to the second common expression; wherein the new formula includes the second nomenclature object and utilizes the second name in place of at least one instance of the second common expression; and wherein, when the new formula is evaluated in the cell, the association between the second name and the second common expression is further utilized to evaluate the second name in the new formula, and the second association between the second name and the second common expression is not available to evaluate the second name in any other cell of the data processing application.
4 . The method of claim 3 , wherein automatically generating the new formula further comprises replacing every instance of the second common expression with the second name, except for within the second nomenclature object.
5 . The method of claim 3 , wherein the second common expression comprises the first name.
6 . The method of claim 1 , further comprising searching the first formula to identify the first common expression in the cell in response to detecting that a user has finished entering or editing the first formula in the cell.
7 . The method of claim 1 , further comprising automatically replacing the first formula in the cell with the new formula.
8 . A non-transitory computer-readable media comprising program code executable by at least a processor to cause the at least a processor to perform steps comprising: searching a first formula to identify a first common expression, the first common expression having a plurality of instances in the first formula, the first formula being a formula for use in a cell of a data processing application comprising an electronic spreadsheet program or database program; in response to identifying the first common expression, automatically generating a first nomenclature object comprising a first name, a first unique symbol or unique combination of symbols, and the first common expression, wherein the first unique symbol or unique combination of symbols creates a first association between the first common expression and the first name so that the first name is mathematically equivalent to the first common expression; and automatically generating a new formula, the new formula including the first nomenclature object, and utilizing the first name in place of at least one instance of the first common expression; wherein, when the new formula is entered and evaluated in the cell of the data processing application, the first association is utilized to evaluate the first name in the new formula, and the first association is not available to evaluate the first name in any other cell of the data processing application.
9 . The non-transitory computer-readable media of claim 8 , wherein automatically generating the new formula comprises replacing every instance of the first common expression with the first name, except for within the first nomenclature object.
10 . The non-transitory computer-readable media of claim 8 , wherein the program code further causes the at least a processor to perform steps comprising: searching the first formula to identify a second common expression in the first formula, the second common expression having a plurality of instances in the first formula and being different from the first common expression; in response to identifying the second common expression, automatically generating a second nomenclature object comprising a second name, the first unique symbol or unique combination of symbols, and the second common expression, wherein the second name is different from the first name, the first unique symbol or unique combination of symbols further creates a second association between the second common expression and the second name so that the second name is mathematically equivalent to the second common expression; wherein the new formula includes the second nomenclature object and utilizes the second name in place of at least one instance of the second common expression; and wherein, when the new formula is evaluated in the cell, the association between the second name and the second common expression is further utilized to evaluate the second name in the new formula, and the second association between the second name and the second common expression is not available to evaluate the second name in any other cell of the data processing application.
11 . The non-transitory computer-readable media of claim 10 , wherein automatically generating the new formula further comprises replacing every instance of the second common expression with the second name, except for within the second nomenclature object.
12 . The non-transitory computer-readable media of claim 10 , wherein the second common expression comprises the first name.
13 . The non-transitory computer-readable media of claim 8 , wherein the program code further causes the at least a processor to perform steps comprising searching the first formula to identify the first common expression in the cell in response to detecting that a user has finished entering or editing the first formula in the cell.
14 . The non-transitory computer-readable media of claim 8 , wherein the program code further causes the at least a processor to perform steps comprising automatically replacing the first formula in the cell with the new formula.
15 . A computing device comprising: at least a processor; and memory coupled to the at least a processor, the memory storing program code executable by the at least a processor to perform steps comprising: searching a first formula to identify a first common expression, the first common expression having a plurality of instances in the first formula, the first formula being a formula for use in a cell of a data processing application comprising an electronic spreadsheet program or database program; in response to identifying the first common expression, automatically generating a first nomenclature object comprising a first name, a first unique symbol or unique combination of symbols, and the first common expression, wherein the first unique symbol or unique combination of symbols creates a first association between the first common expression and the first name so that the first name is mathematically equivalent to the first common expression; and automatically generating a new formula, the new formula including the first nomenclature object, and utilizing the first name in place of at least one instance of the first common expression; wherein, when the new formula is entered and evaluated in the cell of the data processing application, the first association is utilized to evaluate the first name in the new formula, and the first association is not available to evaluate the first name in any other cell of the data processing application.
16 . The computing device of claim 15 , wherein automatically generating the new formula comprises replacing every instance of the first common expression with the first name, except for within the first nomenclature object.
17 . The computing device of claim 15 , wherein the program code further causes the at least a processor to perform steps comprising: searching the first formula to identify a second common expression in the first formula, the second common expression having a plurality of instances in the first formula and being different from the first common expression; in response to identifying the second common expression, automatically generating a second nomenclature object comprising a second name, the first unique symbol or unique combination of symbols, and the second common expression, wherein the second name is different from the first name, the first unique symbol or unique combination of symbols further creates a second association between the second common expression and the second name so that the second name is mathematically equivalent to the second common expression; wherein the new formula includes the second nomenclature object and utilizes the second name in place of at least one instance of the second common expression; and wherein, when the new formula is evaluated in the cell, the association between the second name and the second common expression is further utilized to evaluate the second name in the new formula, and the second association between the second name and the second common expression is not available to evaluate the second name in any other cell of the data processing application.
18 . The computing device of claim 17 , wherein automatically generating the new formula further comprises replacing every instance of the second common expression with the second name, except for within the second nomenclature object.
19 . The computing device of claim 17 , wherein the second common expression comprises the first name.
20 . The computing device of claim 15 , wherein the program code further causes the at least a processor to perform steps comprising searching the first formula to identify the first common expression in the cell in response to detecting that a user has finished entering or editing the first formula in the cell.

Description

CROSS-REFERENCES TO RELATED APPLICATIONS This application is a continuation of U.S. non-provisional patent application Ser. No. 18/734,406, filed Jun. 5, 2024, which is a continuation of U.S. non-provisional application Ser. No. 16/599,895, filed Oct. 11, 2019, now U.S. Pat. No. 12,039,258, which claims priority to and the benefit of U.S. provisional patent application No. 62/745,760, filed Oct. 15, 2018, entitled “Method and System for Dynamic Naming of Component Expressions Within a Formula in a Cell in a Spreadsheet Application,” the contents of all of which are hereby incorporated by reference in their entireties. A portion of the disclosure of this patent document contains material that is subject to copyright. The copyright owner has no objection to the authorized facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever. STATEMENT REGARDING FEDERALLY SPONSORED RESEARCH OR DEVELOPMENT Not Applicable REFERENCE TO SEQUENCE LISTING OR COMPUTER PROGRAM LISTING APPENDIX Not Applicable BACKGROUND OF THE INVENTION The present disclosure relates generally to a METHOD AND SYSTEM FOR DYNAMIC NAMING OF COMPONENT EXPRESSIONS WITHIN A FORMULA IN A CELL IN A SPREADSHEET APPLICATION. Specifically, the present disclosure relates to apparatuses, systems, and methods for providing dynamic naming of component expressions within a formula in a cell in a spreadsheet application. Consultants and analysts are often involved in developing complicated spreadsheet models for their clients. These models involve complex logic, incorporating many cell inputs and embedded functions. Spreadsheet models are often shared amongst different users, many of whom are less skilled in the use of spreadsheet tools. Hence, there is a need to design spreadsheet logic in such a manner as to be understandable by users with varying skill levels. When the logic becomes complex, formulas can become so long that the entire formula may not fit in a single formula bar making it difficult for the spreadsheet developer and user to see and understand the full logic easily. This makes the development task of the analyst prone to errors. Analysts may simplify the logic by breaking up a formula into several intermediate calculations in a series of distinct cells. However, such steps make the entire model bulkier and more difficult to digest as the user must track and understand the interrelationship of all these distinct cells. Alternatively, the analyst can keep the number of cells to a minimum by writing longer formulas, but the proofing of the logic can be onerous, leading to quality control issues. Another way spreadsheet developers try to make their formulas easier to understand is by creating and naming cells containing a component of the formula or expression. However, this does not solve the model bulk and spread problem described above and requires the developer to name many individual cells. This creates additional work around managing cell names and the ranges they correspond to increasing complexity in a whole new way. Further, the names assigned to the cells are global, whereby, they refer to the same cell regardless of where in the spreadsheet or workbook they are used. When these formulas are copied to new rows or columns, the names act as fixed references to the cell rather than moving relationally to other cells as may be the more appropriate functionality desired in model development. Accordingly, there is a need in the art for a method and system for naming expressions within a cell formula in simplified manner within the formula itself so that the name can be immediately used in other parts of that formula where that same expression is used again and in place of it. There is further a need for such a given name to refer to the assigned expression in a relational manner when such a formula is copied to cells in other rows or columns in the spreadsheet. The name of the expression need not be not globally fixed in the spreadsheet and instead can have meaning only in the context of the cell that it resides in. Thus, the same name can refer to different expressions in different cells and can be copied to different rows and cells with corresponding relational assignment of expressions. Finally, there is a need to display the formula and named expressions within it in a manner that is easy for the developer and the user to see and understand. BRIEF SUMMARY OF THE INVENTION Implementations consistent with the present disclosure may solve problems associated with traditional devices by providing apparatuses, systems, and methods for naming component expressions within a formula in a cell in a spreadsheet application. The present disclosure generally relates to spreadsheet application systems used commonly in desktop computers or mobile devices, which may be available as device resident application