CS 385 Exam 5

4.5 (8 reviews)

Get a hint

Use the YEAR and YEARFRAC Functions

1. Insert the YEAR function in cell F9 in the 1-Date Logic worksheet. Use cell E9 for the Serial_number argument.

2. Insert the YEARFRAC function in cell G9 to enter =YEARFRAC(E9,F2) to calculate difference between an employee's hire date and the date in cell F2.

3. Change the reference of F2 in the YEARFRAC function to the mixed reference F$2.

4. Select the range F9:G9, the range containing the YEAR and YEARFRAC functions and use Auto Fill to copy the functions to the range F10:G33. Select Fill Without Formatting to preserve the formatting, including borders.

Click the card to flip 👆

1. Select cell F9 and click the formulas tab. Click Date&Time in the function library group. Select YEAR. Select cell E9 to insert it into the Serial_number argument

2. Click cell G9 in the 1-Date Logic worksheet. On the formulas tab in the Function Library group, click the Date& Time. Select YEARFRAC. In the start date box click cell E9. In the End_Date box select cell F2. Click OK.

3. Edit the formula in the formula bar

4. Select range F9:G9. Double click the fill handle in cell G9. Click the AutoFill Options button. Click fill without formatting.

Click the card to flip 👆

1 / 15

Terms in this set (15)

Use the WEEKDAY Function

1. Insert the WEEKDAY function in cell H9 to return a number representing the day of the week. Use cell E9 as the Serial_number argument and 2 as the Return_type argument.

2. Insert a VLOOKUP function in cell I9 to identify the day of the week for the results of the WEEKDAY function in cell H9. Use the range H$2:I$6 as the table array.

3. Select the range H9:I9, the range containing the WEEKDAY and VLOOKUP functions and use Auto Fill to copy the functions to the range H10:I33. Select Fill Without Formatting to preserve the formatting, including borders.

1. Click cell H9. On the formulas tab in the Function library group, select DATE&TIME, then select WEEKDAY. In the Serial_number box select cell E9. In the return_type box type 2. Click OK.

2. Click cell I9. On the formulas tab in the Function library group, select lookup&reference, then select VLOOKUP. In the Lookup_value box, click cell H9. Press tab to select the Table_Array box and select range H2:I6. (make sure it reads H$2:I$6). Insert 2 in the column box.

3. Select the range H9:I9. Double click the fill handle in I9. Click the Autofill option. Click fill with out formatting.

Use the SWITCH Function

1. Insert the SWITCH function in cell C9 that looks up the expression in cell B9 and switches a city for a region. The function should be =SWITCH(B9,"Atlanta",C$2,"Boston",C$4,"Chicago",C$3,"Cleveland",C$3).
2. Copy the function in cell C9 and paste the function in the range C10:C33 using the Paste Formulas option. Press ESC.

1. Select Cell C9. In the formulas tab, in the library function group, select LOGICAL, scroll to select SWITCH. In the Expression box type B9. In the Value_1 type Atlanta. In the Result_1 Box type C$2. In the default_or_value2 box type Boston, in the Result2 box type C$4. In the default or value3 box type Chicago. In the result3 box type C$3. Continue pattern. click OK

2. Click cell C9 and press copy in the clipboard group. Select range C10:C33 and click the paste arrow. Select formulas. Press escape.

Use the IFS Function

1. Insert an IFS function in cell K9 to compare the hire date in cell E9 to see if it is before the date 1/1/2010 in cell K2. If true, multiply the salary in cell J9 by the 8% bonus rate in cell L2. In the Logical_test2 box, compare the hire date to see if it is before the date 1/1/2015 in cell K3. If true, multiply the salary in cell J9 by the 4% bonus rate in cell L3. In the Logical_test3 box, compare the hire date in cell E9 to see if it is before the date 1/1/2020. If true, multiply the salary in cell J9 by the 2% bonus rate in cell L4. Type TRUE in the Logial_test4 box and 0 in the Value_if_true4 box. Use the mixed references to the references in column K and L. The function should be =IFS(E9<K$2,J9L$2,E9<K$3,J9L$3,E9<K$4,J9*L$4,TRUE,0).

2. Copy the function in cell K9 and paste the function in the range K10:K33 using the Paste Formulas option, and then press ESC.

1. Click cell K9. On the Formulas tab in the Function library group, click Logical, and select IFS.
In the Logical_Test1 box type E9<K$2 then press tab.
In the Value_if_True1 box, type J9*L$2. Press Tab.
In the Logical_Test2 box type E9<K$3. Press Tab
In the Value_if_true2 Box type J9*L$3. Press tab.
In the Logical_Test3 box type E9<K$4. Press tab.
In the Value_if_True3 box type J9*K$4. Press tab
In the Logical_Test4 box type TRUE. Press Tab
In the Value_if_True4 box type 0
Click ok.

3. Copy, select K10:K33, Paste using formula

Nest an AND Function within an IF Function

1. Go to cell L9 and insert an IF function with a nested AND function to display Due for raise to all managers who earn a salary of less than $80,000 and N/A for anyone who does not fit the criteria.

2. Use the fill handle to copy the function down, populating the rest of the Raise Status column. Select Fill Without Formatting to preserve the existing formatting

1. Click cell F9. On the formulas tab in the Library function group, select Logical and then select IF.
In the Logical_Test Box type AND(D9="manager",J9<F$3)
In the Value_if_True box type "Due for raise"
In the Value_if_False box type "N/A"
Click OK.

2. Drag down, select fill wihtout formatting