Exp$22\_$Excel$\_$Ch$07\_$HOE$\_$Salaries

In cell G$7,$ insert the YEARFRAC function using the Hire Date and Last Day of the Year. Use relative and mixed references correctly. Use the fill handle to copy the function in cell G$7$ to the range G$8$:G$23$ using the Fill Without Formatting option. $5$

$4$ You want to identify which day of the week each employee was hired. To do this, you will insert a WEEKDAY function to return an integer representing the weekday.

In cell H$7,$ insert the WEEKDAY function using the Hire Date. Copy the function from cell H$7$ and paste to the range H$8$:H$23$ using the No Borders paste option. $5$

$5$ You will use a custom number format to display the result as a weekday instead of an integer.

Select the range H$7$:H$23,$ apply the custom number format dddd$,$ and apply left horizontal alignment. $3$

$6$ Column B contains the city location for each account rep and manager. However, you also want to display the regions. Atlanta is in the South, Boston is in the Northeast, and Chicago and Cleveland are both in the Midwest. You will use the SWITCH function to identify which region each city is in$.$

In cell C$7,$ insert the SWITCH function to identify the city in cell B$7.$ Switch Atlanta for South $($cell C$2),$ switch Boston for Northeast $($cell C$4),$ switch Chicago for Midwest $($cell C$3),$ switch Cleveland for Midwest $($cell C$3).$ Insert city names as text strings, and use mixed references to the cells containing the regions. Copy the function from cell C$7$ and paste to the range C$8$:C$23$ using the paste Formulas option. $5$

$7$ Your next task is to calculate the annual bonus amount for each employee. The company uses a tiered bonus system that awards a specific percentage of salary based on hire date.

In cell J$7,$ insert the IFS function to test if the Hire Date is before $1/1/2013.$ If so$,$ the bonus is $8\%$ of the salary. If the Hire Date is before $1/1/2018,$ the bonus is $4\%$ of the salary. If the Hire Date is before $1/1/2023,$ the bonus is $2\%$ of the salary. If none of the conditions are met, the bonus is $0.$ Use mixed references to the dates in the range J$2$:J$4$ and bonus percentages in the range K$2$:K$4.$ Copy the function from cell J$7$ and paste it in the range J$8$:J$23$ using the paste Formulas option. $5$

$8$ The HR director recommends that the company pay managers at least $$80,000.$

In cell K$7,$ insert an AND function that evaluates if the the Title is Manager and if the Salary is less than $$98,000.$ Insert the title as a text string and use a mixed reference to the $$98,000$ in cell G$3.$ Nest the AND function as the logical test within an IF function. If the results of the AND function are true, display the text Due for raise. If the results are false, display the text N$/$A$.$ Use the fill handle to copy the function to the range K$8$:K$23$ using the Fill Without Formatting option. $5$

$9$ You want to calculate the number of employees in each state. You set up a worksheet with a summary section to calculate statistics by states, indicated by the state abbreviations.

Display the $2-$Stats Map worksheet. In cell J$3,$ insert the COUNTIF function to count the number of state abbreviations in the State column that match the state abbreviation in cell I$3.$ Use mixed references to the range and relative reference to the criteria state abbreviation. C