CLB KHOA HỌC CÔNG NGHỆ

TRONG KINH TẾ VÀ KINH DOANH - CTE FTU

Data Science Hub

EXCEL TRAINING 01:

FUNDAMENTAL
CONCEPTS
Agenda

1. Excel Overview

2. How Formulas Work

3. Math, Date, Text, and Logic

Functions
 Excel as an Analytics Tool

Pros Cons
Functionality Not a Database
● Quickly inspect and summarize data ● Holds up to 1,048,576 rows, but calculations are
● Flexible for charts and tables slower at a fraction of maximum size

Ease of Use Advanced Statistics are Limited

● Integrate easily with other programming tools, or ● Not intended for complex statistics or econometrics,
with final report/presentation limited functions available
● Intuitive early learning curve ● Only basic linear programming (optimization)

Other

Widespread adoption ● Tedious to automate production tables and charts at

scale
● Widespread use and familiarity ● Costs money! (Google Sheets is free)
● Incredible online resources to solve problems ● Mac vs PC differences
● Can handle huge variety of tasks
 How Excel Functions Work

Functions Have 3 Ingredients

Usually arguments will contain cell references

Cell references are by default dynamic but we can

choose to lock cell references to meet our needs
using $ symbol:
● Row lock (A$1) - as formula is dragged down
1) Begin with an equal sign to signal that + right, only column reference will update
what comes next is a formula (rather than ● Column lock ($A1) - as formula is dragged
data) down + right, only row reference will update
2) Specify the function name which is then ● Double lock ($A$1) - as formula is dragged
immediately followed by parentheses down + right, cell reference will not update
3) Within the parentheses we pass
arguments (inputs) to the function, which
the function uses to calculate outputs
Math Functions

Perform calculations using operators and functions

Basic operators Functions
=MIN(list of numbers)
=MAX(list of numbers)
=AVERAGE(list of numbers)
=COUNT(list of numbers)
=SUM(list of numbers)

A list of numbers can include a combination of cell ranges and values.

Examples:
MAX(0 , 1 , 5) = 5
MAX(B2:B4) = 4
MAX(B2:D4) = 8
MAX(B2:B4, 5) = 5
Challenge Exercise - Math

What we are doing What we are using

Complete the orange-filled cells =MIN(list of numbers)

=MAX(list of numbers)
=AVERAGE(list of numbers)
=COUNT(list of numbers)
=SUM(list of numbers)
 Date Functions

Excel has many useful functions to deal with date and time
Get specific attributes Add/subtract days and dates

YEAR(“6/1/2020”) = 2020 6/1/2020 + 8 = 6/9/2020

MONTH(“6/1/2020”) = 6 6/1/2020 - 1 = 5/31/2020
DAY(“6/1/2020”) = 1 6/1/2020 - 6/1/2019 = 366
DATEDIF(“6/1/2019”, “6/1/2020”, “D” ) = 366
Combine attributes into date Get end of the month
DATE(2020, 6, 1) = 6/1/2020 EOMONTH(“6/1/2020”, 0) = 6/30/2020
TODAY() = [today’s date] EOMONTH(“6/1/2020”, -1) = 5/31/2020

Displayed date format is a formatting decision:

June 1, 2020 = 6/1/2020
Challenge Exercise - Dates

What we are doing

Complete the green-filled cells

What we are using

YEAR(“6/1/2020”) = 2020 DATEDIF(“6/1/2019”, “6/1/2020”, “D” ) = 366

MONTH(“6/1/2020”) = 6 EOMONTH(“6/1/2020”, -1) = 5/31/2020
DAY(“6/1/2020”) = 1
DATE(2020, 6, 1) = 6/1/2020
TODAY() = [today’s date]
Text Functions

Split and combine text data

Syntax Example

LEFT(text, number of characters) LEFT(“Testing”, 4) = “Test”

