Question
Excel SAM Module 6 Pt. 4 16. Go to the Lookup worksheet, which lists project details, including the ID code that staff in the Development
Excel SAM Module 6 Pt. 4
16. 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:
a. In cell H3, begin to enter a formula using the VLOOKUP function.
b. Use the Project ID (cell H2) as the lookup value.
c. Use the Lookup table (range A2:E23) as the table_array.
d. Use the Project Name column (column 2) as the col_index_num.
e. Specify an exact match (FALSE) for the range_lookup.
17. 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:
a. In cell H4, begin to enter a formula using the INDEX function.
b. Use the Lookup table (range A2:E23) as the array.
c. For the row_num argument, use the MATCH function.
d. Use the Project ID (cell H2) as the lookup_value for the MATCH function.
e. Use the ID column (range A2:A23) as the lookup_array for the MATCH function.
f. Specify an exact match (0) for the MATCH function.
g. Use the Start Date column (column 4) as the column_num for the INDEX function.
18. 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:
a. In cell H8, 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 A1:E23) as the database, "Funding" as the field, and the range G6:G7 as the criteria.
b. In cell H13, create a formula using the DAVERAGE function to average the funding amounts for Commercial projects, using the Lookup table (range A1:E23) as the database, "Funding" as the field, and the range G11:G12 as the criteria.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started