Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

 

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

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

| 1           | 31  | Male   | North     | 5         | 2000         |

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

| 3           | 42  | Male   | East      | 3         | 9000         |

| 4           | 35  | Male   | North     | 8         | 5000         |

| 5           | 25  | Female | South     | 8         | 7000         |

| 6           | 40  | Male   | East      | 3         | 7000         |

| 7           | 31  | Male   | North     | 4         | 5000         |

| 8           | 30  | Female | South     | 8         | 8000         |

| 9           | 45  | Male   | East      | 9         | 5000         |

Task :

1. Create a PivotTable to summarize total amount spent by Gender and Region.
2. Use AVERAGEIF function to calculate the average age of male customers.
3. Calculate the total purchases for each customer using SUMIFS function.
4. Use RANK function to rank customers based on Amount Spent.
5. Create a clustered column chart to compare Amount Spent by Gender and Region.

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

Steps to Perform:

1. Create a PivotTable to Summarize Total Amount Spent by Gender and Region:
   • Insert a PivotTable based on the provided data.
   • Drag "Gender" to the rows area and "Region" to the columns area.
   • Summarize "Amount Spent" in the values area to show total amount spent by each gender in each region.
2. Use AVERAGEIF Function to Calculate Average Age of Male Customers:
   • Use the AVERAGEIF function to calculate the average age of male customers.
   • Example: =AVERAGEIF(B2:B4, "Male", C2:C4) assuming age data is in column B and gender data is in column C.
3. Calculate Total Purchases for Each Customer Using SUMIFS Function:
   • Use the SUMIFS function to calculate total purchases for each customer based on criteria.
   • Example: =SUMIFS(E2:E4, A2:A4, A2) assuming purchases data is in column E and customer IDs are in column A.
4. Use RANK Function to Rank Customers Based on Amount Spent:
   • Use the RANK function to rank customers based on the amount they spent.
   • Example: =RANK(G2, G$2:G$4, 0) assuming "Amount Spent" data is in column G.
5. Create a Clustered Column Chart to Compare Amount Spent by Gender and Region:
   • Select the summarized data from the PivotTable (total amount spent by gender and region).
   • Insert a clustered column chart from the Insert menu in Excel.
   • Customize the chart to display "Gender" and "Region" as categories on the axis and "Amount Spent" as the data series.

Detailed Steps:

• Step 1: Create PivotTable
  • Select the data range including headers.
  • Go to Insert > PivotTable.
  • In the PivotTable Fields pane, drag "Gender" to Rows and "Region" to Columns.
  • Drag "Amount Spent" to Values and ensure it is summarized as Sum.
• Step 2: Use AVERAGEIF Function
  • In a cell where you want to display the average age of male customers, enter =AVERAGEIF(C2:C4, "Male", B2:B4) assuming age is in column B and gender is in column C.
• Step 3: Calculate Total Purchases Using SUMIFS Function
  • In a new column (e.g., F), use =SUMIFS(E2:E4, A2:A4, A2) assuming purchases are in column E and customer IDs are in column A.
• Step 4: Use RANK Function
  • In a new column (e.g., H), use =RANK(G2, G$2:G$4, 0) assuming "Amount Spent" values are in column G.
• Step 5: Create Clustered Column Chart
  • Select the data summarizing "Amount Spent" by Gender and Region.
  • Go to Insert > Column Chart > Clustered Column.
  • Customize the chart title, axis labels, and legend as needed to clearly compare "Amount Spent" by "Gender" and "Region".