Exam Slides 33-53

Question 1

==VLOOKUP(“Orange”, A2:B5, 2, FALSE)

Product Price
Apple 1.50
Banana 0.80
Orange 1.20
Grapes 2.00

Answer 1

“Orange” is the value you’re looking for.
A2:B5 is the range of data
2 = column number
FALSE ensures exact match

Returns 1.20

Question 2

=INDEX(A1:B21,2,1)

Answer 2

returns a value or reference of the cell at the intersection of a specific row/column in the given range

given example will return row 2, column 1

Question 3

MATCH function in Excel returns _____________________________.

A simple example of the MATCH function is:
Formula =MATCH(25,A1:A3,0)
Result: 2=MATCH

Answer 3

the position of a value in a range of cells

The result is 2 because 25 is the second item in the range A1:A3.

Question 4

in referencing ShippedDate,
=TEXT(P8, “YYYY”) will return..
and =TEXT(P8, “MMMM”)…
__________ the two columns to get…

Answer 4

• year order was shipped
• month order was shipped
• concatenate
• Year-Month

Question 5

__________: powerful tool for distilling a complex data set into meaningful information

Answer 5

PivotTables

Question 6

PivotTables allows you to create…………….. and can be used quickly to create……………

Answer 6

• charts/summaries of key info
• cross-tabulations + drill down into a large set of data

Question 7

PivotCharts

Answer 7

utilize data in PivotTables and can be created in a one-click fashion:
- select PivotTable –>
- from Analyze tab, PivotChart –>
- excel will display an Insert Chart dialog allowing you to choose which kind of chart

Question 8

creating a storyboard steps (4)

Answer 8

1. review all data analytics thus far
2. split results into
   - factual/descriptive/background data
   - potential problems/issues
3. create a smooth flowing description
4. create a set of recommendations

Question 9

(T/F) Multiple process exist to create a storyboard.

Answer 9

true

Question 10

An effective presentation should be a logical series of ________ that tell the whole story

Answer 10

headlines

Question 11

headline
content
kicker-box

Answer 11

clickbait; tells the story
explains the story
tells the implications of the story, answers “so-what”

Question 12

document quality checklist

Answer 12

refer to slides

Question 13

primary vs foreign key

Answer 13

primary - identifying factor
foreign - links to primary but not identifying factor of its table

Question 14

‘%queso%’

Answer 14

anything w/ queso in it
% in front would want returns of beginning with ‘queso’

Question 15

left outer join

Answer 15

returns everything (what two tables have in common and what they don’t) on the left, returning uncommon info as NULL

Question 16

NULL will get picked up for a count of the values in _____, but not for _____

Answer 16

COUNTA
COUNT

Question 17

networkdays

Answer 17

business days

Question 18

days

Answer 18

all days not just business (calendar)

Question 19

blank column headers when running a pivot table will lead to…

Answer 19

error

Question 20

pie chart

Answer 20

best for showing proportions
small # categories

Question 21

bar/column graph

Answer 21

comparing diff categories/groups
use w/ categorical data
use when needing to compare values side by side, over time

Question 22

line chart

Answer 22

showing trends over time
use when using continuous data (monthly sales, etc) over time

Question 23

scatter plot

Answer 23

best for showing correlations bw variables

Question 24

stacked bar/column
ex. comparing quarterly sales of a company across 3 regions (N, E, E) over 4 quarters.

Answer 24

best for comparing parts of a whole across categories

ex. comparing total quarterly sales (aka sum of all the regions) in comparison to how much each region contributed

Question 25

predictive analytics

Answer 25

what will happen?

Question 26

prescriptive analytics

Answer 26

how can we make it happen?