Excel Functions

The Excel functions are the "calculation heart" of a spreadsheet.  We'll take a brief look at how to use functions and some of the variety available.

Arithmetic Operators

You've seen the arithmetic operators before.  This is a reminder that you need to do your arithmetic using a new set of symbols to indicate arithmetic operations.

Order of Operation

When you have a string of operations, you often need to control which operations are done first.

This listing shows the order in which operations are performed.  For example, if you have both a subtraction and a multiplication in an arithmetic function, the multiplication is done first.  That might not be your intent, so you need to construct your function carefully.

You can control the order of operation by using parentheses.  When you surround part of your function with parentheses, what is inside gets done first.  For example, if you wanted a subtraction done before a multiplication, you would surround the terms used in the subtraction in parentheses.  That forces them to be done first since parentheses are the highest (first) in the order of precedence.

When there is equal precedence, your function is evaluated from left to right.

A good practice is to use lots of parentheses.  Extra parentheses never hurts.  Leaving them out may result in an error.

General Purpose of Functions

Excel functions are more than just a calculator within a cell.

Most often, functions are used to operate on values in other cells.

For example, you use a function to add up a column of values.

Sometimes you have alternative operations, depending on the value in some cell.  For example, you determine the tax to be paid by multiplying by the tax rate, but you only want to do this if a value is positive. You would do this with an Excel function that first tests the value to see if it is positive.  Depending on the outcome, you get either zero or the actual tax.

General Structure of Functions

Functions have a general structure that you should know.

• They start with an equal sign.
• This is followed by the name of the function.
• Then there is a left parenthesis.
• Inside the function are the values to be used by the function.
• Finally, there is a closing right parenthesis.

There aren't any blanks in this.

Literal Values

Sometimes you need to supply what is called a "literal value" to a function.  These are text values where you want to say, this is the text that is to be used.

"Literal" is already a familiar term, as in "I literally mean this."

You enclose literal text in double quotation marks.  You can see this in the example.

Single-Column Functions

Some of Excel's functions generally work on a single column of your spreadsheet.

An example is the function that lets you add up all the values in a column.  You use the SUM function.

SUM is actually a very general function that lets you enter a bunch of individual cells (each separated by commas), or a range (hence the identification as a single column function).

Look at the examples and you'll see how it works.

A similar function is COUNT.  It tells you how many non-blank items are in the range.  That's pretty useful, particularly if you have a long list of items.

Single-Value Functions

(This slide should be titled Date Functions)

Some functions require information that generally doesn't come from spreadsheet cells.

For example, Excel has a function (TODAY) that lets you put the current data into a cell.

Similarly, you can put the date and the time (with NOW).

The last of these functions does let you use information from spreadsheet cells.  If you have information in different cells that correspond to a year, month and day, you can synthesize these into a date with the DATE function.

Logical Functions

Logical functions let you test whether something is true or false.

Logical functions have a lot of uses.

The first part of a logical function is the logical test.  This is the test that will be evaluated to see whether it is true or false.

The logical operators are shown here along with their meaning.

Below, you see three examples of how you compare the values in different spreadsheet cells, or actual values.  Remember, the result of each test results in a true or false.

The first of the comparisons says "the value in cell A1 is less than the value in cell B1."

The second one says "the value in cell B3 is greather than or equal to 25."

Finally, the last one says "the contents of cell C9 is the literal value "dog".

Logical Functions

The most common place to use a logical operator is in the IF function.  There are three parts to the IF function: the logical test, then what is to be used if the result of the test is true, then what is to be used if the result is false.

Look at the example.  If the value in cell A5 is greater than the value in cell B5, then put the word "invalid" in the cell.  Otherwise, put the value in cell B5.

COUNTIF looks at a range of cells.  It counts how many cells pass the test listed as "criteria."  For example, count the number of cells in the range from A1 through A5 which have values greater than 3.

SUMIF works the same way.