Reference no: EM131746442
1. Download the spreadsheet named 'Student Excel Assignment Spring 2017.xlsx' from D2L
2. In the worksheet named 'VLOOKUP' do the following below the Sporting Goods Inventory List:
a. Write an 'INDEX' formula to find the value at the intersection of the 4th row and the 6th column in the Inventory table.
b. Write a'VLOOKUP'formula to find the Reorder Quantity for the Basketball product.
HINT: Before creating the above formulas define the appropriate data array/named range for the Inventory table
3. In the worksheet named 'HLOOKUP' do the following:
a. Inserting the appropriate 'HLOOKUP' formula for Price per Unit and figure out the sales total for the first two transactions of July.
HINT: Before creating the formula, define the appropriate data arrays/named ranges for both the ITEM and COSTSANDPRICES tables.
4. In the worksheet named 'SUMIFS, etc.' do the following:
a. Write a 'COUNTIFS' formula to count the total number of observations that have a customer number greater than "101" and are also located in the "SW" sales region.
b. Write a 'SUMIFS' formula to summarize the total sales for customers that are in the "SE" sales region and that occurred in the last two quarters of the year.
5. In the worksheet named 'PIVOTTABLE' do the following:
a. Using the customer data, create a pivot table where you can find the total sales by customer name based on a certain sales region. Please put this pivot table in a new worksheet in the same spreadsheet.
6. Please add your first name and last name to the file name and save the spreadsheet. For instance, I would save the spreadsheet file as 'Student Excel Assignment(MarcusCaylor).xlsx'
7. Upload this file to the Excel Demo Assignment folder in D2L in order to receive credit for the assignment.