# CS101 Infection Rate Charts i upload the excel files that you will work on it , and the instructions. please follow the instructions and be careful HOMEWOR

CS101 Infection Rate Charts i upload the excel files that you will work on it , and the instructions. please follow the instructions and be careful HOMEWORK INSTRUCTIONS

Homework #2

HIV Statistics Problem

Background Information

The scourge of HIV/AIDS has had an extraordinary impact on the entire

world. The spread of the disease has been closely tracked since the

discovery of the HIV virus in the 1980s. Since that time, many millions of

people have died from AIDS.

While there are now pharmaceuticals available to help manage HIV/AIDS,

the disease is still extremely deadly and enormously expensive to treat. As the data

presented in this assignment shows, the future of an HIV-infected person can be

directly impacted by the part of the world in which they live.

Problem Statement

In this assignment, students will analyze the growth of HIV/AIDS throughout the

world using UNAIDS and World Health Organization statistics. They will also look at

the relationship between economic conditions and the spread of the epidemic.

Instructions

IMPORTANT: Complete the steps below in the order they are given. Completing the

steps out of order may complicate the assignment or result in an incorrect result.

1.

Download and extract the provided Data Files ZIP file. It contains the following

files for use in this assignment:

a.

hivstatistics.csv – Statistics on living with HIV for regions of the world

between 2004 and 2014 [1]–[5].

Column Name

Region

2004

Type

Text

Number

2006

Number

2008

Number

2010

Number

2012

Number

2014

Number

Introduction to Computer Applications

West Virginia University

Page 1 of 7

Description

Name of region.

Estimated number

in 2004.

Estimated number

in 2006.

Estimated number

in 2008.

Estimated number

in 2010.

Estimated number

in 2012.

Estimated number

in 2014.

of people living with HIV

of people living with HIV

of people living with HIV

of people living with HIV

of people living with HIV

of people living with HIV

Version 21.4

Modified 5/23/2018

HOMEWORK INSTRUCTIONS

Homework #2

HIV Statistics Problem

b.

infections.csv – Statistics on new HIV infections for regions of the world

between 2004 and 2014 [1]–[4], [6].

Column Name

Region

2004

Type

Text

Number

2006

Number

2008

Number

2010

Number

2012

Number

2014

Number

Description

Name of region.

Estimated number

with HIV in 2004.

Estimated number

with HIV in 2006.

Estimated number

with HIV in 2008.

Estimated number

with HIV in 2010.

Estimated number

with HIV in 2012.

Estimated number

with HIV in 2014.

2.

Create a new Microsoft Excel workbook named

lastname_firstname_hw2_hivsp.xlsx.

3.

We must adjust the sheets in our workbook.

4.

5.

a.

Rename Sheet1 to HIV Statistics.

b.

Add a new sheet named New Infections.

c.

Add a new sheet named Infection Rates.

d.

Add a new sheet named Analysis Questions.

of people newly infected

of people newly infected

of people newly infected

of people newly infected

of people newly infected

of people newly infected

Import the following items into the workbook:

a.

hivstatistics.csv file – Import starting in cell A3 of the HIV Statistics

sheet. The file is comma-delimited. Its first row contains headers.

b.

infections.csv file – Import starting in cell A3 of the New Infections

sheet. The file is comma-delimited. Its first row contains headers.

We wish to apply formatting to the HIV Statistics sheet.

a.

We must setup a table to store data on HIV.

i.

If a table does not already exist in cells A3 through G9, create one

using a style of your choice. The table has headers and will overlap

external data ranges. If prompted, convert the selection to a table

and remove all external connections.

ii.

If a table already exists in cells A3 through G9, format the table

using a style of your choice other than the default table style.

Introduction to Computer Applications

West Virginia University

Page 2 of 7

Version 21.4

Modified 5/23/2018

HOMEWORK INSTRUCTIONS

Homework #2

HIV Statistics Problem

b.

We need to add additional table columns to store statistics and a

sparkline.

i.

c.

Enter text in the cells as indicated below:

i.

A1: People Living with HIV

ii.

H3: Minimum Value

iii.

I3: Maximum Value

iv.

J3: Infected Increasing

v.

K3: Sparkline

