Conditional Formatting Assessment

Twitch is a livestreaming video service. While you can find a variety of different types of content on Twitch, they are primarily known for video game streaming. Top streamers can earn millions of dollars a year.

The Value, Top, and Symbols worksheets all contain data on the top 200 Twitch gaming streamers in 2020. On the Value worksheet, use conditional formatting based on target values to examine the data. Use conditional formatting based on exceptional values to analyze the data on the Top worksheet. Finally, use the Data Bars, Color Scales, and Icon Sets to examine the data on the Symbols worksheet. (my email fastessayz@gmail.com)

1.1

Use conditional formatting to highlight the number of followers for streamers with more than 500000 followers.

Apply the conditional formatting to range G3:G202.

Highlight values over 500000 with light green background and dark green text.

1.2

Use conditional formatting to highlight the number of streaming minutes for streamers with less than 100000 minutes streamed.

Apply the conditional formatting to range D3:D202.

Highlight values less than 100000 minutes streamed with light red background and dark red text.

1.3

Use conditional formatting to highlight the value for average viewers of streamers with between 5000 and 8000 average viewers.

Apply the conditional formatting to range F3:F202.

Highlight values between 5000 and 8000 average viewers with light yellow background and dark yellow text.

1.4

Use conditional formatting to highlight the value for language of streamers who speak Czech.

Apply the conditional formatting to range H3:H202.

Highlight values that contain the text “Czech” (without the quotes) with light red background and dark red text.

1.5

Use conditional formatting to highlight the top 30 values for minutes of watch time.

Apply the conditional formatting to range C3:C202.

Highlight the top 30 values for watch time with light green background and dark green text.

1.6

Use conditional formatting to highlight the bottom 15 % of values for minutes of stream time.

Apply the conditional formatting to range D3:D202.

Highlight the bottom 15% of values for stream time with light red background and dark red text.

1.7

Use conditional formatting to highlight the above average values for peak viewers.

Apply the conditional formatting to range E3:E202.

Highlight the above average values for peak viewers with light yellow background and dark yellow text.

1.8

Use Data Bars to highlight the number of peak users for each streamer.

Apply the conditional formatting to range E3:E202.

Select the blue data bars option.

1.9

Use Color Scales to highlight the number of followers for each streamer.

Apply the conditional formatting to range G3:G202.

Select a three-color option.

1.10

Use Icon Sets to highlight the number of minutes streaming for each streamer.

Apply the conditional formatting to range D3:D202.

Use the 3 Arrows (Colored) option.

Data Project #1

Brief Description

Students collect trade data on a given topic (Export of Educational Service by Canada), organize and

summarize the data, and draw conclusions.

Detailed instructions

Collect data.

Detailed steps can be followed in the video clip in this folder. Watching the clip is highly

recommended. I also explained the dataset in the clip, which may help you understand the data

you are using better.

Go to comtrade.un.org/data/.

Make the following choices and click to download CSV file. (You may later want to download

more data based on your questions.)

You can follow the steps in the video clip to clean the data.

You can also take a quick look at page 4-5 in the reference file from Statistics Canada.

http://www.statcan.gc.ca/eng/statistical-programs/document/1536_D2_T9_V1-eng.pdf

Organize and illustrate the data. (Please see the general instruction file.)

Submission (Please see the general instruction file.)

Grading (Please see the general instruction file.)

file://///home.uvic.ca/ljzhang/My%20Documents/courses/306/project/202009/comtrade.un.org/data/

http://www.statcan.gc.ca/eng/statistical-programs/document/1536_D2_T9_V1-eng.pdf

List of Potential Research Questions

(This is a question that does not need data illustration. Every team must complete this question.

Make sure every team member knows about the answer.) Please use your own words to explain

some background information on the topic. (The answer is in the reference file.)

What is export of education-related expenditure?

How is it measured?

What are the patterns in Canadian Export of “Education-related expenditure”?

Have the patterns in Canadian Export of “Education-related expenditure” changed over time? If

yes, what are the major changes?

What are the common/different points between the changes in the patterns in Canadian export of

“education-related expenditure” and the changes in the patters in other Canadian service export?

(1-3, but change from “Canadian export to the world” to “Canadian export to the USA”. This

counts as three questions.)

How important is the USA as a trade partner to Canada in this category?

What are the common/different points between the (changes of) the patterns in Canadian export

of “education-related expenditure” to the world and that to the USA.

Hint: As you can see, you can always start from the pattern of one product one partner one year, and

then gradually extend to any (combination) of 1) comparison with other products; 2) comparison with

other partners; and 3) the trend across time.