Fresh features from the #1 AI-enhanced learning platform.Try it free
Fresh features from the #1 AI-enhanced learning platformCrush your year with the magic of personalized studying.Try it free

# 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 / 15
1 / 15
Terms in this set (15)
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.
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.
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.
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