58K - views

Logical Functions. Part 1. Business . Intelligence. Quality . Information. Functions. 5-Component. SDLC. Info Literacy. Ethics. Design. Spreadsheet*. Database. . Data . Analytics. Big Data*. Methods.

Download Presentation - The PPT/PDF document "Business Intelligence" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.

## Presentation on theme: "Business Intelligence"— Presentation transcript:

Slide1

Logical FunctionsPart 1

Slide2

Data AnalyticsBig Data*MethodsData Mining*Text Mining*OLAP*Visualization*Query

DatabaseBenefits Components 

Implement with:* Excel Access

Slide3

Concept Map

Design

Implementation

IF, VLOOKUP

3-D Ref

Accuracy

Range Name

Group

Slide4

Objectives

Define

Demonstrate Excel’s logical functions that help produce business intelligence.

Slide5

Segment A:

Slide6

Set of software and methodologies that present visualizations depicting insights gathered from analytics and traditional IS for the purpose of improved decision making.

Slide7

Recommender Software

Music Genome ProjectArrangementBeatHarmonyLyricsMelodyRhythmTempoVoice

Content.time.com/time/video/player/0,32068,88490113001_1992632,00.html

Slide8

Segment B:

IF Functions

Slide9

IF function displays one of two possible values depending on the outcome of a logical testLogical Test compares two thingsIf 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.

IF Function

MAC: Logical Test is broken into 3 separate boxes

Slide10

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

Slide11

What cell is used for the logical test?What is the logical test?What is the true value? False value?

Course Difficulty Example

3

Slide12

Bonus Points Example

Open One Semester.xlsx

Extra Credit

Add a new column to display the extra credit points.

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.)

Curve

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.

Slide13

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.

Slide14

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.

Slide15

8:00 Average Example

Calculate the average for Exam 1 of just those students in the 8:00 section.

Slide16

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

c

riteria_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.

Slide17

8:00 Average Example

Calculate the average of Exam 1 for Freshmen in the 8:00 section.