Recap

1
Recap

• Relative addressing No , new formula will be
  determined by the displacement from the original
  cell.
• Absolute addressing Use to keep
  column/row/both fixed when copying a formula.

2

3

4
Using Functions in Excel

• Objectives Using Excel functions
• Sum, Min, Max, Average, Count
• Round
• Countif Sumif

5
A Function is a predefined worksheet formula

• The advantage of using a function
• Saves time
• Simplifies complex calculations
• Faster
• Less chance of typographical errors
• Fewer characters in the formula bar
• Example instead of A1A2A3A4A5
• use SUM(A1A5)

6
Functions take arguments and return a result

• The general format of a function is -
• Functionname(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.

7
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(B2B8) 3 Excel
  will add the values in cells B2 through B8 and
  then multiple the result by 3.

8
Arguments of a SUM function

• Valid Range Arguments for a SUM function
• A1A4 - Range along a column
• A1D1 - Range along a row
• A1D4 - A two-dimensional range (Block)
• A1, D3D5, 7 - non-contiguous cells
• not all range arguments of functions can be
  used with non-contiguous cells

9
Using functions
What formula is written in cell G5 and copied
down the column to determine the total points
earned by the corresponding student? (link)
SUM(C5F5)
10
Common Functions- with only a range argument
Where number1, number2 are 1 to 30 numeric
arguments. Arguments can either be numbers,
ranged names or ranges of cell references which
contain numbers. (Link)
11
How a functions algorithm can affect the
resulting value

• How does the Average function algorithm treat
  blank cells?
• What value will result in cell A5 if it contains
  the formula AVERAGE(A1B4)?

Blank cells are ignored ? the resulting value is
20
12
COUNT

• Count the number of numeric cells in the range
• Syntax count(range)
• Argument Range to be counted

13
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
• What value will result if you write the formula
  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 ClC10 to the nearest ten?

? 810
? ROUND(AVERAGE(C1C10),-1)
14
Round changes the precise value
The formula in cell C2 is B2 copied down The
formula in cell D2 is Round(B2,0) copied
down The formula in cell C5 is C2C3C4 copied
across
15
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(B2B7,6)
• Text USA COUNTIF(A1A50,USA)
• A cell reference B2 COUNTIF(C3C10,B2)
• A Boolean expression gt5 COUNTIF(A1A10,gt5)

The comma tells the computer the next argument
is the criteria so you cannot list individual
cells separated by a comma for the range
16
Using the COUNTIF Function
How many people scored above 70 points on Midterm
1?
COUNTIF(B4B6, gt70)
17
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
lt2 - a cell reference such as A1
18
SUMIF
What is the total donation amount of all Female
students ? Sumif(B3B7, F , E3E7)
What is the total donation amount of all students
with GPAgt3? Sumif(D3D3, gt3 , E3E7)
What is the total donation amount of EHE students
?
19
SUMIF
Write a formula in cell B10, which can be copied
down the column, to calculate the number of
donations made by EHE students
20
SUMIF cont

Write a formula in cell B10, WHICH CAN BE COPIED
DOWN the column, to calculate the total donation
amount made by students in this college
21
Other Categories of Functions

• Statistics
• Mean, Median, Standard deviation
• Financial
• Present value, Future value
• Logical
• NOT, AND, OR
• Trigonometric
• COS, TAN,

22
Function Wizard

• Function wizard A short-cut to all the functions
  in excel (use fx toolbar button)
• Walks you through building a function
• Useful when you dont know which function to use
  and how to use it

23
Function Wizard

• Function wizard A short-cut to all the functions
  in excel (use fx toolbar button) that walks you
  through building a function

24
Examples

• Gradebook
• Rock Concert