Excel Formulas and Functions - Whitman College



Excel Formulas and Functions

Basic Arithmetic Formula Construction

All Excel formulas begin with an equals ‘=’ sign. This rule must be followed without exception.

Enter the following in cell J5: =C5+E5+G5 This is an example of Direct Formula Entry. We will discuss the other method, the Function Wizard, at the end of this class. Press the enter key. Viola! Your first formula.

Things to note:

• As you enter the formula the text appears in the Formula Bar. This is where all formula construction work takes place. Fig 1

• Pressing the enter key evaluates (i.e. calculates) the formula.

• The result of the formula is displayed in the cell, but the true cell contents are shown in the formula bar. Be clear on the difference! Fig 1.

• All the basic functions work in the same way: Addition (using the + key), subtraction (the – key or the hyphen), multiplication (the * key or asterisk), division (the / key or the forward slash), and exponentiation (the ^ key or the carat).

• Functions may be combined to literally any degree you may desire.

• Use parentheses to control the order of operation in compound formulas. EX: =5+(2*3) yields 11 while =(5+2)*3 yields 21.

Fig 1

Hi-lighting Cells for Argument Entry

Functions are the operators that a formula uses, such as addition or subtraction. An argument is the data that a function acts on, such as the factors in a multiplication problem, or the mean and standard deviation in a normal distribution. These arguments can simply be typed in (hard coded), or as we did in our first exercise, the cell addresses of the arguments can be entered. The second method is preferred, due to the ease of changing the arguments value.

Fig 2

In most cases, an even better way to enter arguments is to use the mouse to enter cell addresses by pointing at the cell in which an argument will be entered. Again, lets look at Exercise sheet 1 on the accompanying diskette.

Pointing at cells is very simple. After entering the ‘=’ sign (thus notifying Excel that you are entering a formula), just point and click once on the cell that you want to perform some operation on. Next, enter the desired operation, and then click on another cell. This can be continued indefinitely. Once satisfied with the formula, press enter.

Editing Formulas

After a formula is constructed and evaluated, it’s not too late to make changes. Edit a cell by either pointing at the formula bar and clicking, or by pressing the F2 key. The screen will change to color code both the cell references in the formula bar, and the cells themselves on the worksheet - with matching colors, no less!

Things to note:

• By default, Excel inserts new characters into the formula as you type.

• Pointing and clicking with the mouse will add new cell references to the formula.

• Pressing enter evaluates the new formula, saving your changes.

• Alternatively, pressing the escape key will discard any changes to the formula.

The AutoSum Button

Anybody who works with spreadsheets for very long will inevitably end up adding a row or column of numbers. One way to do this we have already seen: Just type in the cell references alternately with plus signs. This is great until you have a column of 400 numbers that you want to add. Because adding big columns is such a common task, Excel has a special button called the AutoSum button. The AutoSum button is on the button bar and is labeled by the Greek character Sigma ( ( ).

Fig 5

AutoSum is invoked by placing the cursor in the cell in which you want the sum and pressing the AutoSum button. Excel will take its ‘best guess’ at what you want to sum up; if it guesses right, press enter to evaluate the formula and you’re done. If it guesses wrong, just hi-light the cell range that you want to sum up, and then press enter. See Exercise Sheet 1 on the diskette.

Relative, Absolute, and Mixed Cell References

From time to time, you will want to make certain that Excel treats a reference to a cell as absolute, no matter what. The way Excel knows that a cell is to be referenced absolutely is by the use of the dollar sign $. If you enter a cell reference as A5, this will be interpreted by Excel as a relative cell reference. Alternatively, if you enter the reference as $A$4, Excel will treat this reference as absolute. Absolute references can be entered manually from the keyboard, or by pressing the F4 key, you can cycle through the various reference types.

Mixed Cell References: These are a blend of absolute and relative references. Mixed cell references look like $A1 or A$1. Their primary purpose is to ‘freeze’ either the column or row respectively, while still allowing the counterpart to vary as the formula is copied either vertically or horizontally. See the page named ‘mixed’ on Exercise Sheet 2.

File and Worksheet Linking:

Much like Excel ‘links’ from one cell to another using cell references, Excel can also link from one sheet to another using worksheet references, and even from one file to another using file references. The syntax can be kind of tricky, so the best method is to simply use the mouse to point at a worksheet or file, and let Excel worry about the syntax. See Exercise Sheet 3a for examples and exercises.

Advanced Formulas, Text Functions, and the Function Wizard

The depth and breadth of functions and the formulas that can be built with them is almost to vast to be easily comprehended. Over 350 defined functions are available and can be combined in truly limitless ways. And if that's not enough, you can even custom design your own special functions (but that is another class!). The Function Wizard is the door to these Excel defined functions.

Fig 6

Invoking the Function Wizard brings up the box shown in Fig 7.

Fig 7

If you know the function, or the category that the function is in, then just select the function and follow the instructions. Lets work an example. Open Exercise Sheet 4, select a cell, and bring up the Function Wizard. Choose the text function category, and select concatenate. This function will join together a set of text arguments and calculate the result.

Fig 8

This powerful utility will now accept the arguments that you choose to type in. They can be either cell references, or actual text arguments. If you want to add cell arguments by the point and click method, you can minimize the space taken up by the Wizard, and inform Excel that you are using the point and click method by pressing the small button to the left of appropriate argument entry field. As you enter arguments, the final result of your formula is displayed in the box, as is each argument as it is entered. The box also describes the exact syntax required for the function, and a brief description of what the function does. The Function Wizard is also your doorway to the help system. If you click on the icon in the lower left corner of this box, you will bring up the Office Assistant. Click on the 'Help with this Feature' button. At this point you will either get the help screen you want or another office assistant. If you get the assistant, choose 'Help on selected function'. See figure 9.

Fig 9

When you are satisfied with the formula, click on OK. The formula will evaluate, and the result will appear in the selected box.

The Function Wizard is probably the best way to learn which functions are available and how they work. Get used to using the help screens, they contain not only a wealth of information on the different functions, but illustrative examples as well.

-----------------------

Formula Bar

Actual Contents of Cell B4

Result of the Formula in cell B4

The AutoSum Button

The Function Wizard

'Point & Click button.

Evaluated Argument

Evaluated Formula

Description

Syntax

Additional Help and Examples

The Function Wizard brings up an indexed list of all the available functions in Excel along with a short description.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download