Advanced Spreadsheet Skills: Lesson 2

CHAPTER 2
Lesson 2
Advanced Spreadsheet
Skills
• Commonly used Microsoft Excel functions
• Conditional functions
• Use of Microsoft Excel in market research
At the end of this lesson, the students should be able to:
1. familiarize with the most commonly used functions in Microsoft

Excel;
2. use several conditional functions available in Microsoft Excel; and
3. use Microsoft Excel as a viable tool in market research and product

development.
MS Excel
It features calculation, graphing tools, pivot tables, and a
macro programming language.
It can compute costs incurred in the creation of projects, or
create tables for findings in the researchers, and then
create reports for business or research that you are doing.
It is also a collaboration tool for financial analysis or
modelling.
Microsoft Excel provides an automated way of
displaying any statistical data. It can be used to
automatically compute for several factors that
are not easy to notice especially when faced by
a large data. Microsoft Excel includes several
arithmetic and basic functions that help you
compute faster.
Quick Access
Title Bar
Toolbar
File
Tab Ribbon
Zoom
Controls
View Buttons
New Tabs
Formulas Data View

Formulas
Data
View
Sheets Tab
Create New
Sheet
Columns
Name Bar Formula Bar
Cells
Rows
The Anatomy of a Formula
A formula is an expression which

calculates the value of a cell.
Functions are predefined formulas and
are already available in Excel.
Basic Excel Formulas:
Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
*type <=> before the formula, thus:

=a1+a2 <then, hit the ENTER key>
• Average – a function used to compute for the average of the number of a
range.
• COUNTIF – a function used to count the cells with a specified content

within a range.
• SUMIF – a function used to compute for the summation of a range if a

certain condition is met
• AVERAGEIF – a function used to compute for the average of a range if a

certain condition is met
Commonly used Functions
FUNCTION PURPOSE
=SUM Calculates the SUM of the values of a range of cells.
Calculates the ARITHMETIC MEAN of a range of cells or
=AVERAGE values.
=MAX Gives the MAXIMUM value in a range of cells or values.
=MIN Gives the MINIMUM values in a range of cells or values.
=COUNT Counts the number of cells in a range of cells or values.

Shows a series of calculations using the same formula, but
=IF a different value for each calculation to determine whether
the formula is true or false.
Other Specialized Functions
LOOKUP AND REFERENCE FUNCTIONS

Searches the first column of a table_array and returns a
VLOOKUP value from the same row in the column indicated by
col_index_num.
Searches the first row of table_array and returns a value
HLOOKUP from the same column, in the row indicated by
row_index_num.
ROWS Returns the number of rows in the specified range.
Other Specialized Functions
MATH & TRIG FUNCTIONS
Combines the IF and SUM functions to add specific values
SUMIF in a range according to the criterion you supply.
Similar to SUMIF, but count cells in the specified range that
COUNTIF match your specified criterion.
PRODUCT Multiplies all of its arguments.
Rounds a number by using this function for a specific
ROUND number of digits.
How to Create Formulas
=FUNCTION(Cells)
Function Formula Cell Formula
Sum =SUM( ) , Separated cells
Average =AVERAGE( ) :
Range of consecutive
cells
Today() =TODAY()
() [} Enclosure of cells
Concatenate =CONCAT( )
*-/x Basic Operations
How to Use the Basic Functions
EASY WAY
1. Create the table with rows & columns of data.
2. Select all the desired cells.
3. Go to the HOME/FORMULA tab, find the
AUTOSUM Command.
4. Click the dropdown arrow and select the
desired function.

2. Select all the desired cells.
3. Go to the
HOME/FORMULA tab;
find the AUTOSUM
command.
4. Click the DROPDOWN
ARROW, and select the
desired function.
LESS EASY WAY
2. Select the desired cell where you want the
result would be.
3. Type the FUNCTION FORMULA in the
desired cell or on the FORMULA BAR.
=FUNCTION(Cells)
4. Press ENTER on the keyboard.

2. Select the desired cell where you want the
result would be.
3. Type the FUNCTION FORMULA in the desired

