Exam Slides 33-53 Flashcards
==VLOOKUP(“Orange”, A2:B5, 2, FALSE)
Product Price
Apple 1.50
Banana 0.80
Orange 1.20
Grapes 2.00
“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
=INDEX(A1:B21,2,1)
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
MATCH function in Excel returns _____________________________.
A simple example of the MATCH function is:
Formula =MATCH(25,A1:A3,0)
Result: 2=MATCH
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.
in referencing ShippedDate,
=TEXT(P8, “YYYY”) will return..
and =TEXT(P8, “MMMM”)…
__________ the two columns to get…
- year order was shipped
- month order was shipped
- concatenate
- Year-Month
__________: powerful tool for distilling a complex data set into meaningful information
PivotTables
PivotTables allows you to create…………….. and can be used quickly to create……………
- charts/summaries of key info
- cross-tabulations + drill down into a large set of data
PivotCharts
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
creating a storyboard steps (4)
- review all data analytics thus far
- split results into
- factual/descriptive/background data
- potential problems/issues - create a smooth flowing description
- create a set of recommendations
(T/F) Multiple process exist to create a storyboard.
true
An effective presentation should be a logical series of ________ that tell the whole story
headlines
headline
content
kicker-box
clickbait; tells the story
explains the story
tells the implications of the story, answers “so-what”
document quality checklist
refer to slides
primary vs foreign key
primary - identifying factor
foreign - links to primary but not identifying factor of its table
‘%queso%’
anything w/ queso in it
% in front would want returns of beginning with ‘queso’
left outer join
returns everything (what two tables have in common and what they don’t) on the left, returning uncommon info as NULL
NULL will get picked up for a count of the values in _____, but not for _____
COUNTA
COUNT
networkdays
business days
days
all days not just business (calendar)
blank column headers when running a pivot table will lead to…
error
pie chart
best for showing proportions
small # categories
bar/column graph
comparing diff categories/groups
use w/ categorical data
use when needing to compare values side by side, over time
line chart
showing trends over time
use when using continuous data (monthly sales, etc) over time
scatter plot
best for showing correlations bw variables
stacked bar/column
ex. comparing quarterly sales of a company across 3 regions (N, E, E) over 4 quarters.
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
predictive analytics
what will happen?
prescriptive analytics
how can we make it happen?