Microsoft Office Tutorials and References
In Depth Information
9. In cell G8, calculate the range of scores for the ﬁ rst exam, which is equal to the
difference between the maximum and minimum score.
10. Repeat Steps 6 through 9 for each of the other two exams, the ﬁ nal exam, and the
overall weighted score.
11. Use conditional formatting to highlight the top 10 scores in the range F17:F52 in a
light red ﬁ ll with dark red text.
12. Insert a page break at cell A14, repeat the ﬁ rst three rows of the worksheet in any
printout, and verify that the worksheet is in portrait orientation.
13. Save and close the workbook, and then submit the ﬁ nished workbook to your
instructor, either in printed or electronic form, as requested.
Case Problem 2
to create an
order form for
a i reworks
Data File needed for this Case Problem: Wizard.xlsx
WizardWorks Andrew Howe owns and operates WizardWorks, an online seller of
ﬁ reworks based in Franklin, Tennessee. Andrew wants you to help him develop an order
form for his business. The form needs to contain formulas to calculate the charge for each
order. The total charge is based on the quantity and type of items ordered plus the
shipping charge and the 5 percent sales tax. Orders can be shipped using standard 3- to 5-day
shipping for $3.99 or overnight for $10.99. Andrew is also offering a 4 percent discount
for orders that exceed $200. Both the shipping option and the discount need to be
calculated using formulas based on values entered into the worksheet. Complete the following:
1. Open the Wizard workbook located in the Excel3\Case2 folder included with your
Data Files, and then save the workbook as WizardWorks Order Form .
2. In the Documentation sheet, enter your name in cell B3 and enter the date in cell B4.
3. In the Order Form worksheet, in cell C4, enter the customer name, Kevin Kemper .
In cell C6, enter the order number, 28314 . In the range C9:C13, enter the following
Address 1: 315 Avalon Street
City: Greenﬁ eld
4. In cell C5, enter a function that displays the current date.
5. In the range B20:E22, enter the following orders:
Bucket of Fireworks
Mountain Rockets (Box 20)
6. In cell C15, enter overnight to ship this order overnight.
7. In cell F20, enter an IF function that tests whether the order quantity in cell E20 is
greater than 0 (zero). If it is, return the value of cell E20 multiplied by cell D20;
otherwise, return no text by entering . AutoFill this formula into the range F21:F25. “”
8. In cell F27, calculate the sum of the values in the range F20:F25.
9. In cell F28, enter an IF function that tests whether cell F27 is greater than 200. If it is,
return the value of cell F27 multiplied by the discount percentage in cell F12;
otherwise, return the value 0 (zero).
10. In cell F29, subtract the discount value in cell F28 from the subtotal value in
11. In cell F31, calculate the sales tax by multiplying the after discount value in cell F29
by the sales tax percentage, 0.05.