d.

Merge-and-center cells A1 through K1.

e.

Apply the Title cell style to cell A1.

f.

Format the cells as indicated below:

i.

6.

Insert four new table columns to the right of existing column G.

B4 through I9: number with no decimal places, use 1000 separator

g.

AutoFit the width of columns A through K.

h.

Apply Red-Yellow-Green color scale conditional formatting to cells B4

through G9.

We also wish to apply formatting to the New Infections sheet.

a.

b.

We must setup a table to store data on new infections.

i.

If a table does not already exist in cells A3 through G9, create one

using a style of your choice. The table has headers and will overlap

external data ranges. If prompted, convert the selection to a table

and remove all external connections.

ii.

If a table already exists in cells A3 through G9, format the table

using a style of your choice other than the default table style.

Enter text in the cells as indicated below:

i.

A1: New HIV Infections

c.

Merge-and-center cells A1 through G1.

d.

Apply the Title cell style to cell A1.

e.

Format the cells as indicated below:

i.

f.

B4 through G9: number with no decimal places, use 1000 separator

AutoFit the width of columns A through G.

Introduction to Computer Applications

West Virginia University

Page 3 of 7

Version 21.4

Modified 5/23/2018

HOMEWORK INSTRUCTIONS

Homework #2

HIV Statistics Problem

7.

We want must also apply formatting to the Infection Rates sheet.

a.

Enter text in the cells as indicated below:

i.

A1: HIV Infection Growth Rates

ii.

A3: Region

iii.

B3: 2004

iv.

C3: 2006

v.

D3: 2008

vi.

E3: 2010

vii.

F3: 2012

viii. G3: 2014

ix.

A4: Asia and the Pacific

x.

A5: Eastern Europe and Central Asia

xi.

A6: Latin America and the Caribbean

xii.

A7: Middle East and North Africa

xiii. A8: Sub-Saharan Africa

xiv. A9: Western and Central Europe and North America

8.

b.

Merge (but not center) cells A1 through G1.

c.

Apply the Title cell style to cell A1.

d.

Format cells A3 through G9 as a table using a style of your choice. The

table has headers.

On the Infection Rates sheet, we wish to calculate infection statistics.

a.

In column B, write a formula to calculate, for each region, the annual

infection growth rate. Reference the values on the HIV Statistics and New

Infections sheet in your formula.

You can calculate the infection growth rate using the formula:

[ ]

[ ℎ ]

b.

9.

In columns C through G, duplicate your infection growth rate formula

from column B.

We must apply additional formatting to the Infection Rates sheet.

a.

Format the cells as indicated below:

i.

B4 through G9: percentage with 1 decimal place

Introduction to Computer Applications

Page 4 of 7

West Virginia University

Version 21.4

Modified 5/23/2018

HOMEWORK INSTRUCTIONS

Homework #2

HIV Statistics Problem

b.

AutoFit the column width for columns A through G.

10. On the HIV Statistics sheet, we wish to calculate people living with HIV

statistics.

a.

In column H, write a formula to calculate the minimum number of people

living with HIV in each region.

b.

In column I, write a formula to calculate the maximum number of people

living with HIV in each region.

c.

In column J, write a formula to determine if the region’s 2014 number of

people living with HIV was greater than its 2004 number. If it was, display

Increasing; otherwise, display Not Increasing.

11. We wish to add a sparkline to the HIV Statistics sheet to compare data.

In column K, insert a line-type sparkline based on the values in columns B

through G.

12. We wish to create a chart to plot the infection growth rate in all regions for

each year.

a.

Create a 2-D line chart based on cells A3 through G9 of the Infection

Rates sheet. Move the chart to a new sheet named Infection Rates Chart.

Ensure the years are shown as labels for the horizontal (category) axis,

not plotted as chart data. Specify appropriate chart and axis titles.

b.

Add a trendline based on the infection rate for the Western and Central

Europe and North America. Use the trendline type that best fits the data

and project the values forward 5 periods (through the year 2024). Display

the R-squared value on the chart.

NOTE: You cannot use the Moving Average type for your trendline.

13. We need to set up the Analysis Questions sheet so that it can store responses

to the analysis questions.

a.

