1.准时将assignment9.xlsx文件提交给Canvas。

1.员工表

2。

3D簇状柱形图：创建显示区域与平均薪水，高薪的对比。如果您创建一个包含每个区域的列，并使用公式在单独的列中为每个区域进行计算，然后将其放入工作区中以填充图表，则操作会变得更加容易。将图表图块更改为“按地区划分的薪水”。将垂直轴标题设置为Salary。

3D饼图：创建显示区域和每个区域的雇员人数。将标题设为“每个区域的雇员”。选择一种显示每个饼图百分比的样式。设置一个单独的工作区，在该工作区中每个区域都有一列，然后在其旁边的列中输入每个区域的雇员数。这将帮助您正确创建饼图

EffectiveRate：（1+𝑟）𝑛− 1

𝑛

Fluency7教科书第13章

Learning Objective:
This assignment is designed to introduce:

1. The basics of worksheets using MS Excel.
2. Creating simple and complex formulas,
3. Formatting for appeal and emphasis.
4. Creating interpretive charts.

To get credit for this assignment:

1. Submit the assignment9.xlsx file to Canvas on time.

Directions:

• Follow the requirements listed below.
• Use formulas to analyze the employee sheet.
• Submit your assignment9.xlsx file to Canvas using the Canvas-> Assignments link.

Requirements:

Formulas: Complete the missing information (yellow cells column U) with formulas. Format the salaries in \$:

1. Use COUNTIF to determine number of females on the employees sheet?
2. Use COUNTIF to determine number of males on the employees sheet?
3. What is the total annualized payroll?
4. What is the average annual salary?
5. What is the highest salary?
6. Use COUNTIF to see how many people got a zero percent pay raise

Page 1 of 3

2.

1. Use SUMIF to sum salary for all employees in the West Region
2. Use AVERGEIF to find the average salary of employees in the Northeast Region:
3. What is the median salary of the entire employee population?
4. In What region is the person with the lowest salary?
5. Use COUNTA to see how many emails are non-blank
6. Use COUNTBLANK to show how many emails are blank
7. Use Column T to display TRUE for all employees born after “1/1/1985” and work in the company for more than 8 years (Use age in company column). Otherwise display “FALSE” for that employee record

Charts (use the columns W and greater next to the answers from the first part of this assignment to show the data and charts)

1. 3D Clustered Column Chart: Create showing regions vs. average salary, high salary. It makes things easier if you create a column with each region and use formulas to calculate for each region in a separate column and put into a work area to feed the chart. Change chart tile to Salary by Region. Set vertical axis title to be Salary.
2. 3D Pie Chart: Create showing regions and number of employees per region. Make the title to be Employees per Region. Pick a style that shows the percentages of each pie slice. Set up a separate work area where you have a column with each region and in the column next to it, put in the number of employees per region. This will help you to create the pie chart properly
3. Line chart: Show line chart with each region and the highest salary per region. You may find it easier to copy the values to a work area to generate the chart. Title of chart is High salary. Vertical axis title is Salary

Enter a formula in the cells indicated yellow background using the formulas listed below.

1. Amortized Loan Payment: 𝐴 = 𝑃 𝑟(1+𝑟)𝑛 (1+𝑟)𝑛−1
2. Area of a Trapezoid: A = 1 h(b1 +b2) 2
3. Volume of a Sphere: 𝑉 = 4 𝜋𝑟3 3
4. Velocity Final: 𝑣𝑓 = √2 × 𝑎 × 𝑑 + 𝑣𝑖2
5. EffectiveRate:(1+ 𝑟 )𝑛 − 1

References:

𝑛

Page 2 of 3

1. Chapter 13 of the Fluency7 textbook
2. Recitations and Lectures

EasyDue™ 支持PayPal, AliPay, WechatPay, Taobao等各种付款方式!

E-mail: easydue@outlook.com  微信:easydue

EasyDue™是一个服务全球中国留学生的专业代写公司