Problem tuition Case Solution (Excel)
3. In the October 31 sheet, format cells A1:A4 with bold, italic, with font size of 14, and merge and center to column H.
4. Any student worker who works 20 hours or less will have the pay applied to his or her tuition. If a student works more than 20 hours (referenced in cell F7), then the student will receive a paycheck for any hours over 20. In cell D11, construct an IF function to determine the number of hours worked that will be applied to the student’s tuition, using cell F7 as an absolute reference. Copy the formula down through D16.
5. In cell E11, construct an IF function to determine the number of hours worked that will be applied to the student’s paycheck, using cell F7 as an absolute reference. Copy the formula down through E16.
6. In cell F11, construct a formula to determine the total pay by multiplying the total hours worked (in cell C11) by the hourly pay (in cell F6). Be sure to use an absolute reference. Copy the formula down through F16.
7. In cell G11, construct a formula to determine the tuition pay by multiplying the Hours Applied to Tuition (in cell D11) by the hourly pay (in cell F6). Be sure to use an absolute reference. Copy the formula down through G16.
8. In cell H11, construct a formula to determine the Gross Paycheck by multiplying the Hours Applied to Paycheck (in cell E11) by the hourly pay (in cell F6). Be sure to use an absolute reference. Copy the formula down through H16.
9. In cell C17, use the AutoSum function to find the total of C11:C16. Copy the formula across through H17.
10. In cells C17:H17, apply a top and double bottom border.
11. In cells F11:H11 and F17:H17, apply the Accounting style format with two decimals. In cells F12:H16, apply the Comma style format with two decimal places.
12. In the October Summary sheet, format cells A1:A3 with bold, italic, with font size of 14, and merge and center to column N.
13. In cell K8, use a formula that refers to the original cell on the October 31 sheet in cell F11. Copy the formula across through column M and then down through row 14.
14. In cell N8, enter a formula that sums the Gross Paycheck amounts for each week. Copy the formula down through N14. Apply a top and double bottom border to cells K14:N14.
15. In cell D17, create an IF function that will determine those student workers who will receive a paycheck and those who will not receive a paycheck. If the amount in the Total
Gross Paycheck is greater than 0, then enter the text Paycheck . If it is not greater than 0, then enter the text None . Copy the formula down through D22.
NOTE: See If Example below.
16. Apply Highlight Cells Rules conditional formatting to the range D17:D22. If the cell has the text Paycheck, highlight the cell with Green Fill with Dark Green Text. If the cell has the text None, highlight the cell with Light Red Fill with Dark Red Text.
17. Apply Highlight Cells Rules conditional formatting to the range N8:N13. If the cell is greater than 0, highlight the cell with Green Fill with Dark Green Text. If the cell is equal to 0, highlight the cell with Light Red Fill with Dark Red Text.
18. On the October 31 worksheet, Apply the Oriel theme to cells A1:H4. Apply the fill color Light Yellow, Background 2. Apply the font color Red, Accent 3.
19. On the October Summary worksheet, in cells A1:N3, apply the fill color Light Yellow, Background 2. Apply the font color Red, Accent 3.
20. With the worksheets grouped, add the file name to the left footer and the worksheet name to the right footer.
21. Change the page layout to landscape, fit to 1 page wide by 1 tall, and center the data horizontally and vertically on the page.
22. Save your workbook.
23. Complete the assignments by compressing folder by:
a. right-clicking on folder
b. move mouse pointer to Send To
c. Click Compressed (zipped) Folder
24. Send to Blackboard by mid-night February 6
(Scroll down for more IF example)
IF Example:
IF(logical_test,value_if_true,value_if_false)
Cell containing value to be tested
Cell containing value to use if TRUE
Cell containing value to use if FALSE
Student Name
Department
Hours Worked
Hours Applied to Tuition
Hours Applied to Paycheck
Jim Brown
Library
18
18
0
Jane Long
Student Union
39
20
19
