Unit 4: Spreadsheet Skills

Lesson 2
Inserting Formulas and Functions

Empowerment Technologies
Senior High School Applied - Academic
What is Microsoft
Excel?

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 2
 Microsoft Excel is a
spreadsheet
application used to
perform
mathematical
equations without
manual computation.
It shows only the
results to the given
operations and data.
EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 3
What are the
different
operators
used in a
spreadsheet?

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 4
Types of Operators
●Arithmetic
●Comparison
●Text
Concatenation
●Reference
EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 55
How can we create a simple and
basic formula using those
operators?

6
 Factors to consider
before creating
formula:
1.Locate the cell
reference of each
piece of data or
use the point-
click method
2.Use Fill Handle to
copy formulas
EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 77
Creating a basic formula
1. Select the cell where
formula will be entered.
2. Type the equal sign and
select the cell that has
the value/s to be
computed using the
point-click method.

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 88
Creating a basic formula
3. Add the operator
needed in the formula
(arithmetic, comparison,
concatenation, or
reference).
4. Select another cell with
a desired value (use point-
click method) and hit Enter.
EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 99
How can we use functions as part
of the Excel formula?

10
Spreadsheet
Functions
●predefined
operations using
different formulas
●named after their
function

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 11
Parts of a Function
●Equal Sign
●Function name
●Argument

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 12
 Commonly Used Functions

●SUM
●AVERAGE
●COUNT
●MIN
●MAX
●CONCATENATE

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 13
13
Are there other functions used
besides those given above?

14
 Function Library
Different functions
are available in
the function
library, which can
be found inside
the Formulas tab.

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 15
Practice 1
Classify each symbol or
sign listed on the next
slide as an Arithmetic
Operator, a Comparison
Operator, a Text
Concatenation Operator,
or a Reference Operator.
EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 16
 ●> ●: ●# ●%
●< ●^ ● <= ●@
●& ● >= ●= ●+
●/ ●, ● <> ●-
Arithmetic Operator Comparison Operator Text Concatenation Reference Operator
Operator

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 17
Practice 2
Open your chosen
spreadsheet program. Go
to the Formulas tab. List
one function that you can
find in the categories
stated on the next slide.
Describe the use of that
function.
EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 18
Logical Category

Function Name Purpose

Text Category

Function Name Purpose

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 19
Date & Time Category

Function Name Purpose

Math & Trig Category

Function Name Purpose

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 20
Refer to the table on the next
slide. Compute for the BMI, with
three decimal values, of each
person. You can use a
spreadsheet and input the
values in the appropriate cells
to answer the succeeding
questions. Do not use a
function; rather, create a
formula. Note that the formula
for BMI is BMI = kg/m2.
EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 21
Creating a Formula

A B C D

1 Student Name Height (CM) Weight (KG) BMI

2 Pedro 142 50

3 Juan 160 48

4 Berto 143 55

5 Jose 178 58

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 22
22
 Read the instructions below. Use functions for this activity.
Open your chosen spreadsheet program and create a sheet entitled
“Monthly Family Spendings” with different columns, namely Household,
Food, Rent, Clothing, Transportation, Communication, Miscellaneous, and
Total Monthly Spendings. On the first row, write your family name under the
Household category and fill in the cost for each category up to
Miscellaneous. Gather additional data from four (4) of your classmates.
Compute the Total Monthly Budget of each Household using the AutoSum
function. Also, compute the average spending of the other three households
for each category and their average Total Monthly Spendings. Save a soft
copy and present your output to your teacher

EVALUAT
ENGAGE EXPLORE EXPLAIN EXTEND 23
23
 Wrap Up
There are four types of operators for spreadsheets,
namely Arithmetic Operators, Comparison Operators,
Text Concatenation Operator, and Reference
Operators.

You need to use cell names in order to have a cell

reference that you can use to create your formula.

The Fill Handle can be used to easily copy and apply

one formula to other cells, thus saving time.
24
 Wrap Up
A syntax must be followed for a function to work in a
spreadsheet. The function syntax starts with an equal
sign (=) followed by the function name and then the
argument enclosed in parentheses.

The commonly used functions are SUM, AVERAGE,

COUNT, MIN, and MAX.

The Function Library can be found on the Formulas tab

in the Ribbon. It contains different functions under
different categories.
25