Data Analysis with Excel | Final Quiz

Match the correct graph components to label names:

Sort elements

Chart title
Y axis
Data series
Data point
X axis

A
B
C
D
E

Drag the components of the VLOOKUP formula into the right order

Table 2 range
= VLOOKUP
Table 1 common field
Exact or approximate match
Table 2 column to return

View Answers:

Select the formula combinations that you could use to return the text ‘suburb’ from this text value – ‘street-suburb.zipcode’

FIND + MID
LEFT + RIGHT
LEFT + MID
LEN + MID
FIND + RIGHT

Using the quiz dataset.

Calculate the skew for each numerical field. Which field in the dataset is highly right-skewed?

low
close
volume
high
open

Using the quiz dataset.

Calculate the interquartile ranges of the ‘Open’, ‘high’,’low’, ‘close’ and ‘volume’ fields.

Match the correct values:

Sort elements

42.16
42.29
41.49
42.02
39042308

Open
high
low
close
volume

Using the quiz dataset (Apple stock data 13-17)

How many values in the ‘close’ field fall above or below 3 STD Deviations from the average.

7 data points
13 data points
None
3 data points

Using the quiz dataset.

Create a pivot table to calculate Apple’s average stock price per year.

Match the correct answers below:

Sort elements

67.24
92.26
120.04
104.60
150.59

2013
2014
2015
2016
2017

Using the quiz dataset.

Create a pivot table that calculates the percentage increase/decrease of Apple’s average closing stock prices by year.

Match the correct answers below:

Sort elements

37.22%
30.10%
-12.86%
43.96%

2013 to 2014
2014 to 2015
2015 to 2016
2016 to 2017

Does this graph accurately reflect the STD Deviation of AAPL’s closing price by year?

Use a pivot table to check

Yes it does
No it does not

lms

lms