EXCEL

3/18/2022 PRESENTED BY S.KHUDZE 1

 File tab – opens menus for opening and COM
saving Files, and modifying Excel Options

Quick Access Toolbar can be

customized to include icons to frequently
Used features such as Print Preview

Home Ribbon use to change fonts, justify text, insert

rows etc. Ribbons are organized into Groups of similar tasks
such as the Font group or the Number group. In addition, there
are other ribbons containing groups/buttons for laying out pages
using the review features etc.

3/18/2022 PRESENTED BY S.KHUDZE 2

 File tab – opens menus for opening and COM
saving Files, and modifying Excel Options

Quick Access Toolbar can be

customized to include icons to frequently
Used features such as Print Preview

Home Ribbon use to change fonts, justify text, insert rows etc. Ribbons are organized
into Groups of similar tasks such as the Font group or the Number group. In addition,
there are other ribbons containing groups/buttons for laying out pages
using the review features etc.

3/18/2022 PRESENTED BY S.KHUDZE 3

 COM

$7.00
$6.00
$5.00
$4.00
$3.00
$2.00
$1.00
$-
cereal milk eggs cheese meat pasta

3/18/2022 PRESENTED BY S.KHUDZE 4

The “Power” of using Spreadsheet Applications COM

=B2*C2

• Each entry can be related to other values by

including cell referencing in formulas.
• Formula values are automatically updated when
a referenced value changes
• Formulas can be copied
• Charts can be easily generated

3/18/2022 PRESENTED BY S.KHUDZE 5

 FORMULAS

3/18/2022 PRESENTED BY S.KHUDZE 6

Formulas COM

• A formula is a sequence of values, cell references and

operators that produce a new value.
= E8 + 3*(E10 - E11)
• Formulas always start with an equal sign =
• In addition a formula can also contain built-in functions
like SUM, AVERAGE, IF, COUNTIF, etc. =Sum(A2:A8)*2

3/18/2022 PRESENTED BY S.KHUDZE 7

 Things you need to know when writing COM

formulas in Excel

• Data precision vs. cell

display
• Types of operators that can / ≤ −
be used
• Order of precedence of
operators =B2+B3*B1/B8^2

3/18/2022 PRESENTED BY S.KHUDZE 8

 In order to write Excel formulas we also need to use the correct COM

Operator Symbols

Formulas contain two types of components:

• Operators: Operations to be performed
Arithmetic operators: * / + - ^
Relational operators: >, <, <=, >=,< >,=
• Operands: Values to be operated on
Addition
= B2 + 5 Operator

Operands

3/18/2022 PRESENTED BY S.KHUDZE 9

Precedence of Operators COM

( ) Parenthesis is a special operator that forces evaluation of the

expression inside it first
Exponentiation (2^3 →8)
Arithmetic operators: Multiplication & Division
Multiplication & Division have equal precedence and are evaluated from left to
right
Arithmetic operators: Addition & Subtraction
Addition & Subtraction have equal precedence and are evaluated from left to
right
Relational operators have a lower precedence than arithmetic operators

3/18/2022 PRESENTED BY S.KHUDZE 10

 Precision: number of decimal places stored in the computer. COM

Formatted Display: number of decimal places that appear in a cell

Type in a cell : =1/8 display in

cell Does the addition appear to be
correct in col B?

What value results for

each - if multiplied by
1000?
3/18/2022 PRESENTED BY S.KHUDZE 11
 COM
Formatting affects display not the precise value:

Formatting Number
Group

Percent Decimal Display

Currency Commas

3/18/2022 PRESENTED BY S.KHUDZE 12

 COM
Values can also be used to display dates

• Dates are values that can be entered

in several formats: January 27, 2013
or 1/27/2013
• Excel converts these dates to a
numerical representation (1/22/2013
→ 41301)
• Thus dates may be used in formulas:
=A1–B1 will result in the value 5

Note: To do arithmetic calculations with dates if you type =1/27/2013-1/22/2013 directly in a cell it
does not interpret it a date – cell references must be used.

3/18/2022 PRESENTED BY S.KHUDZE 13

 Walkthrough: Building a Simple COM

