Using Functions in Excel
Objectives: Using Excel functions
SUM, MIN, MAX, AVERAGE,
COUNT, COUNTA
ROUND
COUNTIF, SUMIF, AVERAGEIF
A FUNCTION IS A PREDEFINED WORKSHEET FORMULA
The advantage of
using a function:
Saves time writing
Simplifies complex calculations
Faster execution
Less chance of typographical errors
Fewer characters in the formula bar
Example: instead of =C5+C6+C7+C8
use =SUM(C5:C8)
FUNCTIONS TAKE ARGUMENTS AND
RETURN A RESULT
The general format of a function is -
=Function name(arguments)
Arguments – argument variables are used by the
function to calculate the result. Arguments appear in a
specific order.
Syntax – specific format required to use a function its
name and order of arguments
Result – the value calculated by the function
Algorithm – a step-by-step procedure for
accomplishing some end task.
THE SUM FUNCTION
Syntax: SUM(range) or SUM(num1, num2, …)
Can type into cell, use AutoSum toolbar button or
function wizard
Argument: Value or Range of cells to be summed
Algorithm: Arithmetic sum of all values listed in
the range argument
Example: In the formula =SUM(B2:B8) * 3 Excel will add
the values in cells B2 through B8 and then multiple the
result by 3.
ARGUMENTS OF A SUM
FUNCTION
Valid Range Arguments for a SUM
function
A1:A4 - Range along a column
A1:D1 - Range along a row
A1:D4 - A two-dimensional range
(Block)
A1, D3:D5, 7 - non-contiguous cells*
* not all range arguments of functions can be used with non-contiguous cells
USING FUNCTIONS
1
2
1
A
Grade Book
A
Grade Book
B
B
C
C
Lab1 Lab2
D
MT
D
E
Final
E
F
F
Total Percent
G
G
H
H
2 Lab1 Lab2 MT Final Total Percent
3 Total Possible points Honors 10 20 100 200 330
3 Total Possible points Honors 10 20 100 200 330
4
4
5 Blue H 9 15 88 186 298 90.3%
5 Blue H 9 15 88 186 298 90.3%
6 Jones 5 77 155 237 71.8%
6 Jones 5 77 155 237 71.8%
7 Smith H 10 18 91 190 309 93.6%
7 Smith H 10 18 91 190 309 93.6%
8 Grey 7 10 75 155 247 74.8%
8 Grey 7 10 75 155 247 74.8%
9
9
10 Highest Score 10 18 91 190 309 93.6%
10 Highest Score 10 18 91 190 309 93.6%
11 Lowest Score 5 10 75 155 237 71.8%
11 Lowest Score 5 10 75 155 237 71.8%
What formula is written in cell G5 and copied
down the column to determine the total points
earned by the corresponding student? (grades.xls)
=SUM(C5:F5)
FUNCTION WIZARD
Function wizard: A short-cut to all the
functions in excel (use fx toolbar button) that
walks you through building a function
COMMON FUNCTIONS- WITH ONLY A
RANGE ARGUMENT
SUM(number1,[number2],…) Adds the numbers in a range of cells
AVERAGE(SUM(number1,[number2],) Calculates the arithmetic mean of a
list of values
MIN(SUM(number1,[number2],…) Returns the smallest number of a
range of values
MAX(SUM(number1,[number2],…) Returns the largest number of a
range of values
COUNT(value1,[value2],…) Determines the number of cells in a
range that contain numbers
COUNTA(value1, [value2],…) Counts non-blank cells
Where number1, number2 are 1 to 255 numeric arguments.
Arguments can either be numbers, ranged names or ranges
of cell references which contain numbers.
HOW A FUNCTION’S ALGORITHM CAN
AFFECT THE RESULTING VALUE
How does the Average
function algorithm treat
blank cells? A
1 10
What value will result in 2 20
cell A5 if it contains the 3
formula 4 30
5 =AVERAGE(A1:A4)
=AVERAGE(A1:A4)?
Blank cells are ignored the resulting
value is 20
HOW MANY HONOR STUDENTS ARE
THERE?
=COUNT(B5:B8)?
The COUNT function ignores blank cells
and text the resulting value is 0
Use =COUNTA(B5:B8) 2
IF SCORES SHOULD ONLY BE REPORTED AS
INTEGERS.. HOW CAN WE FIX THIS IN EXCEL?
The Increase/Decrease
decimal buttons do NOT
change a value only how
the value is displayed.
Use the ROUND function to change the
precision of a value
THE ROUND FUNCTION CHANGES THE
PRECISE VALUE OF A NUMBER, NOT
JUST ITS DISPLAY
Syntax: Round (number, num_digits)
= Round (24.44,1) results in the value 24.4
The ROUND function can be part of a larger formula:
What value results: =Round (B2,0)*10 if cell B2 contains the value 81.3?
How would your write a formula to round the average value in cells Cl:C10 to the nearest
ten?
810
ROUND(AVERAGE(C1:C10),-1)
THE NUM_DIGITS
Positive num_digits
ARGUMENT round to the specified
number of decimal
places
A zero results in a
whole number
Negative num_digits
round values to tens,
hundreds etc.
Notice the Σ SUM gives different
results when adding rounded values
The formula in cell C2 is =B2 – copied down
The formula in cell D2 is =Round(B2,0) – copied down
The formula in Cell B5 is =SUM(B2:B4) - copied across
COUNTING THE NUMBER OF
HONOR STUDENTS
How can we count the number of honor students if regular students have
the letter R in the honor’s column? Before we used a COUNTA and
ignored blanks but counted text.
USE THE COUNTIF
FUNCTION
The COUNTIF function counts the number of values that meet a
specified criteria:
=COUNTIF(B5:B8, “H”)
THE COUNTIF FUNCTION COUNTS THE NUMBER OF ITEMS IN A RANGE
THAT MEET A SPECIFIC CRITERIA.
COUNTIF (range*, criteria) –
Range - a continuous cell range
Criteria Syntax:
A number 6 =COUNTIF(B2:B7,6)
Text “USA” =COUNTIF(A1:A50,“USA”)
A cell reference B2 =COUNTIF(C3:C10,B2)
A Boolean expression “>5” =COUNTIF(A1:A10,“>5”)
* The comma tells the computer the next argument is the criteria – so
you cannot list individual cells separated by a comma for the range
How many people scored above 6
points on either lab?
=COUNTIF(C5:C8,D5:D8, “>6”)X
=COUNTIF(C5:D8, “>6”) √
The SUMIF Function sums the values in a
range that meet a specific criteria
SUMIF(range, criteria, sum-range)
Range – Continuous range used to compare the criteria
Criteria – Comparison Criteria
Sum-Range - If criteria is met, the computer will sum
the corresponding entry in this range
The syntax of the criteria is the same as the syntax of
the COUNTIF function:
- a number such as 6
- text such as “Honor”
- a Boolean value such as “<2”
- a cell reference such as A1
SUMIF FUNCTION
Write a formula in cell
C9, which can be
copied down the
column, to summarize
the number of courses
being taken by students
in this college
=SUMIF(B$2:B$6,A9, C$2:C$6)
USE THE AVERAGEIF FUNCTION TO AVERAGE VALUES THAT MEET A
SPECIFIED CRITERIA – AVERAGE NUMBER OF COURSES TAKEN BY
STUDENTS IN EACH COLLEGE
=AVERAGEIF(B$2:B$6,A9, C$2:C$6)
AVERAGEIF(range, criteria, averagif-range) –
works identically to
the SUMIF function except it averages the specified
range.
A LITTLE HARDER..CALCULATE
THE AVERAGE CUM FOR
STUDENTS BY COLLEGE BY YEAR
=AVERAGEIF($B$3:$B$9,$B13,C$3:C$9)
Is this equivalent to a sumif/countif?
Other Categories of Functions
• Statistics:
• Mean, Median, Standard
deviation
• Financial:
• Present value, Future value
• Logical:
• NOT, AND, OR
• Trigonometric:
• COS, TAN,