cell or on the FORMULA BAR.
4. Press ENTER on the keyboard.
BASIC FUNCTIONS
FUNCTIO FUNCTIO
FORMULA FORMULA
N N
Sum =SUM( ) Count =COUNT( )
=AVERAGE( =COUNTA(
Average CountA
) )
Max =MAX( ) =CONCAT(
Concatenate
)
Min =MIN( )
ADVANCED FUNCTIONS
VLOOKUP
IF /HLOOKU
Function P
Function
How to Use the IF Function
1. Create the table with rows & columns of
data.
2. Determine the IF Criteria or Conditions.
3. Select the desired cell to put the results in.
AUTOSUM Command.
5. Click the MORE FUNCTIONS.

6. Search IF in the dialogue box
and select the IF Function.
7. Click OK.
8. In the LOGICAL_TEST box, type the
desired criterion (Cell>=Numerical
Value). For example is A1>=75.
9. Enter returned value in the IF_TRUE
and IF_FALSE text boxes (Passed,
Failed).

2. Determine the IF Criteria or Conditions.
4. Go to the
HOME/FORMUL
A tab; find the
AUTOSUM
command.
5. Click the MORE
FUNCTIONS.
6. Search IF in the
dialogue box and
select the IF
Function.
7. Click OK.
8. In the
LOGICAL_TEST
box, type the
desired criterion
(Cell>=Numerical
Value). For example
is A1>=75.
9. Enter returned value
in the IF_TRUE and
IF_FALSE text
boxes (Passed,
Failed).
Cell Returned
Reference Values
=IF(B12>=75, “PASSED”,”FAILED”)
IF Function
Criterion
Formula
VLOOKUP / HLOOKUP Function
VLOOKUP – Vertical
HLOOKUP - Horizontal
How to Use the VLOOKUP Function

2. Create the LookUp Table (your criteria).
Values must be in ASCENDING ORDER.
AUTOSUM Command.
5. Click the MORE FUNCTIONS.
6. Search VLOOKUP in the
dialogue box and select the
function.
7. Click OK.
8. In the LOOKUP_VALUE box, type or select the
reference cell.
9. In the TABLE_ARRAY, select the entire LOOKUP
TABLE. Make it ABSOLUTE (Press F4).
10.In the COL_INDEX_NUM, type 2.
11.In the RANGE_LOOKUP, type TRUE (closest
match)or FALSE (exact match).

2. Create the LookUp Table (your criteria). Values must be in ASCENDING ORDER.
8. In the LOOKUP_VALUE
box, type or select the
reference cell.
9. In the TABLE_ARRAY,
select the entire LOOKUP
TABLE. Make it
ABSOLUTE (Press F3).
10.In the COL_INDEX_NUM,
type 2.
11. In the RANGE_LOOKUP,
type TRUE (closest
match)or FALSE (exact
match).
8. In the LOOKUP_VALUE box, type or select the reference cell.

9. In the TABLE_ARRAY, select the entire LOOKUP TABLE. Make it ABSOLUTE (Press F4).
10.In the COL_INDEX_NUM, type 2.
11. In the RANGE_LOOKUP, type TRUE (closest match)or FALSE (exact match).
Cell Reference Logical
Reference Column Value
=VLOOKUP(B12,$N$3:$P$7,2,TRUE)
IF Function Vlookup
Formula Table

Advanced Spreadsheet Skills: Lesson 2

Uploaded by

Copyright:

Available Formats

Advanced Spreadsheet Skills: Lesson 2

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Advanced Spreadsheet Skills: Lesson 2

Uploaded by

Copyright:

Available Formats

CHAPTER 2

1. familiarize with the most commonly used functions in Microsoft

2. use several conditional functions available in Microsoft Excel; and

3. use Microsoft Excel as a viable tool in market research and product

Formulas Data View

Name Bar Formula Bar

A formula is an expression which

*type <=> before the formula, thus:

• COUNTIF – a function used to count the cells with a specified content

• SUMIF – a function used to compute for the summation of a range if a

• AVERAGEIF – a function used to compute for the average of a range if a

=MIN Gives the MINIMUM values in a range of cells or values.

=COUNT Counts the number of cells in a range of cells or values.

LOOKUP AND REFERENCE FUNCTIONS

1. Create the table with rows & columns of data.

1. Create the table with rows & columns of data.

3. Type the FUNCTION FORMULA in the desired

5. Click the MORE FUNCTIONS.

1. Create the table with rows & columns of data.

1. Create the table with rows & columns of data.

1. Create the table with rows & columns of data.

8. In the LOOKUP_VALUE box, type or select the reference cell.

You might also like