Transcript Business Intelligence& Logical Functions

Business Intelligence
& Logical Functions
Supplemental Resources on Class Website
Concept Map
Business
Intelligence
Visualization
Calculations
OLAP
Design
Accuracy
Efficiency
Charts
Implementation
IF
VLookup
Objectives
▪Define Business Intelligence
▪Explain the Efficiency principle of
Spreadsheet Design
▪Demonstrate Excel’s logical functions
that help produce business
intelligence
Pandora Internet Radio
▪ Recommender
Software
Music Genome Project
•
•
•
•
•
•
•
•
Arrangement
Beat
Harmony
Lyrics
Melody
Rhythm
Tempo
Voice
Business Intelligence (BI)
▪software that searches vast amounts
of data to derive information for
improved decision making
Spreadsheet Design: Efficiency
▪All calculations, including
comparisons, should be done by the
spreadsheet.
Logical Calculations
▪IF
▪VLOOKUP
IF Function
▪ IF function displays one of two possible values
depending on the outcome of a logical test
▪ Logical Test compares two things
MAC: Logical Test is broken into 3 separate boxes
▪ If the Logical Test equates to TRUE, the cell is filled
with the True Value.
▪ If the Logical Test equates to FALSE, the cell is
filled with the False Value.
http://www.lacoste.pamplin.vt.edu/web1504/resources/video/ss.html
IF Function Example
▪ Use the IF function to assign the label of easy or hard
based on the hours. If a course requires more than 3
hours of study time per week, we will label it HARD.
Three or less hours of study is labeled EASY.
IF Function Example
▪What cell is used for the logical test?
▪What is the logical test?
▪What is the true value? False value?
3
VLOOKUP Function
▪ LOOKUP functions display one of MANY
possible values depending on the outcome
of a logical test.
▪ Often used to translate one value into
another value.
▪ Requires a lookup table.
▪ Lookup Value is compared to Lookup Table
▪ Can find Exact Match (false) or next closest
value (true)
http://www.lacoste.pamplin.vt.edu/web1504/resources/video/ss.html
Lookup Table Rules
▪First column must be used for logical
test.
▪First column must contain a value,
not a range of values.
▪First column should be in ascending
order.
Lookup Directory Example
▪Find the extension for a given name
in the directory.
Lookup Table for
Directory Example
Lookup Directory
Example Solution
1. F1 is compared to first column
of lookup table.
2. Extension from Column 2 is
displayed for match.
3. False means an exact match
must be found.