Excel Module 2

Working with Formulas and Functions

1
 Module Objectives

• Create a complex formula

• Insert a function
• Type a function
• Copy and move cell entries
• Understand relative and absolute cell references
• Copy formulas with relative cell references
• Copy formulas with absolute cell references
• Round a value with a function

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 2
password-protected website for classroom use.
 Create a Complex Formula (Slide 1 of 2)

• A complex formula is an equation that uses more than one type of arithmetic
operator
• Example: formula that uses both addition and multiplication
• Arithmetic operations are performed according to the order of precedence
(4+2)*5
• First -Operations inside parentheses are calculated first
• Second -Exponents are calculated next
• Third-Multiplication and division are calculated next (from left to right)
• Fouth-Addition and subtraction are calculated next (from left to right)

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 3
password-protected website for classroom use.
 Create a Complex Formula (Slide 2 of 2)

Now we want to create

formula to calculate 20%
increase in tour expenses
Complex formula
=C12+C12*0.2

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 4
password-protected website for classroom use.
 Insert a Function (Slide 1 of 2)

• A function is a predefined worksheet formula that makes it easy to perform a

complex calculation
• Function Can be used by itself or within a formula
• If used alone, begins with the formula prefix (=)
• Functions are organized into categories ( such as Financial, Date & Time, Statistical)
• To insert function :
-you can use the insert function button on the formula bar
- you can insert function using AutoSum button on the Ribbon
- or you can use Quick Analysis tool

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 5
password-protected website for classroom use.
 Insert a Function (Slide 2 of 2)

Expanded Function Arguments dialog box

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 6
password-protected website for classroom use.
 Type a Function (Slide 1 of 2)

• In addition to using the insert function ,you can manually type the function
into a cell and then complete the arguments needed. But it required that :
-Must know the name and initial characters of the function
-Can be faster than using the Insert Function dialog box
-Experienced Excel users often prefer this method because it faster
• Necessary to begin with the equal sign (=) when typed manually
• Once you type an equal sign, each letter you type activates the AutoComplete
feature

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 7
password-protected website for classroom use.
Type a Function (Slide 2 of 2)

MAX function in progress

ScreenTip

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 8
password-protected website for classroom use.
 Copy and Move Cell Entries (Slide 1 of 2)

• You can copy or move data within a worksheet or between worksheets using:
1-Cut, Copy, and Paste buttons
2-Fill handle in the lower-right corner of the active cell
3-Drag-and-drop feature
• Clipboard temporarily windows storage area that holds the selections that you
copy or cut
• Pasting an item from the Clipboard
• Only need to specify the upper-left cell of the range where you want to paste the
selection

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 9
password-protected website for classroom use.
 Copy and Move Cell Entries (Slide 2 of 2)

In this practice we will calculate 30% rise ,instead of retype this information ,you copy and
move selected cells using 20% rise that we calculated before

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 10
password-protected website for classroom use.
 Understand Relative and Absolute Cell References (Slide 1 of 4)

• As you work in excel ,you may want to reuse formulas in different

parts of a worksheet to reduce the amount of data you have to
retype.

• -you can copy the formulas from one section to another but make
sure that they refer to correct cells
• To do this we need to understand the difference between relative
cell reference and absolute cell reference

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 11
password-protected website for classroom use.
 Understand Relative and Absolute Cell References (Slide 1 of 4)

• Use a relative cell reference when you want to preserve the relationship to the
formula location
• Calculations are performed based on cell relationship

• When a formula is copied, the cell reference changes to preserve the relationship of
the formula to the referenced cells

• For example cell F5 Contain the formula :=SUM(B5:E5),if you copy the cell to new
location such as cell F6 the result will reflect the new formula location B6:E6.

• Default in Excel

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 12
password-protected website for classroom use.
 Understand Relative and Absolute Cell References (Slide 1 of 4)

• Use an absolute cell reference when you want to preserve the exact cell
address in a formula
• Reference does not change even if the formula is copied to another location

• Created absolute cell reference by placing a dollar sign ($) before both the column
letter and the row number for the cell’s address

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 13
password-protected website for classroom use.
 Copy Formulas with Relative Cell References (Slide 1 of 2)

• Copy and moving a cell allow you to Reuse formulas you have already created
• Copying cells is usually faster than retyping the formulas and help to prevent
typing errors.
• Use Copy and Paste commands or the fill handle to copy formulas
• Copying a formula to a new cell
• Excel substitutes new cell references so that the relationship of the cells to the
formula remains unchanged
• Auto Fill feature can be used for filling cells with sequential text or values
• Months of the year; days of the week; or text plus a number (Quarter 1, Quarter 2,
etc.)
• Drag the fill handle to extend an existing sequence
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 14
password-protected website for classroom use.
 Copy Formulas with Relative Cell References (Slide 2 of 2)

Formula pasted in a range

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 15
password-protected website for classroom use.
 Copy Formulas with Absolute Cell References (Slide 1 of 2)

• When you copying cell ,and you want one or more cell references to remain
unchanged in relation to the formula ,you can use absolute cell reference

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 16
password-protected website for classroom use.
 Copy Formulas with Absolute Cell References (Slide 2 of 2)

Absolute reference created in formula

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 17
password-protected website for classroom use.
 Round a Value with a Function (Slide 1 of 2)
• Cells containing financial data are often easier to read if they contain fewer
decimals places than those that appear by default.

• Use the ROUND function to round down your results

• In this practice we will use Round function with Cell B14 to round the value in
it
• Round(number,num of digits)

• When num of digits=0 indicates that you donot want any decimal places to
appear in the calculate value
© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 18
password-protected website for classroom use.
 Round a Value with a Function (Slide 2 of 2)

ROUND function added to an existing formula

© 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a 19
password-protected website for classroom use.