Microsoft Office Tutorials and References

In Depth Information

**Case Problems**

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

Use formulas

and functions

to create an

order form for

a i reworks

company.

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:

Address 1:
315 Avalon Street

City:
Greenﬁ eld

State:
IN

Zip:
46140

4. In cell C5, enter a function that displays the current date.

5. In the range B20:E22, enter the following orders:

Item

Name

Price

Qty

BF001

Bucket of Fireworks

$45.75

1

NAF

Nightair Fountain

$12.95

4

MR20B

Mountain Rockets (Box 20)

$55.25

2

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

cell F27.

11. In cell F31, calculate the sales tax by multiplying the after discount value in cell F29

by the sales tax percentage, 0.05.