CIOS Template - Cysewski



Lesson 6: Using Formulas and Functions

Mini Lecture

Lesson 6, Formulas, and Lesson 7 Exploring Functions, are designed to work together. The Lessons are designed to teach basic formula and function concepts and skills.

When working with formulas and functions start with simple problems and basic values. When the formula or function works, and you can check the work, then apply the formula or function to more complex problems. As always in Excel what looks accurate is not necessarily truly accurate.

The help built into Excel is valuable. I find that looking at Function examples is more useful than looking at Function syntax. If I want to understand a Function I copy the example and get it to work and then I apply my own numbers to the function. Later I can begin to understand the syntax of the function and the optional arguments.

In later lessons we will cover using Names in Excel, Array Formulas, Add-Ins, The Analysis ToolPak, Designing to minimize errors, and creating formulas in multiple worksheets.

Lesson 6 Outline

Lesson Concepts

• What are formulas?

• What are functions?

• What is the difference between a formula and a function

• What is the syntax of a function?

• What are the Excel tools for working with formulas?

• What are the Excel tools for working with functions?

• What are common formula errors?

• What are common formula debugging tips?

What is the difference between a formula and a function

Formulas can contain:

• Operators

• Cell References

• Values or Strings

• Worksheet Functions and their arguments

• Parentheses

Formula Syntax

• Begins with the = key

Functions:

• A worksheet function is a built-in tool that you use in a formula.

• Functions contain arguments that provide information to the function.

• The function uses the arguments to create an answer or result.

• The worksheet is an illustration of the distinction between formulas and functions. The formula in Cell B11 adds each cell; the function in Cell F11 uses the SUM function to accomplish the same ends. The formula in Cell B22 Averages the range F15:121 using a formula. The function AVERAGE in J22 does the same thing, but does not need to explicitly count, add, and divide the range of cells. The calculations are built into the function.

[pic]

Tools for Formulas and Functions

Office 2003 Tools

• Task Panes

• Smart Tags

• Smart Tags which indicate potential errors

Formula Tools

• More AutoSum options

• Can be used for other functions also. Average, Count, Max, Min

• Tools/Options/Error Checking

• AutoCorrect Options Button

• Using Formula AutoCorrect

• Checking for Common Errors

• Formula Watch Window

• Using the Rangefinder with Formulas

• Evaluate Formula Command

Function Tools

• The Function Wizard recommends functions

• ScreenTips for functions

o Pop up showing function syntax

• Function Help and Examples

o You can copy examples into a workbook for experimentation

Formula Concepts

• Modifying how Excel calculates formulas

• Arithmetic Operators

• Text Operators

• Comparison Operators

• Reference Operators

• The Order of Evaluation in Formulas

• Common Formula Error Messages

o Using Relative and Absolute Reference in Formulas

• Mixed Reference Example

Function Concepts

• What are functions?

• What are functions arguments?

• Using Function Help

o Explanation

o Examples

o Using Examples

• Using the Function Wizard

• Using the Excel Function Argument Shortcut

• 3-D Functions

Formulas Outline

• Excel Formula Tools

• Arithmetic Operators

• Text Operators

• Comparison Operators

• Reference Operators

• Order of Evaluation

• Relative and Absolute Reference

• Mixed Reference

• Types of Calculation

• Common Formula Errors

• Common Formula Error Messages

Exercises

Excel Tools/Options Settings

The Tools/Option Dialog box has choices that can change the fundamental working of Excel. For these exercises make sure that the settings are as illustrated. If they have been changed some of the exercises will not work.

Error Checking

In this dialog box you can customize how error checking works. If you are using Excel for the first time on a computer confirm these settings.

[pic]

General

Function tooltips are a valuable source of information. Confirm that they are selected.

[pic]

Calculation

Make sure that Calculation is set to Automatic. Manual calculation can cause problems if you do not remember that it is selected. We will use the INFO() Function to set up a formula to check for the calculation status.

