NHL Historic Records
Start with the Excel spreadsheet) NHL Historic Records – start.xlsx.
You will find historic franchise information from 30 current NHL teams. The dataset includes information
about the teams’ years in the league, number of wins/losses/ties, points, and other Playoff statistics.
a) Use conditional formatting to format any cell in column Q with a value greater than or equal to
the threshold listed in cell S2 with a green fill and values less than S2 with a red fill.
b) Next, in column U under the “More than 1,000 Wins” column header, create a binary encoding
to display a 1 if an NHL team has more than 1,000 wins (W) in column G and a 0 otherwise.
c) Next, in column V under the “More than 10 Playoff Appearances” column header, create a
binary encoding to display a 1 if the NHL team has more than 10 playoff appearances (Yrs Plyf) in
column M and a 0 otherwise.
d) Next, in column W under the “Neither” column header, create a binary encoding to display a 1 if
the NHL team has NOT won more than 1,000 games AND has NOT had more than 10 Playoff
appearances (use a 0 otherwise). Use conditional formatting to format any cell in column W that
contains a 1 with black-boldface font and a yellow fill.
e) Next, in column X under the “Either” column header, create a binary encoding to display a 1 if
an NHL team has won more than 1,000 games OR has more than 10 Playoff appearances (use a
0 otherwise). Use conditional formatting to format any cell in column X that contains a 1 with
white-boldface font and a blue fill.
f) Next, in cell Q33, calculate the average of the number of Stanley Cups awarded to the NHL
teams. Format your result with a black border around the cell, bold font, center the value, and
show 0 decimal places.
g) Next, in cell U33 using AVERAGEIFS that calculates the average number of Stanley Cups awarded
to NHL teams provided that the cells in U2:U31 are equal to 1. Use the appropriate relative and
absolute referencing styles so that the formula can be copied or filled to the right in cells V33 to
X33. Finally, format these results with a black border around the cell, bold font, center the value,
and show 0 decimal places
h) Next, copy or fill the formula that was written U33 to cells V33 through X33.
i) Finally, write a formula using the Excel formula COUNTIFS in cell AA6 referencing the original
table (i.e

. columns A through Q) of values and the values that were entered in AA2:AA4. Format
your result with a black border around the cell, bold font, center the value, and show 0 decimal
places.
Now would be a good time to save your work.
Place your order now for a similar paper and have exceptional work written by our team of experts to guarantee you A Results

Why Choose US

6+ years experience on custom writing
80% Return Client
Urgent 2 Hrs Delivery
Your Privacy Guaranteed
Unlimited Free Revisions
Is this question part of your assignment?
Place order
Posted on May 23, 2016Author TutorCategories Question, Questions