Excel Lesson 5

Using Functions

By
Eddons Munthali
 Objectives
• Identify the parts of a function.
• Enter formulas with functions.
• Use functions to solve mathematical problems.
• Use functions to solve statistical problems.
• Use functions to solve financial problems.
• Use logical functions to make decisions with worksheet
data.
• Use functions to insert times and dates in a worksheet.
• Use text functions to format and display cell contents.

2 Lecturer: Eddons Munthali 2

 Vocabulary
• argument
• date and time functions
• financial functions
• Formula AutoComplete
• Function
• logical functions
• mathematical functions
• statistical functions
• text functions
• trigonometric functions

3 Lecturer: Eddons Munthali 3

 What Are Functions?
• A function is a shorthand way to write an
equation that performs a calculation.
• A formula with a function has three parts:
– The equal sign identifies the cell contents as a
formula.
– The function name identifies the operation to
be performed.
– The argument is the value the function uses to
perform a calculation.
4 Lecturer: Eddons Munthali 4
 What Are Functions? (continued)
• Parts of a function

Lecturer: Eddons Munthali 5

 Entering Formulas with Functions
• To enter a formula with a function, you
need to do the following.
– Start the formula with an equal sign.
– Select or enter the function you want to use.
– Select or enter the arguments.
– Enter the completed formula.
• To open the Insert Function dialog box,
click the Insert Function button on the
Formula Bar.
6 Lecturer: Eddons Munthali 6
 Entering Formulas with Functions
(continued)
• Insert Function dialog box

Lecturer: Eddons Munthali 7

 Entering Formulas with Functions
(continued)
• You can also enter a formula with a
function directly in a cell by typing an
equal sign, the function name, and the
argument.
• Formula AutoComplete helps you enter a
formula with a valid function name and
arguments.
– As you begin to type the function name, a list
of function names appears below the active
cell.
8 Lecturer: Eddons Munthali 8
 Types of Functions
• Mathematical functions and trigonometric
functions manipulate quantitative data in a
worksheet.
• Some mathematical operations, such as
addition and subtraction, do not require
functions.
• Mathematical and trigonometric functions
are particularly useful when you need to
determine values such as logarithms,
factorials, and sines.
9 Lecturer: Eddons Munthali 9
 Types of Functions (continued)
• Commonly used mathematical and trigonometric functions

Lecturer: Eddons Munthali 10

 Types of Functions (continued)
• Statistical functions are used to describe
quantities of data.
• For example, statistical functions can
determine:
– the average, standard deviation, or variance of
a range of data.
– the number of values in a range, the largest
value in a range, and the smallest value in a
range.

11 Lecturer: Eddons Munthali 11

 Types of Functions (continued)
• Commonly used statistical functions

Lecturer: Eddons Munthali 12

 Statistical functions cont..
NOTE: (STDEV, STDEVA, STDEVP, STDEVPA) (VAR, VARA, VARP,
VARPA)
• STDEV - Estimates std dev based on a sample(ignores logical
values and texts in a sample)
• STDEVA - Estimates std dev based on a sample including
logical values and texts (Text and logical value FALSE have the
value of 0; the logical value TRUE have the value of 1
Excel Lesson 5

• STDEVP - Calculates std dev based on the entire population

given as arguments (ignores logical values and texts)
• STDEVPA - Calculates std dev based on the entire
population, including logical values and texts (Text and logical
value FALSE have the value 0; the logical value TRUE have the
value 1

Pasewark & Pasewark Microsoft

Lecturer: Eddons Munthali Office 2010 Introductory
13
 Types of Functions (continued)
• Financial functions are used to analyze
loans and investments.
• Some commonly used financial functions
are future value, present value, and
payment.

14 Lecturer: Eddons Munthali 14

 Types of Functions (continued)
• Commonly used financial functions

Lecturer: Eddons Munthali 15

 Types of Functions (continued)
• Logical functions, such as the IF function,
display text or values if certain conditions
exist.
– The first argument sets a condition for
comparison, called a logical test. The second
argument determines the value if the logical test
is true. The third argument determines the value if
the logical test is false.
• For example, the formula
=IF(C4>60,“PASS”,“FAIL”) returns PASS if the
value in cell C4 is greater than 60; otherwise
the formula returns FAIL.

16 Lecturer: Eddons Munthali 16

 Types of Functions (continued)
• Commonly used logical functions

Lecturer: Eddons Munthali 17

 Types of Functions (continued)
• Date and Time functions can also be used
to insert dates and times in a worksheet.

18 Lecturer: Eddons Munthali 18

 Types of Functions (continued)
• Text functions are used to format and
display cell contents.

19 Lecturer: Eddons Munthali 19

 Summary
In this lesson, you learned:
• A function is a shorthand way to write an equation that
performs a calculation. A formula with a function has three
parts: an equal sign, a function name, and for most
functions one argument, which acts as an operand.
• The best way to select a function is from the Insert
Function dialog box. The Function Arguments dialog box
provides a description of each argument you enter for the
function.

20 Lecturer: Eddons Munthali 20

 Summary (continued)

• When you type a formula with a function directly in a

worksheet cell, Formula AutoComplete helps you enter a
formula with a valid function name and arguments.
• Functions can be used to perform mathematical, statistical,
financial, and logical operations. They can also be used to
insert and calculate dates and times and to format text.

21 Lecturer: Eddons Munthali 21