Download Method and system for constructing a formula in a spreadsheet
Transcript
US005890174A Ulllted States Patent [19] [11] Patent Number: Khanna et al. [45] [54] METHOD AND SYSTEM FOR Date of Patent: 5,652,889 CONSTRUCTING A FORMULA IN A SPREADSHEET 5,890,174 Mar. 30, 1999 7/1997 Sites ...................................... .. 707/504 Primary Examiner_phu K~ Nguyen Attorney, Agent, or Firm—Jones & Askew, LLP [75] Inventors: Karan Khanna, Clyde Hill; Edward [57] A. Martinez, Redmond, both of Wash. ABSTRACT _ _ _ _ A method and system for assisting a user in constructing a [73] AssigneeZ Microsoft Corporation, Redmond, formula in a cell of a spreadsheet'operating on a computer Wash system. A function dialog box is displayed that lists various functions that may be selected by the user to be entered into the formula. By selecting a button in the function dialog box, _ [21] Appl' NO" 558’820 an insert function dialog box is displayed that lists additional [22] Filed; functions by category. When the user selects a function, a parameter dialog box associated With the selected function is [51] [52] Int. Cl. .................................................... .. G06F 15/00 US. Cl. ............................................................ .. 707/504 displayed Parameters may include Cell addresses as Well as numerical or other data entered directly by the user_ The [58] Field Of Search ................................... .. 395/938, 939, parameter dialog box displays the formula as it is being Nov, 16, 1995 6 395/940, 941, 932; 345/440; 707/503, 504, constructed. After the parameters have been entered, a result 509, 510 dialog box is displayed that alloWs the user to select the _ [56] result cell in Which to place the formula. The user selects the References Clted result cell and the result dialog box is removed from the Us‘ PATENT DOCUMENTS display screen, the formula is'entered in the result cell, and the result of the formula is displayed in the result cell. 5,255,356 5,280,575 10/1993 Michelman et a1. .................. .. 707/504 1/1994 Young et a1. ......................... .. 707/504 48 Claims, 17 Drawing Sheets 30 Help D Name and: spreadshee 32 Click tmfmctlonywwartlouselnymlrlomula D See more 0 Commoncalcdatlons D Type and 01 E Totals numbers Multiplies numbers D Type repetiv D Calculate subtracts numbers D formulas, Create a ch Dlvides numbers D Change a c IE] Imam! Averages numbers D Use charts D Add,chang Other caloulauons Dlslalxsnscomplete llst of func and columr D and Charg‘ge l’lOl ates l1 D size.$%,nu entrie lndex 1 00% - Shrink Helr + 34 36 U.S. Patent Mar. 30, 1999 Sheet 1 0f 17 5,890,174 r 10 K 12 21 / OPERATING SYSTEM / 18 14 / APPLICATION PROGRAM CPU f 20 16 / I/O CONTROLLER MEMORY f KEYBOARD! MOUSE FIGJ 24 / DISPLAY MONITOR 22 U.S. Patent Mar. 30, 1999 Sheet 9 0f 17 5,890,174 END A NO SUGGESTED PARAMETERS TDENTIFYANDI DISPLAY < SUGGESTED PARAMETERS 140 HMSTE ER TSR V! E M SMWEwWmESHRMTEUwRm mncmR S S FIG.9 U.S. Patent Mar. 30, 1999 Sheet 17 0f 17 5,890,174 200 ( sTART ) 210 T / ENTER EASY CALC {232 " f w DISPLAY 221 (215 FUNCTION DIALOG BOX 224 DISPLAY SUGGESTED D'SFTmgT'RTRT PARAMETERS [)IALQG BOX V A =P. 5 N0 SL188; 5% 7 ACCEPT USER ENTRY OF {226 CATEGORY METERS 228 DISPLAY LlST OF YES 237 242 FUNCTIONS 230 R2566? / ‘——-_~ DIALOGBOX 244 '— A855? SELECTION r FFUNCTION DISPLAY DEFAULT I . ' REsuLT CELL 24a , 250 ACCEPT USER ENTRY OF RESULT CELL FIGJI I 5,890,174 1 2 METHOD AND SYSTEM FOR CONSTRUCTING A FORMULA IN A SPREADSHEET syntax required to correctly enter the formula. Thus, formula entry can quickly become very dif?cult for the user, and the probability of improperly entering such formulas is high. Spreadsheet programs are popular among both sophisticated, expert computer users as Well as ordinary, novice users. Ordinary users may include homeoWners Who use spreadsheet programs for maintaining household bud TECHNICAL FIELD The present invention relates generally to the entry of information in spreadsheet computer programs and, more speci?cally, to a system and method for constructing and gets and personal ?nances. Other ordinary users may include small business oWners Who use spreadsheets to perform entering a formula in a cell of a spreadsheet. 10 BACKGROUND OF THE INVENTION these tasks require the use of complicated formulas in the spreadsheet program. Spreadsheet programs are Widely-used application pro grams that run on personal computers. Spreadsheet pro grams are commonly used for budgets, forecasting, and other ?nance-related tasks. In a spreadsheet program, data and formulas are entered in ledger-like forms for analysis, tracking, planning, or evaluations of the impacts of real or tasks such as tracking inventory, costs, sales, etc. Frequently, 15 These ordinary users of computers are usually not Well versed in mathematics or computer syntax related to the formulas that can be entered in the spreadsheet. Therefore, the user may not remember the proper syntax and function identi?ers required for various functions and therefore may proposed changes on data. incorrectly enter the formula or may be forced to consult a lengthy and complicated user’s manual to obtain the correct Spreadsheet programs use roWs and columns of cells for the display of data. Depending on the program, a single spreadsheet can contain anyWhere from thousands to mil syntax. lions of cells. Some spreadsheet programs can also link one tured by Microsoft Corporation of Redmond, Wash., spreadsheet to another spreadsheet that contains related information and can update data in linked spreadsheets automatically. Spreadsheet programs may also include Current spreadsheet programs, such as Excel, manufac 25 assignee of the present invention, require that the user is familiar With the complicated syntax needed to enter for mulas in the spreadsheet. Thus, When the user Wishes to enter a formula in the spreadsheet, the user must manually macro facilities, and some can be used for creating and enter the formula including all proper syntax using the sorting databases. For printed output, spreadsheet programs usually provide graphing capabilities and a variety of for matting options for both printed pages and text, numeric values, and captions and legends in graphs. keyboard Without being aided or prompted by the spread sheet program. When a user manually enters a formula, the probability of errors in the formula are high. Common errors include Each roW and column in a spreadsheet is unique, so each cell is uniquely identi?ed. Typically, each column is iden improperly referencing other cells that are part of the ti?ed by a letter, and each roW is identi?ed by a number. Thus, the cell located at the intersection of column A and calculation, transposing cell addresses (identifying cell A1 as cell 1A), placing multiple operators together, and mis 35 matching parentheses. Also, incorrect spacing as Well as roW 1 is identi?ed as cell A1. Each cell is displayed as a rectangular space that can hold text, numerical data, or a various other typographical errors can result in an improp formula that uses parameters, Which may comprise data in erly entered formula. other cells, to calculate a desired result. To ease Current spreadsheet programs utiliZe a “function Wizard”. The WiZard assists the user With the entry of parameters into computation, spreadsheet programs include built-in func tions that perform standard calculations. an individual function but does not alloW the user to enter may be provided in the spreadsheet, including complex functions into the formula. Thus, the function WiZard, While assisting the user With certain aspects of formula construction, only assists the user after the proper syntax for a function has been manually entered. Consequently, func tion WiZards associated With current spreadsheet programs trigonometric functions to calculate the sine or cosine of a do not free the user from the dif?cult task of remembering Such built-in functions may include simple arithmetic functions such as addition (designated by the + character), subtraction (designated by the—character), and multiplica tion (designated by the * character). Many other functions 45 variable (designated by SIN and COS, respectively), and and entering the proper formula syntax. Thus, function functions to ?nd the natural logarithm and exponential value of a variable (designated by LN and EXP, respectively). Many other functions are provided by spreadsheet programs to perform a Wide variety of calculations and data compari WiZards do not assist the user in the construction of a formula in its entirety. Also, current function WiZards do not perform error correction. Although current spreadsheet programs are able to detect an improperly entered formula and generate and sons. A formula may be constructed Within a cell of the spread display a message to the user, current programs do not detect sheet using one or more of the built-in functions combined 55 the precise error that is present Within the formula and also do not provide a suggestion for the correction of the error. With any other constants or variables. The proper syntax of each formula is entered into the spreadsheet directly by the If the user incorrectly enters a formula, current spreadsheet user through the use of keystrokes on a keyboard. The various functions available in spreadsheet formulas are programs generate a generic error message to the user indicating that the formula has been incorrectly entered. denoted by a particular character or characters. For example, the very simple formula A1+B1 may be entered in the noti?es the user of the type or location of the error. spreadsheet When the user Wants to add the numbers located Therefore, Without knoWing What type of error is present in HoWever, the generic error message neither detects nor in cells A1 and B1. More complicated formulas utiliZing the formula, a user is left With the tedious and frustrating unfamiliar and complex functions require the user to manu ally enter a function identi?er, such as SIN, COS, etc., to use the function. Therefore, it is clear that the use of functions task of poring through a lengthy and complicated formula to in spreadsheet formulas requires that the user memoriZe the 65 detect Where the error exists and editing the formula to correct the error. This increases user frustration and decreases efficiency. 5,890,174 3 4 All of these problems associated With formula entry serve not only to make the use of formulas in spreadsheets time-consuming and inef?cient but also frustrating for the invention Will be described With respect to spreadsheet programs, those skilled in the art Will recogniZe that the present invention may be utiliZed in connection With other user. These problems may force a frustrated user to simply softWare programs in Which mathematical or other types of give up and cease entering formulas in spreadsheet pro grams. By not efficiently and effectively utiliZing all of the bene?ts that spreadsheet formulas provide, the user does not maXimiZe the capabilities of the spreadsheet program. formulas are entered. SUMMARY OF THE INVENTION Turning ?rst to the nomenclature of the speci?cation, the detailed description Which folloWs is represented largely in 10 The present invention provides a method and system that terms of processes and symbolic representations of opera tions by conventional computer components, including a central processing unit (CPU) associated With a general purpose computer system, memory storage devices for the assists a user in the construction of a formula in a cell of a CPU, and connected pixel-oriented display devices. These operations include the manipulation of data bits by the CPU spreadsheet. The present invention displays a function dia log boX that lists various functions that may be selected by resident in one or more of the memory storage devices. Such the user to be entered into the formula. By selecting a button and the maintenance of these bits Within data structures 15 in the function dialog boX, an insert function dialog boX is displayed that lists additional functions by category. When the user selects a function, a parameter dialog boX associated With the selected function is displayed. The present invention displays suggested parameters in the others skilled in the art. For the purposes of this discussion, a process is generally conceived to be a sequence of computer-executed steps parameter dialog boX When predetermined functions are selected. The suggested parameters are cell addresses that are identi?ed and displayed based on the position of cells above the current cell and to the left of the current cell that are ?lled With data. The suggested parameters that are leading to a desired result. These steps generally require 25 physical manipulations of physical quantities. Usually, though not necessarily, these quantities take the form of displayed in the parameter dialog boX can be accepted by the electrical, magnetic, or optical signals capable of being user by selecting a button in the parameter dialog boX, or the stored, transferred, combined, compared, or otherWise user can enter other parameters. Other parameters may include cell addresses as Well as numerical or other data manipulated. It is conventional for those skilled in the art to refer to these signals as bits, values, elements, symbols, characters, terms, numbers, records, ?les or the like. It should be kept in mind, hoWever, that these and similar terms should be associated With appropriate physical quan entered directly by the user. As the parameters are being entered, the parameter dialog boX displays the formula as it is being constructed. After the parameters have been entered, a result dialog boX is dis played that alloWs the user to select the result cell in Which data structures impose a physical organiZation upon the collection of data bits stored Within computer memory and represent speci?c electrical or magnetic elements. These symbolic representations are the means used by those skilled in the art of computer programming and computer construc tion to most effectively convey teachings and discoveries to tities for computer operations, and that these terms are 35 merely conventional labels applied to physical quantities to place the formula. The default result cell is displayed in the result dialog boX as the address of the current cell, Which that eXist Within and during operation of the computer. is the cell that Was highlighted When the user initiated the method of the present invention. The default result cell can computer are often referred to in terms such as adding, It should also be understood that manipulations Within the comparing, moving, etc. Which are often associated With manual operations performed by a human operator. It must be accepted by the user by selecting a button in the function dialog boX, or the user can enter another result cell. When the result cell has been entered, the result dialog boX is removed from the display screen, the formula is entered in the result cell, and the result of the formula is displayed in the result cell. be understood that no involvement of a human operator is necessary or even desirable in the present invention. The operations described herein are machine operations per formed in conjunction With a human operator or user that 45 BRIEF DESCRIPTION OF THE DRAWINGS FIG. 1 is a block diagram of a computer system on Which the present invention is implemented. processes, methods, etc. described herein are not related or FIGS. 2—8 are eXamples of display screens used to facili tate the construction of a formula in a spreadsheet in accordance With the present invention. FIG. 9 is a How chart shoWing the method of identifying and displaying suggested parameters. 55 FIGS. 10A—G are examples of display screens shoWing the identi?cation and display of suggested parameters in FIG. 11 is a How chart shoWing the method of construct ing a formula in a cell in a spreadsheet. pleted by a computer and the method of computation itself. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT The present invention does not involve a method of com putation. Instead, the present invention relates to methods, Turning neXt to the ?gures, the preferred embodiment Will sheet. Although the preferred embodiment of the present limited to any particular computer or apparatus. Rather, various types of general purpose machines may be used With programs constructed in accordance With the teachings described herein. Similarly, it may prove advantageous to construct specialiZed apparatus to perform the method steps described herein by Way of dedicated computer systems With hard-Wired logic or programs stored in nonvolatile memory, such as read only memory. Furthermore, it should be understood that there is a distinction betWeen the methods, steps, or operations com accordance With the present invention. noW be described in detail. The present invention is directed to a method and system for entering formulas in a spread interacts With the computer. The machines used for perform ing the operation of the present invention, as Will be understood, include general purpose digital computers or other similar computing devices. In addition, it should be understood that the programs, processes, steps, or operations for a computer and the 65 processing of electrical or other physical signals to generate desired physical signals and to display results and interac tions.