Scribd
Upload
63 views2 pages

Excel - Day 8 Assignment - Statistical Function

The Excel assignment focuses on analyzing car sales data using various conditional and statistical functions. Students are required to create a dataset with specific columns and perform tasks such as counting, summing, and averaging data based on given criteria. The final deliverable includes a filled Excel file with formulas, highlighted results, and a brief conclusion on insights gained.

Uploaded by

Ayush Kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
63 views2 pages

Excel - Day 8 Assignment - Statistical Function

The Excel assignment focuses on analyzing car sales data using various conditional and statistical functions. Students are required to create a dataset with specific columns and perform tasks such as counting, summing, and averaging data based on given criteria. The final deliverable includes a filled Excel file with formulas, highlighted results, and a brief conclusion on insights gained.

Uploaded by

Ayush Kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Excel Assignment: Analyzing Car Sales

Data Using Excel Functions


Objective:
To understand and apply conditional and statistical Excel functions such as COUNTA,
COUNTBLANK, COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS to
analyze car sales data effectively.

Dataset Setup:
Create an Excel sheet named “CarSalesData” with the following columns:

Car ID | Brand | Region | Salesperson | Units Sold | Price per Unit | Total Sale | Sale Month.

The car brands are Maruti, Hyundai, Honda and Toyota.

The regions are East, West, North & South.

The months are January, February & March.

- Enter at least 15 sample records.


- Compute Total Sale = Units Sold × Price per Unit.

Section A: Counting Data


1. Use COUNTA to count:

• - The number of non-empty entries in the Brand column.


• - The number of sales records available (use any consistent column).

2. Use COUNTBLANK to:

• - Count how many entries are missing in the Salesperson column.

3. Use COUNTIF to:

• - Count how many cars were sold in the "North" region.


• - Count how many sales had Units Sold > 5.

4. Use COUNTIFS to:

• - Count how many Toyota cars were sold in the South region.
• - Count how many cars were sold by a specific Salesperson with Units Sold > 3.
Section B: Summing Data
1. Use SUMIF to:

• - Calculate total Units Sold for Brand = “Hyundai”.


• - Calculate total Total Sale in the "East" region.

2. Use SUMIFS to:

• - Calculate total Total Sale for "Honda" cars in the "West" region.
• - Calculate total Total Sale for a specific Salesperson during "January".

Section C: Averaging Data


1. Use AVERAGEIF to:

• - Find the average Price per Unit for "Maruti" cars.


• - Find average Units Sold in the "North" region.

2. Use AVERAGEIFS to:

• - Find the average Total Sale for cars sold in the "South" region with Units Sold > 3.
• - Find average Units Sold for a specific Brand in a specific Month.

Deliverables:
- Submit an Excel file with:
- The dataset fully filled and formulas correctly applied.
- Functions used in a separate section below or beside the dataset for clarity.
- Highlight or color the results of each function.
- Include a brief conclusion (2-3 lines) in a cell on what insights were gained using these
functions.

You might also like