Spreadsheet
•Entering labels and values
•Formatting cells
–font, size, style, color, borders, alignment
–Numeric Format, Currency, Decimal Places
–text wrap, center titles
–Column widths, row height
•Inserting/Deleting rows and columns and sheets
•Writing a simple formula & modify decimal display
•Create a simple chart
• Sheet tabs
–Creating a new worksheets in a workbook (“new sections in a document”, Naming Sheets

3/18/2022 PRESENTED BY S.KHUDZE 14

 COM
Using Functions in Excel

SUM, MIN, MAX, AVERAGE, COUNT, COUNTA

ROUND
COUNTIF, SUMIF, AVERAGEIF

3/18/2022 PRESENTED BY S.KHUDZE 15

 A Function is a predefined worksheet COM

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)
3/18/2022 PRESENTED BY S.KHUDZE 16
 Functions take arguments and return a result COM
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.

3/18/2022 PRESENTED BY S.KHUDZE 17

 The SUM Function COM

• 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.

3/18/2022 PRESENTED BY S.KHUDZE 18

 Arguments of a SUM function COM

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

3/18/2022 PRESENTED BY S.KHUDZE 19

 COM
Using functions
A B C D E F G H
1 Grade Book
2 Lab1 Lab2 MT Final Total Percent
3 Total Possible points Honors 10 20 100 200 330
4
5 Blue H 9 15 88 186 298 90.3%
6 Jones 5 77 155 237 71.8%
7 Smith H 10 18 91 190 309 93.6%
8 Grey 7 10 75 155 247 74.8%
9
10 Highest Score 10 18 91 190 309 93.6%
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)

3/18/2022 PRESENTED BY S.KHUDZE 20

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

3/18/2022 PRESENTED BY S.KHUDZE 21

 Common Functions- with only a range argument COM
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.

3/18/2022 PRESENTED BY S.KHUDZE 22

How a function’s algorithm can affect the resulting value COM
• How does the Average
function algorithm treat
A
blank cells? 1 10
• What value will result in 2 20
3
cell A5 if it contains the
4 30
formula 5 =AVERAGE(A1:A4)
=AVERAGE(A1:A4)?

Blank cells are ignored → the resulting

value is 20
 COM
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
COM
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 COM
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?
→ 810
– How would your write a formula to round the
average value in cells Cl:C10 to the nearest
ten?
→ ROUND(AVERAGE(C1:C10),-1)
3/18/2022 PRESENTED BY S.KHUDZE 26
 COM
The num_digits argument
• Positive num_digits
round to the
specified number of
decimal places
• A zero results in a
whole number
• Negative
num_digits round
values to tens,
hundreds etc.

3/18/2022 PRESENTED BY S.KHUDZE 27

 Notice the Σ SUM gives different results COM
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

3/18/2022 PRESENTED BY S.KHUDZE 28

 COM
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.

3/18/2022 PRESENTED BY S.KHUDZE 29

 COM
Use the COUNTIF Function

The COUNTIF function counts the

number of values that meet a specified
criteria:
=COUNTIF(B5:B8, “H”)

3/18/2022 PRESENTED BY S.KHUDZE 30

 The COUNTIF Function counts the number of items in a COM
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
3/18/2022 PRESENTED BY S.KHUDZE 31
 How many people scored above 6 points COM
on either lab?

=COUNTIF(C5:C8,D5:D8, “>6”) X
=COUNTIF(C5:D8, “>6”) √
3/18/2022 PRESENTED BY S.KHUDZE 32
 The SUMIF Function sums the values in a range
COM
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

3/18/2022 PRESENTED BY S.KHUDZE 33

 Sumif Function COM

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)

3/18/2022 PRESENTED BY S.KHUDZE 34

 Use the AVERAGEIF function to average values that meet a COM
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.
3/18/2022 PRESENTED BY S.KHUDZE 35
A little harder..calculate the average cum for students by COM

college by year

=AVERAGEIF($B$3:$B$9,$B13,C$3:C$9)
Is this equivalent to a sumif/countif?

3/18/2022 PRESENTED BY S.KHUDZE 36

 Other Categories of Functions
•Statistics:
•Mean, Median, Standard
deviation
•Financial:
•Present value, Future value
•Logical:
•NOT, AND, OR
•Trigonometric:
•COS, TAN,

3/18/2022 PRESENTED BY S.KHUDZE 37

 THANK YOU
4 TH C O F F E E

FOR YOUR
AT T E N T I O N