[pic]

Formula Error Checking

Smart Tags and Error Checking

Smart Tags are a new feature that began with Office XP. Smart tags are an excellent tool to point out potential errors.

1. Create the worksheet as illustrated below.

[pic]

2. Highlight the range D3:D11 as illustrated below by double clicking the formula in cell D12..

[pic]

3. Drag Range Finder to select the cells D5:D11.

[pic]

4. Enter the Formula and a Smart Tag will appear.

[pic]

5. Click on the Smart Tag and a list of options will appear explaining the problem and giving some alternatives for a solution. Choose the Ignore Error Option.

[pic]

6. If you ignore the error there will be an indicator on your worksheet of a potential error. See cell D12 for an example.

[pic]

7. Select the Tools/Error Checking command.

[pic]

8. The Error Checking Dialog box will appear. Select the Help on this error command button.

[pic]

9. Help will appear explaining the potential error.

[pic]

Formula AutoCorrect

Excel will correct many common formula errors. This exercise illustrates a few common examples.

• Missing parenthesis

• Cell reference in row-col instead of col-row

• Using a semicolon instead of colon

• Extra space in cell reference or number

• Two decimal points together

• Extra operator at the beginning or the end

• Letter X instead of *

1. In a new worksheet enter the formulas as illustrated in column B. Make sure that you have a range of numbers entered in cells C3:C10.

[pic]

1. As you enter the formula Excel will display an explanation of the error and recommend a solution. Click on Yes

[pic]

2. Here is another example

[pic]

3. Continue to enter the other examples and watch how Excel uses AutoCorrect to recommend solutions.

Using the Range Finder and the Audit Toolbar to Correct Formulas

1. Create the Worksheet as illustrated below.

[pic]

2. Double Click on cell A4 and the rangefinder will appear. It will show the cells in the formula.

[pic]

3. With Cell A4 selected open the Auditing Toolbar. Tools/Auditing/Show Auditing Toolbar.

[pic]

4. Select Cell A4 and press the Trace Precedents Button. The Cells that precede the formula are traced.

[pic]

5. Press the Remove Precedent Arrows and the arrows will be removed.

[pic]

6. Select cell B7 and press the Trace Dependents button. The dependent will be traced. Remove the arrow.

[pic]

[pic]

7. Explore and play with the Rangefinder and the auditing toolbar.

Formulas

Formulas have a basic core set of concepts. This set of exercises is to make explicit essential formula concepts.

Formula Concepts

Arithmetic Operators

Addition +

Subtraction -

Negation -

Multiplication *

Division /

Percentage %

Exponent ^

1. Create the Worksheet as illustrated below. Enter the formulas as illustrated. The Formula View is just for illustration.

[pic]

2. Enter new values in cells C2 and D2 to watch the operators re-calculate

Text Operators

Concatenation &

[pic]

1. Create the Worksheet as illustrated above. Enter the formulas

2. Make sure that you enter a space character in cell B3

3. Enter new text in cells A3 and C3 to watch the operators re calculate

Comparison Operators

Equal to =

Less than <

Less than or equal to

Greater than or equal to >=

Not equal to

[pic]

1. Create the worksheet as illustrated above. Do not enter the data in cells E4:E10. The information is for explanation and is not necessary for the exercise to work.

4. The formulas are entered into cell C5:C10.

5. The results of logical tests are either TRUE or FALSE

6. Change the values in cells A5:B10 to watch the truth value change

7. In cell A5 enter A in Cell B5 enter A

8. In Cell A7 enter A in Cell B7 enter C (Reverse the C and the A and watch the Truth Value Change)

9. Experiment with the worksheet until you are comfortable with Logical Tests

Reference Operators

Range (colon) : B2:D2

Union (comma) , B4,D4

Intersection (space) space B7:D7 C6:C8

[pic]

