BITM 215

Insert an IF function in cell E5 to calculate the total due. If the student has chosen to personalize the ring, there is an additional charge of 5% located in cell B21 that must be added to the cost of the ring; if not, the student only pays the base price. Use appropriate relative and absolute cell references.

Formula - Logical - IF Logical_test - Specific Cell(D5) = "Yes" Value_if_true - C5 + (C5*B21) (click specific cells) Highlight B21 and click F4 Value_if_false - C5

Insert a VLOOKUP function in cell C5 to display the ring cost for the first student. The cost of the ring type should be an exact match. Use appropriate relative and absolute cell references.

Formula - Lookup & References - VLOOKUP Lookup_Value - Click first student ring type Table_Array - A16:B19 then click and choose F4 for the addition of a $ Col_index_num - 2 Range_Lookup - FALSE

Insert a function in cell B2 to display the current date from your system.

Formulas - Date and Time - TODAY - Press Ok

With cell B2 selected, set the width of column B to AutoFit.

Home - Format - Auto Fit Column Width

Insert a function in cell G5 to calculate the first student's monthly payment, using appropriate relative and absolute cell references. Ensure the result is a positive number.

Formula - Financial - PMT Rate - Click B22 then F4 divide by 12 (cause of monthly payment) Nper - F5*12 Pv - Insert a neg sign [ - ] then click E5 Then pick Ok

Set 0.3" left and right margins and ensure the page prints on only one page.

Page Layout - Margin - Custom Margin

Apply Accounting number format to the range C5:C11

Click the %

Calculate totals in cells C12, E12, and G12.

Click the three columns then AutoSum (E looking symbol)