CIS107 Homework 1

CMPTR2 Chapter 15: Creating an Advanced Workbook

1 On Your Own 15-2

On Your Own 15-2

1. Open the data file Hours located in

the Chapter 15\On Your Own

folder. Save the workbook as Hours

Template.

2. In the Documentation worksheet,

enter your name in cell B3 and

enter the TODAY function in cell

B4.

3. In the Template worksheet, in cell

A2, enter a formula to display the

author name from the

Documentation worksheet by

typing = and then clicking cell B3

in the Documentation worksheet.

Press the Enter key to complete the

formula.

4. In cell E7, enter a formula to

multiply the Yearly vacation days

(cell E6) by 8 to calculate the

Yearly vacation hours.

5. In cell E9, enter a formula to

multiply the Yearly personal days

(cell E8) by 8 to calculate the

Yearly personal hours.

6. In cell F13, write an IF function

that tests whether the Hours

Worked cell (cell B13) for that row

is blank; if it is, display nothing in

the cell; otherwise, return the sum

of the Hours Worked plus the Other

Hours Used cells (range C13:E13)

for that row to calculate the Total

Hours per Week.

7. In cell G13, create a nested IF

function. First, write an IF function

that tests whether the Hours

Worked cell for that row is blank; if

it is, display nothing in the cell.

Otherwise, write another IF

function that tests whether the

Hours Worked cell (cell B13) for

that row is less than or equal to the

Hours per week amount in cell E5;

if it is, display 0; otherwise,

subtract the Hours per week

amount in cell E5 from the value in

the Hours Worked cell (cell B13)

for that row to calculate the Comp

Hours Earned. (Hint: Be sure to use

matching numbers of opening and

closing parentheses. Be sure to use

absolute references to the Hours

per week cell (cell E5) so that the

On Your Own

CMPTR2 Chapter 15: Creating an Advanced Workbook

2 On Your Own 15-2

value doesn’t change when copied

to other rows.)

8. In cell H13, write an IF function

that tests whether Hours Worked

cell for that row is blank; if it is,

display nothing in the cell;

otherwise, subtract the value in the

Vacation cell (cell C13) for that

row from the Yearly vacation hours

amount in cell E7 to calculate the

remaining Vacation Balance. Be

sure to use an absolute reference to

the Yearly vacation hours cell so

that the value doesn’t change when

copied to other rows.

9. In cell H14, write an IF function

that tests whether Hours Worked

cell for that row is blank; if it is,

display the value from cell H13;

otherwise, subtract the Vacation

Hours Used for that row (cell C14)

from the Vacation Balance in the

previous row (cell H13) to

calculate the current Comp

Balance.

10. In cell I13, write an IF function that

tests whether Hours Worked cell

for that row is blank; if it is, display

nothing in the cell; otherwise,

subtract the value in the Personal

cell (cell D13) for that row from

the Yearly personal hours amount

in cell E9 to calculate the Personal

Balance. Be sure to use an absolute

reference to the Yearly personal

hours cell so that the value doesn’t

change when copied to other rows.

11. In cell I14, write an IF function that

tests whether Hours Worked cell

for that row is blank; if it is, display

the value from cell I13; otherwise,

subtract the Personal Hours Used

for that row (cell D14) from the

Personal Balance in the previous

row (cell I13) to calculate the

current Personal Balance.

12. In cell J13, write an IF function that

tests whether Hours Worked cell

for that row is blank; if it is, display

nothing in the cell; otherwise,

subtract the Comp hours used (cell

E13) for that row from the Comp

Hours Earned (cell G13) for that

row.

13. In cell J14, write an IF function that

tests whether Hours Worked cell

for that row is blank; if it is, display

the value from cell J13; otherwise,

add the Comp Hours Earned (cell

G14) for that row to the Comp

Balance value in the previous row

(cell J13) to calculate the Comp

CMPTR2 Chapter 15: Creating an Advanced Workbook

3 On Your Own 15-2

Balance, and then subtract the

value in the Comp cell (cell E14)

for that row.

14. Copy the formulas in the range

F13:G13 to the range F14:G64, and

then copy the formulas in cell

H14:J14 to the range H15:J64 to

complete the template.

15. Enter values for Week 1 and Week

2 to test that the formulas you

entered work as expected. Correct

any errors you find.

16. Save the workbook, and then close

it.