cs ch 7
Test the newly created search box using employee number 4070.
Click cell B1 and then type 4070 and press enter
Copy the function in cell C9 and paste the function in the range C10:C33 using the Paste Formulas option. Press ESC.
Click cell C9 and press copy in the clipboard group. Select range C10:C33 and click the paste arrow. Select formulas. Press escape.
In cell D8, insert the IPMT function to calculate the current interest for each payment. Ensure that the function returns a positive number and use mixed references where necessary. Copy the IPMT function to the range D8:D55.
Click cell D8, Type =IMPT( and then click the insert function button to the left of the formula bar.In the Rate box type E$3 (Periodic Rate)In the Per box type A8 (payment number)In the Nper box type E$4 (# of payments)In the Pv box type -E$2 (Loan)
In cell E2, calculate the Present Value of an auto loan that has a $450.00 payment, 5.25% annual interest rate, and a 4-year repayment period. The function should return a positive number.
Click cell E2, and then click the formulas tab. In the formulas group select financial, scroll down to select PV.In the Rate box select E3 (Periodic Rates)Click in the Nper box, type E4 (# of payments)Click in the PMT box and type -B2 (Payment)
Calculate the periodic interest rate in cell E3.
Click cell E3 and type =B3/B5 (APR/PMTS per year)
Calculate the total number of payments in cell E4.
Click cell E4 and type =B4B5 (YearsPMTs per year)
In cell E8, insert the PPMT function to calculate the current principal for each payment. Ensure that the function returns a positive number and use mixed references where necessary. Copy the PPMT function to the range E9:E55.
Click cell E8, type =PPMT( click insert function on the formula bar In the Rate box type E$3 (Periodic Rate)In the Per box type A8 (payment number)In the Nper box type E$4 (# of payments)In the Pv box type -E$2 (Loan)
In cell H8, insert the CUMIPMT function to calculate the cumulative interest for the first payment. Convert the CUMIPMT results to a positive number.
Click cell H8, in the formulas tab select financial and then CUMIPMT.In the Rate box type E$3 (Periodic Rate)In the Nper box type E$4 (# of payments)In the PV box type E$2 (Loan)In the Start_Period box type A$8 (payment number)In the End_Period box type A8In the Type box type 0Put a negative sign in front of the function
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.
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.
In cell I8, insert the CUMPRINC function to calculate the cumulative principal for the first payment of the loan. Convert the CUMPRINC results to a positive number.
Click cell I8, in the formulas tab click financial and then CUMPRINC.In the Rate box type E$3 (Periodic Rate)In the Nper box type E$4 (# of payments)In the PV box type E$2 (Loan)In the Start_Period box type A$8 (Payment number)In the End_Period box type A8In the type box type 0Make sure to put a negative sign infront of function
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.
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.
In cell J15, insert the AVERAGEIFS function that calculates the average salary for Account Reps hired before 1/1/2015. Use cell references in the function.
More functions, averageif, type F2:F26 tab D2:D26 tab H9 tab E2:E26 tab <1/1/2015
In cell J16, insert the MAXIFS function that calculates the highest salary for Account Reps hired before 1/1/2015. Use cell references in the function.
More functions, maxifs type F2:F26 tab D2:D26 tab H9 tab E2:E26 tab <1/1/2015
In cell J17, insert the MINIFS function that calculates the lowest salary for Account Reps hired before 1/1/2015. Use cell references in the fu
More functions, minifs, type F2:F26 tab D2:D26 tab H9 tab E2:E26 tab <1/1/2015
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).
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
Select the non-adjacent ranges I2:I6 and K2:K6 in the 2-Stats Map worksheet and insert a Filled Map.Type Payroll by State as the chart title and click the chart area.Cut the selected map and paste it in cell M1.
Select I2:I6 and K2:K6 at the same time. Click insert. In the chart group select MAP and then click filled map.
In cell J14, insert the SUMIFS function to calculate the total salaries for Account Reps hired before 1/1/2015. Use cell references in the function.
Select SUMIFS.Type F2:F26 in the sum_range1 box(Salaries).In criteria_range 1 box type D2:D26In criteria 1 box type H9In criteria_range2 type E2:E26In criteria2 type <1/1/2015
Go to cell F1 and insert a function that will look up the employee ID in cell B1 and return that employee's current Title from column D.
Select cell F1. On the formulas tab in the Function library group, click Lookup&Reference, click INDEX. In the Select Arguments dialog box, click ok. In the Array box type A7:I26In the Row_num box type MATCH(B1,A7:A26,0)Click the Column_num and type 4
In cell J13, insert the COUNTIFS function that counts the number of Account Reps hired before 1/1/2015. Use cell references in the function.
Select cell J13. oN THE FORMULAS TAB CHOOSE MORE OPTIONS, Point to statistical, choose COUNTIFS. In the criteria_range box select D2:D26. In criteria1 choose H9. In the criteria_range2 box select E2:E26. in the criteria2 box type <1/1/2015. click ok
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.
Select range F9:G9. Double click the fill handle in cell G9. Click the AutoFill Options button. Click fill without formatting
Copy the function in cell F8 to the range F9:F55.Select the range C8:E56 and then use the SUM function to enter totals in the range C56:E56 at the same time.
Select range, and click autosum in formulas
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.
Select the range H9:I9. Double click the fill handle in I9. Click the Autofill option. Click fill without formatting.
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.
Type =YEARFRAC(E9,F2) in G9
In cell J3, insert the COUNTIF function to calculate the number of employees who work in Georgia as indicated by the state abbreviation GA. Use mixed cell references in C$2:C$26 for the range. Use cell I3 as the criteria.
With J3 selected, click the formulas tab. Click More functions, point to statistical, scroll to, and then click COUNTIF. Select C2:C26, and change to C$2:C$26 tab Select I3. Ok
Change the reference of F2 in the YEARFRAC function to the mixed reference F$2.
change to =YEARFRAC(E9,F$2)
Insert the YEAR function in cell F9 in the 1-Date Logic worksheet. Use cell E9 for the Serial_number argument.
formula tab, date and time, year, click E9, ok.
In cell F8, create a formula to subtract the value in cell E8 from cell B8.
=B8-E8
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).
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 TabIn 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*L$4. Press tabIn the Logical_Test4 box type TRUE. Press TabIn the Value_if_True4 box type 0 tab click ok
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.
Click cell L9. 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.
In cell K3, insert the SUMIF function to calculate the total salaries of employees who work in Georgia (GA). Use mixed cell references in C$2:C$26 for the range. Use cell I3 as the criteria. Use mixed cell references in F$2:F$26 for the sum_range.
Click on Math and Trig in formula group, use C$2:C$26 AS RANGE. Cell I3 as Criteria. Sum_Range is F$2:F$26
In cell J9, insert the AVERAGEIF function to calculate the average salary for Account Reps Use mixed references for D$2:D$26 and F$2:F$26. Use cell H9 for the criteria.
Click on more functions, statistical, averageif. Type D$2:D$26 tan click H9 tab type F$2:F$26. Ok
Copy the function in cell K9 and paste the function in the range K10:K33 using the Paste Formulas option, and then press ESC.
Copy, select K10:K33, Paste using formula
Copy the COUNTIF function in cell J3 to the range J4:J6 to count the number of employees in the other states.
Drag down
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
Drag down, select fill without formatting
