Method and system for selecting and executing arithmetic functions and the like

The present invention provides a method and system for automating the range selection process involved with arithmetic functions. When the sum function is selected, the system searches the cells above and to the left of the active cell to determine which cells the user is most likely attempting to manipulate. The range of cells selected by the system is marked by a flashing, dashed, bold border line, and is subject to confirmation by the user. To reject the range of cells selected by the system, the user only has to select a new range. The system reduces the steps required to perform an arithmetic function.

TECHNICAL FIELD 
This invention relates generally to the selection and execution of 
arithmetic functions and the like in a computer program and specifically, 
in a preferred embodiment, to a method and system for implementing a sum 
function in an application program. 
BACKGROUND OF THE INVENTION 
A primary use of computers, especially personal computers, is information 
processing. There are a wide variety of application programs available 
that "computerize" information processing tasks that were previously 
performed manually. One such application program is Microsoft Excel, an 
accounting program for personal or business use. In application programs 
such as Microsoft Excel, a worksheet is the primary document used to store 
and manipulate data. One commonly used type of worksheet is a spreadsheet, 
which is a two dimensional rectangular grid of a finite number of columns 
and rows. The intersection of each column and row is a cell, the basic 
unit of a worksheet in which a user stores data. A user may move the 
screen cursor around the spreadsheet to select desired cells as the active 
cell. 
Data contained in the worksheet may be manipulated by functions, which are 
special pre-written formulas that take a value or values, perform an 
operation, and return a value or values. Using functions simplifies and 
shortens formulas in worksheets, especially those that perform lengthy or 
complex calculations. Data contained in the spreadsheet is manipulated in 
part by functions, which are calculation tools that allow a user of a 
computer system to perform decision-making, action-taking, and 
value-returning operations. Functions work with arguments, which are the 
information that a function uses to produce a new value or perform an 
action. Typically, a function and its arguments are in one of two forms: 
FUNCTION (arg1, arg2, . . . , argN), where the arguments may be numbers or 
cell names; or FUNCTION (arg1: argN), where arg1 and argN represent a 
range of cells. An example is the sum function, which returns the sum of 
the arguments specified. 
In the past, a user carried out mathematical operations (addition, 
subtraction, division, etc.) on spreadsheet data in a variety of different 
ways. First, the user could type the numbers or cell names to be 
manipulated along with the operators in a formula format, i.e., A1+A2+A3. 
Second, the user could type in a function name along with a list of 
numbers and/or cell names to be manipulated as arguments, i.e., SUM 
(A1,A2,A3). Third, the user could type in a function name with the range 
of cells to be manipulated as arguments, i.e., SUM(A1: A3). Fourth, the 
user could type in the function name, then use the keyboard arrows or 
mouse to mark a range of cells to be used as arguments. Fifth, the user 
could select a function from a list of functions, then use the keyboard 
arrows or mouse to mark a range of cells to be used as arguments. Finally, 
some systems allow a user to manipulate an entire column or row of data by 
typing a special symbol in the cell below or to the right of the data to 
be manipulated. The user must specify whether the row or the column of 
data is to be manipulated, and how much of the data is to be used in the 
calculation. Although some of these methods are somewhat of an improvement 
over earlier methods, each existing method requires significant user 
effort to select and execute the mathematical operation. 
The sum function constitutes the majority of all mathematical operations 
used in a spreadsheet. When using the sum function, spreadsheet users 
invariably put sum formulas at the bottom of a column of data or to the 
right of a row of data. Nonetheless, existing systems require a user to 
manually specify which cells to add together, by either typing in the 
information or by using keyboard arrows or a mouse to mark the range. It 
would be desirable for an application program to have the capability to 
select and execute an arithmetic function or the like with minimal user 
effort. 
SUMMARY OF THE INVENTION 
It is an object of the present invention to provide an improved method and 
system for selecting and executing an arithmetic function or the like in a 
computer program. 
It is another object of the invention to provide such an improved method 
and system which can efficiently manipulate numeric data in a computer 
program such as an electronic spreadsheet. 
It is another object of the present invention to provide an improved 
function in which a plurality of cells are automatically designated as the 
arguments of the function, subject to acceptance or rejection by the user. 
It is another object of the present invention to visually notify the user 
which plurality of cells were automatically designated as arguments of the 
sum function. These and other objects of the present invention, which will 
become more apparent as the invention is described more fully below, are 
obtained by providing an improved method and system for selecting and 
executing arithmetic functions. In a preferred embodiment, arguments for a 
sum function are automatically selected using preprogrammed criteria once 
the user selects the sum function. The automatically selected argument 
cells are preferably visually designated using a marquis display or the 
like and the user is prompted to confirm or override the automatic 
selection.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT 
The present invention provides a method and system for selecting, and 
executing arithmetic function and the like with reduced user effort. A 
preferred embodiment for automating a summation process includes 
automatically selecting the plurality of contiguous cells that would most 
likely be designated as the range of arguments for a sum function, based 
on the location of the user-selected cell where the results of the 
addition will be displayed. 
The operation of the preferred embodiment described herein will be 
explained with reference to the overview flow diagram of FIG. 1 and the 
screen displays of FIGS. 5 and 6. The process begins in block 10 when a 
user activates the autosum process by selecting the autosum button 30. At 
this time, the active cell 32, where the results of the summation will be 
displayed, is already selected. The column and row of the active cell, 
also known as the coordinates of the active cell are input to the autosum 
process in block 9. In block 11, the process then examines the cells in 
the column above the active cell 34, and in the row to the left of the 
active cell 36, searching for predetermined criteria. Based on this 
examination, the system selects, if possible, a group of contiguous cells 
as proposed arguments for the sum function. The cell examination process 
is described in detail below. 
In block 12 the system determines whether a group of cells was selected in 
block 11. If no cells were selected, the process moves to block 15 to 
await manual selection by the user, indicating which group of cells should 
be summed. As soon an the cells are manually selected, the arithmetic is 
completed and the results are displayed in block 16. If a cell or cells 
are selected in block 11, the process moves to block 13, where the 
selected range of cells is displayed in the worksheet bordered by a 
rotating dashed line 38. The rotating dashed line visually informs the 
user which range of cells the program has selected, allowing the user to 
verify the accuracy of the automatic selection. 
The process then preferably moves to block 14, where the user is given the 
option of overriding the selection. The process waits for the user to 
either accept the selected plurality of cells or reject them by manually 
selecting a plurality of cells. 
FIGS. 2, 2A, 3, 3A, and 4 are detailed flow diagrams of the examination 
process used in the preferred embodiment described herein and referenced 
by FIG. 1, block 11. FIG. 2 illustrates the row examination section of the 
process, while FIG. 2A illustrates how the row score is established. In 
FIG. 2, the coordinates of the active cell, where the results of the 
addition will be displayed, are input to the examination process. The 
process searches the cells in the rows above, but in the same column as, 
the active cell for predetermined criteria. Based on the contents of these 
cells, a row score is determined. Based on the value of the row score, the 
process either makes a cell selection and exits the process, makes a cell 
selection and continues on to the column cell examination part of the 
process, or continues on to the column cell examination section of the 
process without making a cell selection. 
In block 201, the process stores the coordinates of the active cell. These 
coordinates are where the results of the addition will be displayed. The 
active cell becomes the current cell being examined. In block 202, the 
process queries whether the active cell is in the top row. If so, the 
process concludes that a column of data is not being summed and the 
process skips to block 301, otherwise the process continues at block 203. 
In block 203, the process changes the coordinates of the current cell so 
that the new current cell is one row above and in the same column as the 
previous current cell. 
In blocks 204 through 215, the contents of the current cell are examined. 
Based on the contents, the process either stays in a loop, each time 
through examining the cell above the last one, or it skips to block 216, 
where the row scoring computation begins. 
In blocks 204 through 208, the process examines the current cell in search 
of text, a text formula, or a blank cell. The process remembers whether a 
cell containing one of these is encountered before or after a cell 
containing a number or a sum formula. If the process has already 
encountered a number or sum formula cell when it encounters a text cell, 
then the text cell is treated like a column heading and the examination 
process is over. If the process encounters a text cell before any number 
or sum formula cells, it keeps examining new cells in the same column 
until either a number, a sum formula, or the top of the column is 
encountered. 
In block 204, the process queries whether the current cell is empty, 
contains text, or contains a text formula. If so, the process continues at 
block 205, where the process checks to see if a number has been 
encountered, else the process continues at block 209. The process would 
have remembered if a cell had previously been encountered during this 
search that contained a number or a nonsum formula. In block 205, if a 
number has been encountered the examination is over and the process skips 
to block 216, else the process continues at block 206. In block 206, the 
process queries whether a sum formula has been previously encountered. If 
so, the examination is over and the process skips to block 216, else the 
process continues at block 207. In block 207, the process queries whether 
the current cell is still visible to the user. If it is, the process 
remembers that text was found before numbers or sum formulas, and then 
loops back to block 203, where the coordinates of the current cell are 
changed. If the current cell is not still visible to the user, the top of 
the column has been reached, therefore the examination is over and the 
process skips to block 216. 
In blocks 209 through 211, as long as numbers, or non-sum formulas are 
encountered, and they have not been designated a time or date, the process 
remembers that a number has been located and loops back to block 203 to 
change the coordinates of the current cell. If the cell contents are 
designated as a time or date, the examination is considered over because a 
time or date is usually is found at the top of a column. In block 209, the 
process examines the contents of the current cell to determine whether a 
number or a non-sum formula has been located. If so, the process continues 
at block 210, where the process queries whether the cell contents have 
been designated a time or date, else the process skips to block 212. If 
the cell contents have been designated as a time or date, the examination 
is over and the process skips to block 216, else the process remembers 
that a number was found then loops back to block 203. 
In block 212, the process examines the contents of the current cell 
determine whether a sum formula has been located. If so, the process then 
determines in block 213 whether a number or non-sum formula has previously 
been encountered. If so, the process determines whether a sum formula, 
besides the one found in the current cell, has previously been 
encountered. If so, the process loops back to block 203 where the 
coordinates of the current cell are changed. If the current cell contains 
a sum formula and a number or non-sun formula has not previously been 
encountered, the process remembers a sum formula was found and loops back 
to block 203. If the current cell contains a sum formula, and either a 
number or non-sum formula has previously been encountered, and a sum 
formula has not been previously encountered, the examination is over and 
the process continues on at block 216. If the current cell does not 
contain a sum formula, the examination is over and the process skips to 
block 216. 
In blocks 216 through 223, the process determines the row score based on 
what was encountered during the cell examination process. If numbers or 
non-sum formulas were encountered during the examination, the row score is 
given the highest possible value. If a sum formula was encountered, the 
row score is given the second highest possible value. If only sum formulas 
and no numbers, constants, or non-sum formulas were encountered, the row 
score is given the second to lowest possible value. Finally, if text, text 
formulas, or blank cells were encountered before anything else, the row 
score is divided in half. In block 216, the process queries whether 
numbers, constants or non-sum formulas were encountered. If the process 
continues at block 217, where it queries whether sum formula was 
encountered, else the process skips to block 220. In block 217, if no sum 
formula was encountered, the process continues at block 218, where the row 
score is made equal to ten. If a sum formula was encountered, the process 
skips to block 219 and the row score is made equal to eight. With this 
scoring methodology, a plurality of cells containing only numbers receives 
the highest possible score, while a plurality of cells containing numbers 
and sum formulas receives the second highest scores. 
From blocks 218 and 219, the process skips to block 224. If it is 
determined in block 216 that no numbers were encountered, the process 
skips to block 220. In block 220, the process continues on to block 221, 
where the row score is made equal to two. The process continues on to 
block 222, where it determines whether text, text formulas, or blanks were 
encountered before any numbers, constants or formulas. If so, the process 
continues on to block 223, where the row score is divided in half. 
In blocks 224 through 226, the process determines whether the range 
selection should be based on the row examination, or whether a column 
examination should be performed before the range selection is made. In 
block 224, the process queries whether the value of the row score is equal 
to zero. If the row score is not equal to zero, the process continues at 
block 223, where the range selected flag is set to true, and the range is 
computed, else the process continues at block 301. In block 223, the range 
is computed based on the cell immediately below the last cell searched and 
the cell immediately above where the results of the addition are to be 
displayed. After the range is computed, the process continues at block 
224, where the process queries whether the row score is equal to ten. If 
so, the process is terminated, else the process continues at block 301. If 
the row score is equal to ten, there is no need to perform a column search 
and compute the column score, because the column will be summed when the 
row score is greater than or equal to the column score. 
FIG. 3 is a detailed flow diagram of the column cell examination process. 
The coordinates of the cell where the results of the addition will be 
displayed are recalled to initialize the active cell. The process examines 
the columns to the left of, but in the same row as, the active cell for 
predetermined criteria. Based on the contents of these cells, a column 
score is determined in FIG. 3a. In FIG. 4, the process then compares the 
row and column scores and makes a determination, based on preselected 
standards, as to which contiguous cells, if any, will make up the range. 
In block 301, the process makes the cell where the addition results will be 
displayed the current active cell. In block 302, the process queries 
whether the active cell is in the first column. If so, the search cannot 
continue to the left, therefore the process skips to block 332, else the 
process continues at block 303. In block 303, the process changes the 
coordinates of the current cell so that it is now to the left one column, 
but in the same row. 
In blocks 304 through 313, the contents of the current cell are examined 
and remembered. Based on the contents, the process either stays in a loop, 
each time through examining the cell to the left of the last one, or it 
skips to block 314, where the column scoring computation begins. 
In blocks 304 through 308, the process examines the current cell in search 
of text, a text formula, or a blank cell. The process remembers whether a 
cell containing one of these is encountered before or after a cell 
containing a number or a sum formula. If the process has already 
encountered a number or sum formula cell when it encounters a text cell, 
then the text cell is treated like a column heading and the search is 
over. If the process encounters a text cell before any number or sum 
formula cells, the process keeps searching new cells in the same column 
until either a number, a sum formula, or the top of the column is 
encountered. 
In block 304, the process queries whether the current cell is empty 
contains text, or contains a text formula. If so, the process continues at 
block 305, where the process checks to see if a number has been 
encountered, else the process continues at block 309. The process 
remembers if a cell had previously been encountered during this search 
that contained a number or a non-sum formula. In block 305, if a number 
has been encountered, the examination is over and the process skips to 
block 316, else the process continues at block 306. In block 306, the 
process queries whether a sum formula has been previously encountered. If 
so, the examination is over and the process skips to block 316, else the 
process continues at block 307. In block 307, the process queries whether 
the current cell is still visible to the user. If it is, the process 
remembers that text was found before numbers or sum formulas, and then 
loops back to block 303, where the coordinates of the current cell are 
changed. If the current cell is not still visible to the user, the 
beginning of the row has been reached, therefore the examination is over 
and the process skips to block 316. 
In blocks 309 through 311, as long as numbers or non-sum formulas are 
encountered, and they have not been designated a time or date, the process 
remembers that a number has been located and loops back to block 303 to 
change the coordinates of the current cell. In block 309, the process 
examines the contents of the current cell to determine whether a number or 
a non-sum formula has been located. If so, the process continues at block 
310, where queries whether the cell contents have been designated a time 
or date, else the process skips to block 312. If the cell contents have 
been designated as a time or date, the search is over and the process 
skips to block 316, else the process remembers that a number was found in 
block 311 and then loops back to block 303. 
In block 312, the process examines the contents of the current cell to 
determine whether a sum formula has been located. If so, the process then 
determines in block 313 whether a number or non-sum formula has previously 
been encountered. If so, the process determines whether a sum formula, 
besides the one found in the current cell, has previously been 
encountered. If so, the process loops back to block 303 where the 
coordinates of the current cell are changed. If the current cell contains 
a sum formula and a number or non-sum formula has not previously been 
encountered, the process remembers a sum formula was found and loops back 
to block 303. If the current cell contains a sum formula, and if either a 
number or non-sum formula has previously been encountered, and a sum 
formula has not been previously encountered, the examination is over and 
the process continues on at block 316. If the current cell does not 
contain a sum formula, the examination is over and the process skips to 
block 316. 
In block 312, the process examines the contents of the current cell to 
determine whether a sum formula has been located. If so, the process 
continues at block 313, where it remembers that a sum formula was found, 
then the process loops back to block 303 where the coordinates of the 
current cell are changed. If no sum formula is located, the process 
continues at block 314. 
In blocks 316 through 323, the process determines the column score based on 
what was encountered during the column cell examination process. If 
numbers or non-sum formulas were encountered during the examination, the 
column score is given the highest possible value. If a sum formula was 
encountered, the column score is given the second highest possible value. 
If only sum formulas and no numbers or non-sum formulas were encountered, 
the column score is given the second lowest possible value. Finally, if 
text, text formulas, or blank cells were encountered before anything else, 
the column score is divided in half. In block 316, the process queries 
whether numbers or non-sum formulas were encountered. If so, the process 
continues at block 317, where it queries whether a sum formula was 
encountered, else the process skips to block 320. In block 317, if no sum 
formula was encountered, the process continues at block 318, where the 
column score is made equal to ten. If a sum formula was encountered, the 
process skips to block 319 and the column score is made equal to eight. 
With this scoring methodology, a plurality of cells containing only 
numbers receives the highest possible score, while a plurality of cells 
containing numbers and sum formulas receives the second highest scores. 
From blocks 318 and 319, the process skips to block 324. If it is 
determined in block 316 that no numbers were encountered, the process 
skips to block 320. In block 320, the process continues on to block 321, 
where the column score is made equal to two. The process continues on to 
block 322, where it determines whether text, text formulas, or blanks were 
encountered before any numbers or formulas. If so, the process continues 
on to block 323, where the column score is divided in half. In block 315, 
no sum formula was previously encountered, the process continues at block 
316, where the column score is made equal to ten, else the column score is 
made equal to eight, and the process skips to block 322. In block 318, the 
process checks the status of the sum formula flag. If it is true, the 
process continues at block 319, where the column score is made equal to 
two, and the process continues at block 320, else the process skips to 
block 320. In block 320, the process checks the status of the text first 
flag. If it is true, the process continues at step 321, where the column 
score is divided by two and the process continues at block 322, else the 
process skips to block 322. 
In FIG. 4, the process determines whether the cell selection should be 
based on the row cell examination or the column cell examination, or 
whether no range should be selected. In block 401, the process queries 
whether the value of the row score is equal to zero. If the row score is 
not equal to zero, the process continues at block 402, where it queries 
whether the row score is greater than or equal to the column score. If the 
row score is greater than or equal to the column score, the cells selected 
during the row examination are displayed to the user. If the row score is 
not equal to zero, the process skips to block 403, where it queries 
whether the column score is equal to zero. If both the row and column 
score are equal to zero, the process is unable to select a range of cells 
to add, therefore the process ends without setting the range selected flag 
to true. If the column score is not equal to zero or if the row score is 
not greater than or equal to the column score, the process continues at 
block 404. In block 404, the range selected flag is set to true, and the 
range is computed, based on the cell immediately to the right of the last 
cell searched and the cell immediately to the left of where the results of 
the addition are to be displayed. After the range is selected, the 
examination process ends. 
FIGS. 5 and 6 are screen prints of a spreadsheet implementing a preferred 
embodiment of the present invention. FIGS. 5a and 6a are screen prints 
taken immediately after the user has selected a cell where the results of 
the addition are to be displayed. FIGS. 5b and 6b are screen prints taken 
immediately after the user has selected the autosum function. A range of 
cells was automatically selected based on coordinates of the active cells, 
and is displayed as a plurality of contiguous cells bordered by a 
rotating, dashed, bold line. The inferred range and the sum function have 
been entered on the formula line and the system awaits confirmation or 
rejection by the user. FIGS. 5c and 6c are screen prints taken immediately 
after the user has confirmed the selection by pressing enter or by 
clicking on the enter box. The results of the addition are displayed. 
Although the methods and systems of the present invention have been 
described in terms of preferred embodiments, it is not intended that the 
invention be limited to these embodiments. Modifications within the spirit 
of the invention will be apparent to those skilled in the art. It will be 
apparent to those skilled in the art that the present invention can be 
used in other than spreadsheet systems, and for other than sum formulas. 
The invention can be used generally in systems where arithmetic functions 
are performed on columns or rows of data. The scope of the present 
invention is defined by the claims that follow.