Open the Instruments Worksheet$$ Home

Insert

Draw Page Layout

Formulas

Data

Review

View

Automate

$$

$x$

Paste

Calibri $($Body$)$

$11$

$A^A{?}^{?}$

$u$

$=$del

$\frac{ab}{c}$

Wrap Text

Date

B

A$.$

Merge & Center $$

$

$\%$

$9$

$5$

$\mathrm{.}00$

$-10$

Conditional Format

Cell

H $2$ Instruments

Rental

Rental Information

Rental Report

Ready

Accessibility: Investigate

$1)$ In cell H$2,$ create a function to add the text Gold Customer if the Rental Period is $9,$ and Insurance Coverage is Yes. If the condition is not met the cell should be blank.$$

$2)$ In cell I$2,$ create a function to put the value, Brass in the cell if the instrument is either a Trombone or a French Horn, and put the value, Woodwind if the instrument is either a Flute, Clarinet, or Oboe, and blank if either condition is not met.$$

$$Rental Data Worksheet $$ the formula is created in column I, once you create the formula for column H the correct answers will appear.$$

$3)$ In column H$,$ create a lookup function to lookup the Instrument and return the group code from the Instrument Group Array in the Rental Information sheet. This is an exact match. Copy the formula down.$$

$4)$ In columns H and I, add the IFERROR function to the lookup function that states, Instrument is not listed.$$ Copy the formula down$$

$5)$ In column J$,$ create an If lookup to lookup the insurance cost. The insurance cost is based on the group code. If they have insurance coverage, lookup the value and $*$ it by rental period, if they don$$t have insurance $0$ should be entered in the cell. Hint, the monthly insurance is located in column D on the Rental Information worksheet.$$

$6)$ In column K$,$ create a nested IF function to find the shipping cost based on the shipping code.$$Pickup $=0$Ground $=25$Other $($Rush$)=50$

$7)$ In column L$,$ create a formula to find the Total Cost. Rental $ $+$ Insurance Cost $+$ Shipping Cost$$

$8)$ In column M$,$ pull out the month for the rental date. You will get the number representing the month.$$

$9)$ In column N$,$ Combine Columns A and B; make sure you put a space between the first and last name. Widen the column to fit.$$

$10)$ In column O$,$ change the instrument name to Upper Case. Widen the column to fit.$$

$11)$ Create a Pivot table to resemble the diagram below $($don$$t worry if your numbers do not match$),$ place it on a separate sheet:

$12)$ Return to the rental data worksheet and add subtotals to subtotal the Total Cost by Group Code$$Open the Rental Report Worksheet$$

$13)$ In cell C$4,$ Count the number of rentals that are $3$ months and have insurance coverage. $$

$14)$ In cell C$5,$ Count the number of rentals that are $9$ months and have insurance coverage.$$

$15)$In cell D$4,$ Sum the Total rental price for the rentals that are $3$ months and have insurance coverage.

$16)$ In cell D$5,$ Sum the Total rental price for the rentals that are $9$ months and have insurance coverage.

$17)$ Apply a background color and change the font color of cells A$1$:D$1$

$18)$ Wrap the text in D$3$

$19)$ Merge cells A$1$:D$1.$

$20)$ Apply the currency style to cells D$6$