1. Create the Worksheet as illustrated above. Enter the illustrated formulas in range A1:C3

Order of Evaluation

Formulas are calculated in the order of evaluation. The order of evaluation can be controlled by using parentheses

Range :

Intersect space

Union ,

Negation -

Percentage %

Exponent ^

Multiplication and Division * and /

Addition and subtraction + and -

Text joining &

[pic]

1. Enter the worksheet as illustrated above

10. Enter the formulas, as illustrated in cells B3:B4

Relative and Absolute Reference

1. Create the worksheet as pictured below. Enter the formulas as illustrated in the Formula View to put in the absolute reference to cell A2.

[pic]

[pic]

2. Change the value in cell A2 to 3.50% and watch the estimated growth change.

[pic]

3. Experiment with changing the value in A2 to see the benefit of using absolute reference.

Mixed Reference

1. The worksheet below takes one formula Auto Filled to fill the entire range of C4:L13. In order for this formula to work a mixed reference formula must be created. The formula is =$B4*C$3

2. Use the formula to try to re-create the worksheet. See the example to check your work.

[pic]

Another Mixed Reference Example

1. The worksheet below takes one formula Auto Filled to fill the entire range of C6:F10. In order for this formula to work a mixed reference formula must be created. The formula is illustrated in the formula bar.

2. Use the formula to try to re-create the worksheet. See the example to check your work.

[pic]

Summing an Entire Column

You can Sum entire Rows or Columns

• To create a formula that will refer to an entire Column use B:B

• For a formula in a Row us the row designator 2:2.

• If you put the formula in the selected column or row you will cause a circular reference error

1. Create the Worksheet as Illustrated Below.

[pic]

2. Experiment with entering numbers in Column B and Row 4.

3. Enter the Formula =SUM(B:B) into column B. Notice the Circular Error reference in the Status Bar.

AutoSum Shortcuts (Not a Function)

1. The AutoSum Tool has some interesting amplifications. Create the Worksheet as illustrated below.

2. Using the Ctrl key select the illustrated range and press the AutoSum toolbar button.

[pic]

3. Excel will create formulas for Sub Totals and Grand Totals.

[pic]

AutoSum is really Auto Statistics

1. Enter the Worksheet as illustrated below.

2. Select cell B9 and click on the downward facing arrow to the right of the AutoSum toolbar button.

3. Experiment with the choices.

[pic]

3-D Formulas and Syntax

Between Workbooks

You can write formulas that refer to other sheets in a Workbook and also between Workbooks.

The syntax is =[Book2]SourceData!$C$5. =[BookName]SheetName!CellReference

• A Single Cell Reference

[pic]

• A Reference to a Range of Cells

[pic]

Between Worksheets in the Same Workbook

You can write formulas that refer to other sheets in a Workbook and also between Workbooks.

The syntax is =SourceData!D3. =SheetName!CellReference

• A Single Cell Reference

[pic]

• A Reference to a Range of Cells

[pic]

1. Create two Workbooks. The Workbook names might be different then those illustrated below.

2. Give the Windows/Arrange/Tile/ command to tile the two Workbooks.

[pic]

3. Enter numbers in cells B2:B5 of one of the Workbooks.

