Download Method and system for constructing a formula in a spreadsheet

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.