Excel Homework 2

Exercise 1

Type in the following spreadsheet, and format it to look like the sample below.

Instructions

1) Type in all text and numbers shown in the spreadsheet below (two decimal)

2) Format all numbers as a currency.

3) Center the spreadsheet heading 'Pay Book of Company abc' across the spreadsheet.

4) Format all text as displayed in the sample below.

5) Create formula to fill all blank cells.

6) Sort the table by annual net pay (min to max)

Exercise 2

Create a spreadsheet and use scatterplot to plot functions f(x), g(x) and h(x) vs ‘x’ in one graph. ‘x’ is the set of all integers from -10 to 10 including -10 and 10.

𝑓(𝑥) = 𝑥2 + 3𝑥 + 5

𝑔(𝑥) = −𝑥2 + 2𝑥 − 11

ℎ(𝑥) = 4𝑥

The graph should include title and label for horizontal and vertical axis.

Exercise 3

Below are data for the median family income in the United States by Race and Hispanic Origin, for the years 1972-1987. The data are in current dollars.

1. Convert the current dollar figures into constant dollars (i.e., control for inflation). The formula for converting constant dollars into current dollars is:

constant dollars = current dollars/(CPI-U * 0.01)

Calculate constant dollars for each of the categories: All Families, White, Black, and Hispanic. The CPI-U figures have a 1982-84 as the base year. Therefore, the constant dollars will be in 1982-84 dollars.

2. Create one line graph to compare the trends for the three racial/ethnic groups (i.e. White, Black, Hispanic). Use the constant dollar figures.

3. Create a memo in Word comparing the trend for median family income for the three groups over the past 15 years. Import your graph into your Word memo.

Save your documents with the filename: INCxxx (where xxx are your initials).

Note: You should save two files. One should be an EXCEL document and the other should be a

Word document.

Median Family Income by Race and Hispanic Origin, 1960-87 (in current dollars)

Exercise 4

The table below shows the blood glucose level for six subjects of different ages.

· Plot a Scatter Chart to show the variation of glucose level (Y) against age (X). Make sure to add Axis Titles and name them properly.

· Calculate the Pearson correlation coefficient, r, for the data.

· Determine the Direction and Strength of the correlation.

· Based on the correlation coefficient and the scatter chart, write a brief statement (2-3 sentences) describing the general relationship between age and the glucose level.