Excel SAM Module 6 Pt. 3

12. Dean wants to summarize the number of projects proposed by the project type and calculate their funding amounts and average funding amounts. Calculate this information for Dean as follows:

a. In cell J2, enter a formula using the COUNTIF function that counts the number of proposed Commercial projects, using ProposedProjects[Project Type] as the range and cell I2 as the criteria.

b. Fill the range J3:J5 with the formula in cell J2.

c. In cell K2, enter a formula using the SUMIF function that totals the funding for proposed Commercial projects, using ProposedProjects[Project Type] as the range, cell I2 as the criteria, and ProposedProjects[Funding] as the sum_range.

d. Fill the range K3:K5 with the formula in cell K2.

e. In cell L2, enter a formula using the AVERAGEIF function that averages the funding for proposed Commercial projects, using ProposedProjects[Project Type] as the range, cell I2 as the criteria, and ProposedProjects[Funding] as the average_range.

f. Fill the range L3:L5 with the formula in cell L2.

13. In the range I8:L12, Dean needs to insert a summary of the city development projects from the previous year. Insert this data as a table as follows:

a. Insert a table in the range I8:L12, specifying that the data has headers.

b. In the new table, enter the data shown in Table 2.

c. AutoFit the contents of columns I:L to display the complete cell contents.

d. Apply Brown, Table Style Medium 7 to the new table to match the formatting of the ProposedProjects table.

Table 2: Data for the New Table

Project Type	Started	Completed	Funding
Commercial	5	3	45,500
Mixed Use	4	2	57,800
Public	4	3	33,750
Residential	3	3	41,325

14. Go to the Funding Totals worksheet, which lists all the current and proposed development projects. Dean wants to display the data by funding type and then list the projects by start date. Sort the data in the table in ascending order first by funding type and then by start date.

15. Dean also wants to calculate subtotals for each funding type (Hint: You must complete all actions of this step correctly to receive full credit.):

a. Convert the table to a range.

b. Insert a subtotal at each change in the Funding Type value.

c. Use the Sum function to calculate the subtotals.

d. Add subtotals to the Funding values only.

e. Include a summary below the data.

f. Collapse the outline to display only the subtotals for each funding type and the grand total.