Assignment -37 | Sales Data Automation

Excepted timing : 10 minutes

Sample Data

 

| Order ID | Customer Name | Product    | Quantity | Unit Pri | Order Date |

|----------|---------------|------------|----------|---------|------------|

| 1001     | John Doe      | Laptop     | 2        | 1000    | 2023-05-10 |

| 1002     | Jane Smith    | Smartphone | 5        | 500     | 2023-06-15 |

| 1003     | David Brown   | Tablet     | 3        | 700     | 2023-07-20 |

 


Task :
  1. Use VLOOKUP function to retrieve Customer Name based on Order ID from another sheet (lookup table).
  2. Use INDEX and MATCH functions to find Unit Price for Product "Laptop".
  3. Create a dynamic named range using OFFSET function for Quantity column.
  4. Use SUMPRODUCT function to calculate total sales revenue for each product.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

  1. Use VLOOKUP Function to Retrieve Customer Name based on Order ID:
    • Assuming your lookup table with Order ID and Customer Name is in another sheet (e.g., Sheet2Adjust the cell references and sheet names as per your actual setup.
  2. Use INDEX and MATCH Functions to Find Unit Price for Product "Laptop":
    • Assuming Product names are in column C and Unit Prices in column E, Adjust the cell references based on the actual range of your data.
  3. Create a Dynamic Named Range Using OFFSET Function for Quantity Column:
    • Assuming Quantity column starts from D2, define a named range (e.g., QuantityRange) with the formula:

This dynamically adjusts the range based on the number of non-empty cells in the Quantity column.

  1. Use SUMPRODUCT Function to Calculate Total Sales Revenue for Each Product:
    • Assuming Order ID is in column A, Quantity in column D, and Unit Price in column E,

Adjust the ranges to match your actual data structure.

Detailed Steps:

Adjust the sheet name (Sheet1) and column reference as per your actual data.