Enter text in the cells as indicated below:

i.

A1: Question Number

ii.

B1: Response

b.

Bold the contents of row 1.

c.

AutoFit the width of column A. Set the width of column B to 100 (8.39”).

d.

Set the height for rows 2 through 5 to 110 (1.53”).

e.

Change the vertical alignment setting for columns A and B so that the

text is displayed at the top of each row.

Introduction to Computer Applications

West Virginia University

Page 5 of 7

Version 21.4

Modified 5/23/2018

HOMEWORK INSTRUCTIONS

Homework #2

HIV Statistics Problem

f.

Turn on text wrapping for column B.

14. Starting in row 2 of the Analysis Questions sheet, answer four of the five

analysis questions below. Respond to one question per row.

a.

Which trendline type did you use on Infection Rates Chart? Why did you

choose this type of trendline?

b.

What sort of factors might cause the HIV infection growth rate to increase

or decrease in the future?

c.

Why might the number of reported HIV cases not be an accurate

reflection of how many people actually have HIV?

d.

Looking at the regional HIV statistics, what sort of relationships can you

deduce between an area’s general economic condition and its new

infection rates? Why might this relationship exist?

e.

What sort of effect might the high prevalence of HIV have on the

demographics and economy of Sub-Saharan Africa in the future?

Grading Rubric

This assignment is worth 50 points. It will be graded by your instructor using this

rubric, with partial credit awarded as appropriate:

Steps 3a-d

Step 4

Steps 5a-g

Step 5h

Steps 6a-f

Steps 7a-d

Steps 8a-b

1.5 points total

3 points

3.5 points total

2.5 points

3 points total

3 points total

4.5 points total

Steps 9a-b

Steps 10a-c

Step 11

Step 12a

Step 12b

Steps 13a-f

Steps 14a-e (pick 4 of 5)

1.5 points total

4.5 points total

3 points

4 points

3 points

3 points total

2.5 points each

The analysis questions in Steps 14a-e will be evaluated using this rubric:

Standard

Answer is reasonable.

Answer is supported.

Meets Requirements

(1.25 points)

Answer addresses the

question prompt and is

factually correct or a

reasonable interpretation of

available data.

Logical rationale is provided

to support the given

answer.

Does Not Meet

Requirements (0 points)

Answer does not address

the question prompt, is

factually incorrect, or is an

unreasonable interpretation

of available data.

Logical rationale is not

provided to support the

given answer.

Acknowledgments

The image in the introduction appears courtesy of Amada44 [7].

Introduction to Computer Applications

Page 6 of 7

West Virginia University

Version 21.4

Modified 5/23/2018

HOMEWORK INSTRUCTIONS

Homework #2

HIV Statistics Problem

References

[1] “AIDS epidemic update,” UNAIDS, Dec. 2006. Available:

http://data.unaids.org/pub/EpiReport/2006/2006_EpiUpdate_en.pdf.

[2] “AIDS epidemic update,” UNAIDS, 2009. Available:

http://data.unaids.org/pub/report/2009/jc1700_epi_update_2009_en.pdf.

[3] “UNAIDS World AIDS Day Report,” UNAIDS, 2011.

http://www.unaids.org/en/media/unaids/contentassets/documents/unaidspublic

ation/2011/JC2216_WorldAIDSday_report_2011_en.pdf.

[4] “UNAIDS report on the global AIDS epidemic 2013,” UNAIDS, 2013. Available:

http://www.unaids.org/sites/default/files/media_asset/UNAIDS_Global_Report_

2013_en_1.pdf.

[5] “AIDS by the numbers 2016.” UNAIDS. Available:

http://www.unaids.org/en/resources/documents/2016/AIDS-by-the-numbers.

[6] “AIDS by the numbers 2015.” UNAIDS. Available:

http://www.unaids.org/en/resources/documents/2015/AIDS_by_the_numbers_

2015.

[7] Amada44, World AIDS Day Ribbon. 2007. Available:

https://commons.wikimedia.org/wiki/File:World_Aids_Day_Ribbon.svg.

Introduction to Computer Applications

West Virginia University

Page 7 of 7

Version 21.4

Modified 5/23/2018

Purchase answer to see full

attachment