Скачать или смотреть New Perspectives Excel 2019 | Module 11: End of Module Project 1

To Get this Solution Contact us on Email and WhatsApp is given in Video
WhatsApp no : +923114151799
Email address: [email protected]

Start by calculating the number projects for clients in each business category. In cell N4, enter a formula using the COUNTIF function that counts the number of projects for clients in the Banking category, checking that the Category column in the Sales table (Sales[Category]) is equal to the value in cell M4. Fill the range N5:N8 with the formula in cell N4.
2. In column O, Nadia wants to calculate the total sales for each category. In cell O4, enter a formula using the SUMIF function that totals the sales for Banking projects, checking that the Category column in the Sales table is equal to the value in cell M4, and that the formula totals the sales for all four quarters (Sales[Total Sales]). Fill the range O5:O8 with the formula in cell O4.
3. In cell P4, enter a formula using the AVERAGEIF function that averages the sales for Banking projects, checking that the Category column in the Sales table is equal to the value in cell M4, and that the formula averages the sales for all four quarters (Sales[Total Sales]). Fill the range P5:P8 with the formula in cell P4.
4. Nadia needs to identify the number of projects that have total sales more than $10,000 and those for client KERRA. In cell N12, create a formula using the DCOUNT function to count the number of projects with total sales of more than $10,000, using the data in the entire Sales table (Sales[#All]) and counting the values in the Total Sales column ("Total Sales") that are equal to the values in the range M10:M11.


#Excel Module 11 End of Module Project 1
#Excel_Module_11_End_of_Module_Project_1
#ExcelModule11EndofModuleProject1
#samsimnetsolution
#Excel Module 11 End of Module Project 1 Qualey Consulting
#Excel_Module_11_End_of_Module_Project_1_Qualey_Consulting
#ExcelModule11EndofModuleProject1Qualey Consulting
#samsimnetsolution
#New Perspectives Excel 2019 Module 11 End of Module Project 1
#New_Perspectives_Excel_2019_Module_11_End_of_Module_Project_1
#NewPerspectivesExcel2019Module11EndofModuleProject1
#samsimnetsolution
#Qualey Consulting
#Qualey_Consulting
#QualeyConsulting
#samsimnetsolution
#CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS
#CREATEADVANCEDPIVOTTABLESANDUSEDATABASEFUNCTIONS
#samsimnetsolution
#NP_EX19_EOM11-1_FirstLastName_1.xlsx
#NP_EX19_EOM11-1_FirstLastName_2.xlsx
#NP_EX19_EOM11-1
#cengage
#np
#samsimnetsolution
#EX19

5. In cell N16, create a formula using the DCOUNTA function to count the number of projects for client KERRA, using the data in the entire Sales table (Sales[#All]) and counting the values in the Client ID column ("Client ID") that are equal to the values in the range M14:M15.
6. In cell N20, create a formula using the DSUM function to calculate the total sales for marketing plans, using the data in the entire Sales table (Sales[#All]) and totaling the values in the Total Sales column ("Total Sales") for services equal to the values in the range M18:M19.
7. In cell N24, create a formula using the DAVERAGE function to calculate the average sales for clients in New Jersey, using the data in the entire Sales table (Sales[#All]) and averaging the values in the Total Sales column ("Total Sales") for clients in states equal to the values in the range M22:M23.
8. Go to the Sales by Category worksheet. Group the Start Date field values by Quarters only. Change the report layout to Compact Form to make the PivotTable look less cluttered.
9. Go to the Sales by Client worksheet. Manually group the CT, NJ, and NY column labels (cells B3, E3, and F3). Use North as the name of the group. Manually group the GA and FL column labels in row 4 and use South as the name of the group.
10. Nadia also wants display the sales by region. Remove the State field from the Columns area. Rename the State2 field to use Region as the custom name. Sort the South values in ascending order to list first the North sales amounts in column B followed by the South sales amounts in column C. Hide the field headers to further streamline the layout.
11. Go to the Sales by Service worksheet, Insert a calculated field named Difference that subtracts the Qtr1 field amount from the Qtr4 field amount. In cell E2, use +/- Q4 to Q1 as the column label.
12. Insert another calculated field named % Difference that subtracts the Qtr 1 field amounts from the Qtr 4 field amounts, and then divides the result by the Qtr 1 field amount. In cell F2, use % Q4 to Q1 as the column label. Change the number format of the % Q4 to Q1 amounts to Percentage with 2 decimal points.
13. Add the Total Sales field to the Values area of the PivotTable, making it the last field in the Values area. Use the Average calculation to summarize the Total Sales field data. Move the Annual field so that it is listed as the last field in the Values area.