Formulas and Functions

The real power of a spreadsheet resides in your ability to create formulas and functions. To understand these you also need to understand the concept of ranges.

Formulas

Since the result is a number, the resulting data is right aligned.

• a formula always begins with an equals ( = ) sign
• for example
  • =A1 + B3
  • =C9-G7
  • =D4*H7
  • =M12/T3
• mathematical rules of priority (aka “orders of operation”)
  • these long established rules are followed explicitly by all spreadsheet programs
  • here is a test of your understanding of these rules: what is 2+3*4?

Functions

The alignment of the resulting data depends upon the result, that is, the “answer” to some functions is text, to others it is a number.

• a function always begins with an equals ( = ) sign
• represents a shorthand way to perform mathematical and logical operations, that is, by calling a function you have requested that the spreadsheet perform a “prepackaged” operation; for example
  • summing a series of numbers (SUM)
  • averaging a series of numbers (AVERAGE)
  • calculating the standard deviation (STDEV) of a series of numbers

the basic format for a function is

function name(argument of function)

• function name is the name for the function to be performed; the “name” may be a whole word or an abbreviation
• the argument of function represents the cells to be evaluated
• for example
  • =SUM(A1:A3)
  • =AVERAGE(M4:M12)

for further reference

If you would like a reference to all of the functions included with Excel, with examples on how to use them …

• Microsoft Excel 2002 Formulas. (John Walkenbach) [M&T Books]
• Microsoft Excel 2000 Formulas. (John Walkenbach) [M&T Books]
• Microsoft Excel 2000 Functions in Practice. (Patrick Blattner) [Que]

Since the functions in Excel Windows and Excel Macintosh are the same, any book can be used for both platforms.

Ranges

A range is a rectangular group of cells. Ranges are included in many types of functions. To specify a range in a formula or function, you separate the upper-left and lower-right cells in the range (known as “anchors” or “anchor cells”) with a colon. For example:

• A3:A9 represents 7 cells in column A that span rows 3 through 9. Put another way, this range is 7 rows “high” and 1 column “wide”.
• C1:E4 represents 12 cells in columns C, D and E that span rows 1 through 4. This range is 4 rows “high” and 3 columns “wide”.

By definition, the smallest range would be a single cell while the largest would be the entire spreadsheet.

Why are Ranges Important?

Using ranges is much easier than specifying each individual cell in the argument. Consider the last function example above:

=AVERAGE(M4:M12)

is the same as

=AVERAGE(M4+M5+M6+M7+M8+M9+M10+M11+M12)

Formulas versus Functions

If you can use either a formula or a function to accomplish the same thing, and you need to perform a mathematical operation on a RANGE of cells, then you should always use a Function with its argument expressed as a RANGE. Ranges will automatically be updated if you add/delete rows or columns within the range. It is imperative that you express the range properly, that is with the anchor cells separated by a colon.

In other words, you could do either of the following

=A1 + A2 + A3 + A4 + A5

=SUM(A1 + A2 + A3 + A4 + A5)

=SUM(A1, A2, A3, A4, A5)

=SUM(A1:A5)

The last way is the right way. Although all four methods are mathematically the same, only the last way will always give you the correct result if you ever insert rows within the specified range. If you are not sure, try the following and you will see!

• click in cell A1
• enter 1, press <Return> / <Enter>
• enter 2, press <Return> / <Enter>
• enter 3, press <Return> / <Enter>
• enter 4, press <Return> / <Enter>
• enter 5, press <Return> / <Enter>
• you should now be in A6
• enter =A1 + A2 + A3 + A4 + A5 then press <Return> / <Enter>
• you should now be in A7
• enter =SUM(A1:A5) then press <Return> / <Enter>
• the number 15 should be in both A6 and A7
• now click on the row heading 4 to select all of row four
• choose InsertRows to insert a new row in the middle of the range
• click in the “new” cell A4
• enter 5 then press <Return> / <Enter>
• click in A7 and note the answer shown is still 15 and the formula now reads =A1 + A2 + A3 + A5 +A6
• click in A8 and note the answer shown is now 20 and the function now reads =SUM(A1:A6)

In both cases the formula/function was updated once the new row was inserted. However only the function with its argument expressed as a range was correctly updated to include the new information. Keep this concept in mind as we begin to build a spreadsheet.

Copyright©2003
last updated August 12, 2003