RIGHT(text, number of characters) RIGHT(“Testing”, 3) = “ing”
MID(text, start character, end character) MID(“Testing”, 4, 6) = “tin”
LEN(text) LEN(“Testing”) = 7

CONCAT(text1, text2, …) CONCAT(“Linked”, “in”) = “Linkedin”

CONCATENATE(text1, text2, …) CONCATENATE(“D”, “-“, “Lab“) = “D-Lab”
text1&text2&... “Go”&” “&”Warriors“ = “Go Warriors”
TRIM(text) TRIM(“ testing “) = “testing” (no edge whitespace)
Challenge Exercise - Text

What we are doing What we are using

Complete the gold-filled cells CONCAT(text1, text2, …)

CONCATENATE(text1, text2, …)
text1&text2&...
Logic Functions

Most logic functions use a conditional test to determine if

condition is TRUE or FALSE

Conditional tests use logical operators

=, <>, <, >, <=, >=

Logic Functions: IF

IF(conditional test, value if true, value if false)

IF(A25 < B25,

“A is smaller than B”, IF(A25 = B25,
“A not smaller than B”) “A equals B”,
“A and B are different values”)

Since 1 < 2 is TRUE, take the value if true

which is “A is smaller than B” Since 1 = 2 is FALSE, take the value if false
which is “A and B are different values”
Challenge Exercise - Logic #1

What we are doing

Complete the dark grey-filled cells

What we are using

IF(conditional test, value if true, value if false)

 Logic Functions:
ISBLANK, ISNUMBER, ISTEXT

Test for basic characteristics of a cell

ISBLANK(A3) returns FALSE

ISBLANK(B3) returns TRUE
ISNUMBER(A2) returns FALSE
ISNUMBER(B2) returns TRUE
ISTEXT(A4) returns TRUE
ISTEXT(B4) returns FALSE

IF(conditional test, value if true, value if false)

IF(ISBLANK(B3), “Missing Test”, “Test Taken”)

IF(ISNUMBER(A6), “That’s a test score”, “That’s a student name”)

IF(ISTEXT(A5), “That’s a student name”, “That’s a test score”)

 Logic Functions & Errors:
ISERROR, IFERROR, ISNA

Detect and handle errors & missing values

ISERROR(value) returns TRUE if argument is any error type, FALSE

otherwise

ISNA(value) returns TRUE if argument is #N/A,

FALSE otherwise

IFERROR provides a backup option if an error occurs

IFERROR(function or value, value if error in evaluation)

These return the same thing:

IF(ISERROR(X1/Y1), “There is an error”, X1/Y1)

IFERROR(X1/Y1, “There is an error”)

Check specifically for N/A:

IF(ISNA(X2),“NA value detected”, “No NA value”)

Challenge Exercise - Logic #2

What we are doing What we are using

Complete the pink-filled cells IF(conditional test, value if true, value if false)
ISBLANK()
ISERROR()

IFERROR(function or value, value if error in evaluation)

 Logic Functions: AND, OR

Stack multiple conditions with AND & OR functions

AND(1 >2, 3>2) returns FALSE

OR(1 >2, 3>2) returns TRUE

IF(conditional test, value if true, value if false)

IF(AND(test1, test2, …), value if true, value if false)

IF(OR(test1, test2, …), value if true, value if false)

IF(AND(B5>60, C5>60, D5>60), “Connor graduates”, “Connor repeats grade”)

IF(OR(C2<60, C3<60, C4<60, C5<60, C6<60), “Someone failed history”, “Everyone passed history”)
Challenge Exercise - Logic #3

What we are doing

Complete the light grey-filled cells

What we are using

IF(OR(test1, test2, …), value if true, value if false)

Summary of Logic Functions

Function Conditional test Output

IF() Custom Custom
ISBLANK() Is a cell blank? True OR False
ISERROR() Is an error? True OR False
IS____() (Cell or value property) True OR False
IFERROR() Is an error? Custom
AND() Multi-Custom True OR False
OR()