$$ 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.

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

$$ Convert the table to a range.

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

$$ Use the Sum function to calculate the subtotals.

$$ Add subtotals to the Funding values only.

$$ Include a summary below the data.

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

$$ Go to the Lookup worksheet, which lists project details, including the ID code that staff in the Development Division use to refer to the projects. Dean wants to find a simple way to look up a project name based on its ID$.$

Create a formula that provides this information as follows:

$$ In cell H$3,$ begin to enter a formula using the VLOOKUP function.

$$ Use the Project ID $($cell H$2)$ as the lookup value.

$$ Use the Lookup table $($range A$2$:E$23)$ as the table$\_$array.

$$ Use the Project Name column $($column $2)$ as the col$\_$index$\_$num.

$$ Specify an exact match $($FALSE$)$ for the range$\_$lookup.

$$ Dean also wants to look up the start date of each project. Instead of using the VLOOKUP function, he suggests using the INDEX and MATCH functions, which are faster with large amounts of data.

Create a formula that provides the start date of a project as follows:

$$ In cell H$4,$ begin to enter a formula using the INDEX function.

$$ Use the Lookup table $($range A$2$:E$23)$ as the array.

$$ For the row$\_$num argument, use the MATCH function.

$$ Use the Project ID $($cell H$2)$ as the lookup$\_$value for the MATCH function.

$$ Use the ID column $($range A$2$:A$23)$ as the lookup$\_$array for the MATCH function.

$$ Specify an exact match $(0)$ for the MATCH function.

$$ Use the Start Date column $($column $4)$ as the column$\_$num for the INDEX function.

$$ Dean also wants to identify the number of projects that have less than $$5,000$ of funding and calculate the average funding amount of commercial projects.

Create formulas that provide this information as follows:

$$ In cell H$8,$ create a formula using the DCOUNT function to count the number of projects with funding amounts less than $$5,000,$ using the Lookup table $($range A$1$:E$23)$ as the database, "Funding" as the field, and the range G$6$:G$7$ as the criteria.

$$ In cell H$13,$ create a formula using the DAVERAGE function to average the funding amounts for Commercial projects, using the Lookup table $($range A$1$:E$23)$ as the database, "Funding" as the field, and the range G$11$:G$12$ as the criteria.