Using Basic Formulas and Functions

1
Using Basic Formulas and Functions

• Lesson 7

2

• SKILL MATRIX SKILL
• 3.1.1 Create formulas that use absolute and
  relative cell references
• 3.1.2 Create formulas that reference data from
  other worksheets or workbooks
• 3.1.3 Manage named ranges

Skills Matrix
3

• SKILL MATRIX SKILL
• 3.1.4 Use named ranges in formulas
• 3.2.1 Use SUM, COUNT, COUNTA, AVERAGE, MIN, and
  MAX
• 3.3.1 Create and modify list ranges
• 3.8 Display and print formulas

Skills Matrix
4
Formulas Tab
Totals adjacent cells
Create a named cell or range
View and edit named ranges
Functions used most recently
Select a built-in function
Active only when building a formula
Software Orientation
5
Formulas

• Major strength of Excel is ability to perform
  common and complex calculations

• Formula Equation that performs calculations on
  values in a worksheet

Building Basic Formulas
6
Parts of Formulas

• Operands Values to be used in calculations
• Mathematical operators Symbols that specify
  calculations to be performed

Operands
158
Tells Excel this is a formula
Mathematical operator
Building Basic Formulas
7
Create a Formula that Performs Addition

• Select cell to contain formula
• Begin formula with equal sign ()
• Enter operands separated by (addition operator)
• Example 826213

Building Basic Formulas
8
Create a Formula that Performs Addition (cont.)

• Sum appears in cell
• Formula displayed in Formula bar

Formula
Result of formula
Building Basic Formulas
9
Create a Formula that Performs Subtraction

• Select cell to contain formula
• Begin formula with equal sign ()
• Enter operands separated by (subtraction
  operator)

Building Basic Formulas
10
Create a Formula that Performs Multiplication

• Select cell to contain formula
• Begin formula with equal sign ()
• Enter operands separated by (multiplication
  operator)

Building Basic Formulas
11
Create a Formula that Performs Division

• Select cell to contain formula
• Begin formula with equal sign ()
• Enter operands separate by / (division operator)

Building Basic Formulas
12
Order of Operations

• Negative numbers ()
• Percents ()
• Exponentiation ()
• Multiplication () and division (/)
• Addition () and subtraction ()

Building Basic Formulas
13
Order of Operations (cont.)

• Use parentheses to control order of operations

Values in parentheses calculated first
Formula results
Building Basic Formulas
14
Use Relative Cell References in a Formula

• Reference
• Identifies cell or range of cells

• Relative cell reference
• Changes relative to location where copied or
  moved

Using Cell References in Formulas
15
Use Relative Cell References in a Formula (cont.)

• Hold down mouse button and drag to select cell
  range to be included in formula

Using Cell References in Formulas
16
Use Relative Cell References in a Formula (cont.)
Formula
Value returned by formula
Using Cell References in Formulas
17
Use Absolute Cell References in a Worksheet

• Absolute reference does not change when formula
  copied or moved
• Indicated by (dollar sign)
• Example D14

Using Cell References in Formulas
18
Use Absolute Cell References in a Worksheet
(cont.)

• Mixed reference
• One component is absolute and one is relative
• Examples D24, D24

Using Cell References in Formulas
19
Refer to Data in Another Worksheet

• Enter (equal sign) in destination cell
• Go to worksheet containing source data
• Select cell to be copied and press Enter
• Value appears in destination cell

Using Cell References in Formulas
20
Refer to Data in Another Workbook

• Go to destination workbook, select destination
  cell, and key (equal sign)
• Go to source workbook, select source cell, and
  press Enter
• Value appears in specified location in
  destination workbook

Using Cell References in Formulas
21
Name a Range

• Clarifies purpose of data in range of cells
• Easier to understand formulas that use a range
• Create your own names for cell ranges

Using Cell Ranges in Formulas
22
Name a Range (cont.)

• Select range
• Formulas tab, Defined Names group, Define Name
• Enter name in Name box and click OK

Using Cell Ranges in Formulas
23
Change the Size of a Range

• Formulas tab, Defined Names group, Name Manager
• In Name Manager dialog box, select range and
  click Edit
• Collapse dialog box and select new range
• Expand dialog box, click OK, and click Close

Using Cell Ranges in Formulas
24
Create a Formula that Operates on a Named Range

• Enter equal sign () into cell to hold formula
• Formulas tab, Defined Names group, Use in Formula
• Select range to be entered
• Finish formula and press Enter

Using Cell Ranges in Formulas
25
Functions

• Predefined formulas that perform calculation
• Save time
• Ensure accuracy

Summarizing Data with Functions
26
Use SUM

• Select cell to hold SUM function
• Formulas tab, Function Library group, Insert
  Function
• Select SUM function in Insert Function dialog
  box
• Click OK
• Sum appears in cell

Summarizing Data with Functions
27
Use COUNT

• Counts number of numeric entries in range

Summarizing Data with Functions
28
Use COUNTA

• Counts number of cells in range that are not
  empty

Summarizing Data with Functions
29
Use AVERAGE

• Adds range of cells and divides by number of cell
  entries

Summarizing Data with Functions
30
Use MIN

• Returns smallest number in set of values

Summarizing Data with Functions
31
Use MAX

• Returns largest value in set of values

Summarizing Data with Functions
32
Select Ranges for Subtotaling

• Sort data if necessary
• Select range to be subtotaled
• Data tab, Outline group, Subtotal
• In Subtotal dialog box, enter subtotal criterion
  in Add subtotal to box and click OK

Using Formulas to Create Subtotals
33
Select Ranges for Subtotaling (cont.)
Subtotals Hours each time value of Job Title
column changes
Using Formulas to Create Subtotals
34
Modify a Range in a Subtotal

• Can alter subtotal formula
• Can change values in Formula bar
• Can alter range used in calculation

Using Formulas to Create Subtotals
35
Build Formulas to Subtotal and Total
Number of SUM function
Range to be subtotaled
Using Formulas to Create Subtotals
36
Display Formulas on the Screen

• May be helpful in auditing formulas
• To display formula
• Formulas tab, Formula Auditing group, Show
  Formulas

Controlling Appearance of Formulas
37
Print Formulas

• May be helpful in auditing worksheet
• To print formulas
• Formulas tab, Formula Auditing group, Show
  Formulas
• Go to Page Layout tab and make desired changes
• Print worksheet

Controlling Appearance of Formulas
38
You Learned How to

• Create a formula that performs addition
• Create a formula that performs subtraction
• Create a formula that performs multiplication
• Create a formula that performs division

Summary
39
You Learned How to (cont.)

• Use relative cell references in a formula
• Use absolute cell references in a worksheet
• Refer to data in another worksheet
• Refer to data in another workbook

Summary
40
You Learned How to (cont.)

• Name a range
• Change the size of a range
• Keep track of ranges
• Create a formula that operates on a named range

Summary
41
You Learned How to (cont.)

• Use SUM
• Use COUNT
• Use COUNTA
• Use AVERAGE

Summary
42
You Learned How to (cont.)

• Use MIN
• Use MAX
• Select ranges for subtotaling
• Modify a range in a subtotal

Summary
43
You Learned How to (cont.)

• Build formulas to subtotal and total
• Display formulas on the screen
• Print formulas

Summary