Practical 7 – Basic Statistical Formulas

1. Objective

The primary objective of this practical is to familiarize students with Excel’s essential statistical functions, enabling them to quickly perform common data analyses. Students will learn to:

·         Utilize SUM to calculate totals.

·         Calculate the AVERAGE of a range of numbers.

·         Use COUNT to determine the number of numerical entries in a range.

·         Find the MAXimum and MINimum values within a dataset.

·         Identify the MEDIAN value in a given range.

·         Apply these functions to extract meaningful insights from tabular data, specifically the academic marksheet.

2. Introduction

Excel functions are pre-designed formulas that perform calculations. Basic statistical functions are among the most frequently used tools for summarizing and understanding data. Whether you need to find total sales, average scores, the highest-selling product, or simply count entries, these functions provide a quick and accurate way to do so. This practical will guide you through applying SUM, AVERAGE, COUNT, MAX, MEDIAN, and MIN to your marksheet data to gain immediate insights into student performance.

3. Pre-requisites

·         Successful completion of Practical 3.

·         Basic understanding of Microsoft Excel interface and data entry.

·         Familiarity with basic formula entry.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

Ensure you have your completed marksheet from Practical 3 open. We will use this table to demonstrate and practice these functions.

Step 5.1: Opening the Marksheet and Setting Up for Analysis

1.     Open the Marksheet: Open the Excel workbook containing your Marksheet from Practical 3.

2.     Navigate to a New Area for Analysis: Go to an empty area on your Marksheet tab, perhaps starting around cell L1 or M1, to perform these calculations without disturbing your main data.

3.     Add Labels for Results: In cells L1 through L6, type the following labels to organize your analysis:

o    L1: Total Marks for All Students

o    L2: Average Total Marks

o    L3: Number of Students Graded

o    L4: Highest Total Marks

o    L5: Lowest Total Marks

o    L6: Median Total Marks

Step 5.2: Using the SUM Function

The SUM function adds all the numbers in a specified range of cells.

1.     Select Cell for Result: Click on cell M1 (next to Total Marks for All Students).

2.     Enter SUM Formula: Type =SUM( and then select the range of Total Marks for all your students (e.g., G4:G8).

o    Your formula should look like: =SUM(G4:G8)

3.     Complete Formula: Press Enter.

o    Observation: This will display the sum of all students’ total marks.

Step 5.3: Using the AVERAGE Function

The AVERAGE function calculates the arithmetic mean of a range of numbers.

1.     Select Cell for Result: Click on cell M2 (next to Average Total Marks).

2.     Enter AVERAGE Formula: Type =AVERAGE( and then select the range of Total Marks (e.g., G4:G8).

o    Your formula should look like: =AVERAGE(G4:G8)

3.     Complete Formula: Press Enter.

o    Observation: This will display the average of all students’ total marks.

Step 5.4: Using the COUNT Function

The COUNT function counts the number of cells that contain numbers and dates in a range. It ignores empty cells and text values.

1.     Select Cell for Result: Click on cell M3 (next to Number of Students Graded).

2.     Enter COUNT Formula: Type =COUNT( and then select the range of Total Marks (e.g., G4:G8).

o    Your formula should look like: =COUNT(G4:G8)

3.     Complete Formula: Press Enter.

o    Observation: This will display the count of students for whom total marks are available. If you were to select a column with text (like Student Name), COUNT would return 0.

Step 5.5: Using the MAX Function

The MAX function returns the largest value in a set of values.

1.     Select Cell for Result: Click on cell M4 (next to Highest Total Marks).

2.     Enter MAX Formula: Type =MAX( and then select the range of Total Marks (e.g., G4:G8).

o    Your formula should look like: =MAX(G4:G8)

3.     Complete Formula: Press Enter.

o    Observation: This will display the highest total marks achieved by any student.

Step 5.6: Using the MIN Function

The MIN function returns the smallest value in a set of values.

1.     Select Cell for Result: Click on cell M5 (next to Lowest Total Marks).

2.     Enter MIN Formula: Type =MIN( and then select the range of Total Marks (e.g., G4:G8).

o    Your formula should look like: =MIN(G4:G8)

3.     Complete Formula: Press Enter.

o    Observation: This will display the lowest total marks achieved by any student.

Step 5.7: Using the MEDIAN Function

The MEDIAN function returns the median (the middle number) of the given numbers. The median is the number in the middle of a set of numbers that has been sorted in numerical order.

1.     Select Cell for Result: Click on cell M6 (next to Median Total Marks).

2.     Enter MEDIAN Formula: Type =MEDIAN( and then select the range of Total Marks (e.g., G4:G8).

o    Your formula should look like: =MEDIAN(G4:G8)

3.     Complete Formula: Press Enter.

o    Observation: This will display the median total marks. If there’s an even number of data points, it calculates the average of the two middle numbers.

Step 5.8: Formatting the Results

1.     Format Numbers: Select cells M1 through M6.

2.     Apply Number Format: On the Home tab, in the Number group, choose Number with 0 decimal places from the dropdown, or click the , button for better readability.

3.     Bold Labels: Select cells L1 through L6 and apply Bold formatting.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet:

1.     Subject-wise Analysis:

o    In a new area of your worksheet (e.g., starting L8), create new labels: Total Sub 1 Marks, Avg Sub 1 Marks, Highest Sub 1 Score, Lowest Sub 1 Score.

o    Use the SUM, AVERAGE, MAX, and MIN functions to calculate these statistics for Subject 1.

o    Repeat this for Subject 2, Subject 3, Subject 4, and Subject 5.

2.     Grade Counting:

o    Using the COUNTIF function (which you’ll learn more about later, but can explore now as a challenge), count the number of students who received Grade A, Grade B, and Grade C in your marksheet.

o    (Hint: =COUNTIF(Range, "Criteria"))

3.     Pass/Fail Count:

o    Use COUNTIF to count how many students Passed and how many Failed.

4.     Overall Percentage Statistics:

o    Find the AVERAGE, MAX, MIN, and MEDIAN of the Percentage column for all students.

5.     Adding New Data: Add two new students with their marks to your marksheet. Observe how the summary formulas you created in Step 5.2-5.7 automatically update (if your range selection was dynamic, or you might need to adjust the formula ranges).

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with the Marksheet sheet, including all summary calculations from the procedure and exercises.

·         Ensure all functions are correctly implemented and yield accurate results.

·         Verify that results are formatted appropriately for readability.

·         Be prepared to explain the purpose and application of each function used.