4. Select Cell A2 of the other Workbook and enter =SUM( and then highlight the range B2:B5 of the other Workbook. Press Enter to finish the Formula

[pic]

5. You can see the forumula syntax that show the Book and Sheet name.

[pic]

Common Formula Error Messages

These are common error messages that occur no matter how often you create spreadsheets.

1. #NAME?

2. #DIV/0!

3. #N/A

4. #NULL!

5. #VALUE!

6. #REF!

7. CIRCULAR! Circular Reference REMEMBER TOOLBAR

8. #######

#NAME?

1. Enter the Worksheet as illustrated below. Make sure you misspell the SUMM function.

[pic]

1. Click on the Smart Tag and choose the Help on this error command.

[pic] [pic]

2. Read the description of the error.

#VALUE!

1. Enter the Worksheet as illustrated below. Make sure you enter the letter O not the number 0 in cell B3.

[pic] [pic]

2. Click on the Smart Tag and choose the Help on this error command.

3. Read the description of the error.

#REF!

1. Create the Worksheet as illustrated below.

[pic]

2. Select cell B7, right click, and Delete the cell.

[pic]

3. Choose to Shift cells up

[pic]

4. Click on the Smart Tag and choose the Help on this error command.

5. Read the description of the error.

[pic] [pic]

TOO NARROW COLUMN

1. Enter the Worksheet as illustrated below.

[pic]

2. Narrow Column C and watch the display change.

[pic]

CIRCULAR

1. Create the Worksheet as Illustrated below. Make sure that the formula includes itself in the range.

[pic] [pic]

1. When opening a Workbook with a Circular Reference you will get this dialog box.

[pic]

Array Formulas

• Arrays must be created using Ctrl-Shift-Enter. The brackets can not be typed into the function.

• Arrays cannot be edited.

• Read the description below. Arrays are important because some functions require the arguments to be entered as Arrays.

[pic]

Array Example

1. Enter the data as illustrated in columns A, B, and C.

2. In B7 Enter the Formula as illustrated. After creating the formula, use Ctrl+Shift+Enter to enter the formula.

3. If you make a mistake the result will look like the example in columns E, F, and G.

4. To correct the problem you must edit the formula and then use Ctrl+Shift+Enter to activate the formula. Just clicking on the formula will not do. Read the illustration for details.

[pic]

What are Functions?

[pic]

Function Arguments

• The components of a typical function are illustrated below.

[pic]

• Functions have required arguments, in bold, and optional arguments. Commas separate arguments. Read the explanation below.

[pic]

• The Help Provided for Functions includes examples. The Examples may be copied into an Excel Worksheet to serve as examples for further understanding.

[pic]

Function Tools

Insert Function Tool

1. Create the Worksheet as illustrated below. Select cell C6 and give the Insert Function command.

1. Type Payments of a loan in the Search for a function text box.

2. Select the Payment Function.

[pic]

3. Fill out the dialog box as illustrated and then confirm the dialog box.

[pic]

Expand Range Button

Sometimes a dialog box covers up cells that you want to select.

1. With the Insert Function Dialog box open select the collapse dialog button

[pic]

2. The Dialog Box will collapse so that you can select the cells that you need for the formula.

3. Click on the Expand Dialog Box Button and the Dialog box will be restored.

[pic]

4. The restored dialog box.

[pic]

ScreenTips for functions

1. Double Click the Function and look at the Screen Tips for the function.

[pic]

Common Function Errors

Not having all required arguments in the required order and data types. To omit an argument use the comma placeholder.

1. Edit the formula as illustrated. Make sure you remove the comma. When you enter the formula you will get the illustrated warning.

[pic]

Formula and Function De-Bugging Tips

If a long formula has an error in it convert it to text by typing an apostrophe before the formula. The formula can then be entered and edited later.

1. Double Click the Formula from the previous exercise. Enter a single apostrophe before the formula, as illustrated.

[pic]

2. You can also remove the = sign to convert a formula to text.

1. The Formula is converted to text. You can read and edit the formula.

[pic]

2. Remove the apostrophe and the text is converted back to a formula.

[pic]

Type Functions in lower case. The function and the cell references will display in upper case when the formula is entered. If the formula is incorrect the argument will not convert to upper case.

1. Type the formula as illustrated below. Use lower case for the example.

[pic]

2. When the formula is entered, Excel converts it to upper case. If it is not converted to upper case there is a problem with the formula.

[pic]

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

Circular Cell Reference

FORMULA VIEW

Type a space character in Cell B3

FORMULA VIEW

Formulas View

Collapse Button

Expand Button

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

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

Google Online Preview   Download