Transcript Business Intelligence

Business Intelligence
Logical Functions
Part 1
ACIS 1504 - Introduction to Data Analytics & Business Intelligence
Business
Intelligence
Quality
Information
Functions
5-Component
SDLC
Info Literacy
Ethics
Design
• Spreadsheet*
• Database
Data
Analytics
Big Data*
Methods
•
•
•
•
•
Data Mining*
Text Mining*
OLAP*
Visualization*
Query
Database
Benefits 
Components 
Implement with:
* Excel
 Access
Concept Map
Design
Business
Intelligence
Implementation
Range
Name
IF,
VLOOKUP
Accuracy
Group
3-D Ref
Objectives
• Define Business Intelligence.
• Demonstrate Excel’s logical functions that
help produce business intelligence.
Segment A:
Business Intelligence
Business Intelligence (BI)
Set of software and methodologies that
present visualizations depicting insights
gathered from analytics and traditional IS for
the purpose of improved decision making.
Pandora Internet Radio
Recommender
Software
Music Genome Project
•
•
•
•
•
•
•
•
Arrangement
Beat
Harmony
Lyrics
Melody
Rhythm
Tempo
Voice
Content.time.com/time/video/player/0,32068,88490113001_1992632,00.html
Segment B:
IF Functions
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.
Course Difficulty 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.
Course Difficulty Example
• What cell is used for the logical test?
• What is the logical test?
• What is the true value? False3 value?
Bonus Points Example
1. Open One Semester.xlsx
2. Extra Credit
A. Add a new column to display the extra credit points.
B. Give all students in the 8:00 Section 10 points of extra
credit. Students in the 9:00 section receive zero extra
credit. (Display either 0 or 10 in this new column.)
3. Curve
A. Add a new column to display the curved Exam 1
grades.
B. Curve Exam 1 grades by increasing all Freshmen grades
by 10% and leaving all other grades the same. Show
both the original and curved exam grades in separate
columns. Do not show the curve points separately.
Shipping Cost Example
• Open SmartPen Sales.xlsx.
• Delete the data in the Shipping Cost column.
• Use an IF statement to display 4.95 when the
shipping method is standard and 14.95 if it is
not standard.
SUMIF, COUNTIF & AVERAGEIF
• SUMIF function includes a value in the
calculation only when the criteria is true
=sumif(range, criteria, sum_range)
• Each cell in range is compared to criteria.
• If that comparison equates to TRUE, the
corresponding cell in sum_range is included
in the calculation.
8:00 Average Example
Calculate the average for Exam 1 of just those
students in the 8:00 section.
SUMIFS, COUNTIFS & AVERAGEIFS
• SUMIFS function includes a value in the
calculation only when two or more criteria are
true
=sumif(sum_range, criteria_range1, criteria1
criteria_range2, criteria2)
• Each cell in rangex is compared to criteriax.
• If all comparisons equate to TRUE for a single
row, the corresponding cell in sum_range is
included in the calculation.
8:00 Average Example
Calculate the average of Exam 1 for Freshmen
in the 8:00 section.