Assignment -17 | Customer Segmentation and Analysis

Excepted timing : 10 minutes

| Customer ID | Age | Gender | Region    | Purchases | Amount Spent |

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

| 1           | 35  | Male   | North     | 5         | 5000         |

| 2           | 28  | Female | South     | 8         | 7000         |

| 3           | 40  | Male   | East      | 3         | 4000         |


Task :
  1. Create a table to store customer data with columns for Customer ID, Age, Gender, Region, Purchases, and Amount Spent.
  2. Calculate the average age of customers using the AVERAGE function.
  3. Use COUNTIF function to count the number of male and female customers.
  4. Create a PivotChart to visualize total purchases by region.
  5. Use slicers in the PivotChart to filter data by age group and gender.

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

Steps to Perform:

  1. Create a Table for Customer Data
    • Set up a table in Excel with columns for Customer ID, Age, Gender, Region, Purchases, and Amount Spent.
    • Enter the provided sample data into this table.
  2. Calculate Average Age of Customers
    • Use the AVERAGE function to calculate the average age of customers based on the Age column in your table.
    • Example formula: =AVERAGE(B2:B4) where B2 represents the range of cells containing ages.
  1. Count Male and Female Customers using COUNTIF
    • Utilize the COUNTIF function to count the number of male and female customers based on the Gender column.
    • Example formula for counting males: =COUNTIF(C2:C4, "Male")
    • Example formula for counting females: =COUNTIF(C2:C4, "Female")
  2. Create a PivotChart for Total Purchases by Region
    • Select the table data including headers.
    • Go to the "Insert" tab in Excel and click on "PivotChart" (or "PivotTable" then "PivotChart").
    • In the PivotChart Fields pane, drag Region to Axis (Categories) and Purchases to Values (Sum of Purchases).
    • Excel will generate a PivotChart showing total purchases by region.
  3. Use Slicers to Filter Data by Age Group and Gender
    • Click on the PivotChart to select it.
    • Go to the "Insert" tab and click on "Slicer" under the "Filters" group.
    • Choose Age and Gender from the available fields to create slicers.
    • Use the slicers to dynamically filter the PivotChart data by different age groups and genders.