Objectives

$1.$ Use SUM, AVERAGE, MEDIAN, MIN, MAX, COUNT, and COUNTA functions.

$2.$ Create simple formulas.

$3.$ Reinforce formatting of numbers, borders, date and time, and color$-$fill.

$4.$ Display formulas and rename sheets.

$5.$ Insert Header$/$Footers

Lesson Instructions

Background: You will be completing a Product Inventory worksheet for the Titan Off$-$Campus Shops. The

worksheet you will use is the L$2\_$Titan$\_$OffCampus file. Open it and complete the following instructions.

$($Note: when no specific cell reference is given, you are to use your best judgement based upon how the

worksheet is set up for you to decide what cells are involved or where a result should be placed.$)$

$1.$ Re$-$save the file to either your desktop or other storage device using the name

$$firstName$\_$lastName$\_$L$2\_$Titan$\_$OffCampus$.($Note firstName and LastName are your own first and

last names$).$

$2.$ Create a formula which calculates the Total Retail Value for Category $1001-$SS and copy the formula

down for the other categories $($quantity $*$ retail price$).$

$3.$ Use a function to calculate the total for the Quantity column and the Total Retail Value column $($in row

$30).$

$4.$ Type in Today$$s date next to the Inventory Date cell $($E$4).$

$5.$ In the summary area at the top, do the following next to the appropriate labels:

a$.$ Using a function, count the Number of Categories $($note$,$ use column A in your function$).$

b$.$ Using a function, calculate the average Retail Price.

c$.$ Using a function, calculate the median Retail Price.

d$.$ Using a function, calculate the Highest Retail Price.

e$.$ Using a function, calculate the Lowest Retail Price.

$6.$ Re$-$name the sheet tab to $$Product Inventory$.$

$7.$ Insert a footer that will appear on the left which will include the current date, and the worksheet

name.

$8.$ Format as follows:

a$.$ Right$-$align the labels in cells B$4-$B$6$ and D$4-$D$6$

Lesson $2,$ P a g e $|2$

b$.$ Fill in the rectangle of cells between B$4-$E$6$ with the White, Background $1,$ Darker $5\%$ color.

c$.$ Put borders around the above rectangle, also under the column headings, and below the last

row of products, and a double$-$border below the total.

d$.$ Using the Accounting Style, format only the sales numbers that appear on the top rectangle of

summary values, the top row of the product inventory list $($row $9),$ and the total$$s row $($do not

use Accounting Style for non$-$currency type numbers$).$ Format remaining sales numbers to

Comma Style. Format all Quantity values $($including those in the summary rectangle$)$ to Comma

Style with $0$ decimal places.

e$.$ Format the date to display month, day, and year as two digits eac