Practical 14 – Applying Goal Seek and Subtotal

1. Objective

The primary objective of this practical is to equip students with the ability to use Excel’s advanced data analysis tools: Goal Seek and Subtotal. Students will learn to:

·         Utilize Goal Seek to find the input value required to achieve a desired result in a formula.

·         Understand and apply Subtotal to automatically group and summarize data within a sorted list.

·         Analyze data efficiently for decision-making and reporting.

·         Manage and interpret the hierarchical outline created by the Subtotal feature.

·         Integrate these tools into their data analysis workflow.

2. Introduction

In real-world scenarios, you often know the outcome you want to achieve but are unsure what input value is needed to reach that outcome. Goal Seek is Excel’s "what-if" analysis tool that solves exactly this problem, working backward from a result to find the required input. Conversely, when dealing with large datasets, summarizing information quickly is crucial. The Subtotal feature allows you to automatically group rows of related data and calculate summary statistics (like sums, averages, counts) for each group, providing a hierarchical view of your data without manual formula creation. This practical will demonstrate how to apply these features using your existing marksheet and other relevant datasets.

3. Pre-requisites

·         Successful completion of Practical 3.

·         Basic understanding of Microsoft Excel interface and formula entry.

·         Familiarity with data sorting (from Practical 4).

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 tools.

Step 5.1: Opening the Marksheet and Preparing Data

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

2.     Ensure Formulas are Correct: Verify that Total Marks, Percentage, Grade, and Result columns are correctly calculated with formulas.

3.     Add Sample Data (if needed): Ensure you have at least 5-7 students with varying marks and grades to make the Subtotal demonstration more impactful.

Step 5.2: Applying Goal Seek

Let’s use Goal Seek to find out what marks Alice needs in Subject 5 to achieve a Percentage of 80%.

1.     Identify Target Cells:

o    Set cell: H4 (Alice’s Percentage cell) – this cell contains the formula =G4/500.

o    To value: 0.8 (or 80%) – this is our target percentage.

o    By changing cell: F4 (Alice’s Subject 5 marks) – this is the input we want to change.

2.     Access Goal Seek:

o    Go to the Data tab on the Excel ribbon.

o    In the Forecast group, click What-If Analysis.

o    Select Goal Seek....

3.     Fill in the Goal Seek Dialog Box:

o    In the Set cell: box, enter H4.

o    In the To value: box, type 0.8.

o    In the By changing cell: box, enter F4.

4.     Click OK.

o    Observation: Excel will perform the calculation. A Goal Seek Status dialog box will appear, showing the original value and the new value. Alice’s Subject 5 marks (F4) will change to the required value, and her Percentage (H4) will update to 80%.

o    Note: If the required mark exceeds the maximum (e.g., 100 for a subject), Goal Seek will still show the mathematically required number, even if it’s unrealistic.

5.     Keep or Cancel: Click OK to accept the change or Cancel to revert to the original values. For this practical, click Cancel to restore Alice’s original Subject 5 marks.

Step 5.3: Applying Subtotal

We will use the Subtotal feature to calculate the average Total Marks for each Grade.

1.     Sort the Data: Before applying Subtotal, the data must be sorted by the column you want to group by.

o    Select any cell within your marksheet data (e.g., A3).

o    Go to the Data tab.

o    In the Sort & Filter group, click Sort.

o    In the Sort dialog box, select Grade for Sort by, Cell Values for Sort On, and A to Z for Order.

o    Click OK.

o    Observation: Your marksheet will now be sorted by Grade.

2.     Access Subtotal:

o    Select any cell within your marksheet data.

o    Go to the Data tab.

o    In the Outline group, click Subtotal.

3.     Fill in the Subtotal Dialog Box:

o    At each change in: Select Grade (because we want to group by Grade).

o    Use function: Select Average (because we want the average Total Marks).

o    Add subtotal to: Check Total Marks. Uncheck any other boxes.

o    Replace current subtotals: Keep checked (important if you’re reapplying subtotals).

o    Page break between groups: Uncheck (usually not needed for on-screen analysis).

o    Summary below data: Keep checked (places the average after each grade group).

4.     Click OK.

o    Observation: Excel will insert new rows with Average calculations for each grade, and a Grand Average at the bottom. It also creates an Outline structure on the left side of the worksheet with levels 1, 2, and 3.

Step 5.4: Navigating and Removing Subtotals

1.     Use the Outline Levels:

o    Click 1 on the left side of the worksheet. This shows only the Grand Average.

o    Click 2. This shows Grand Average and the Average for each Grade.

o    Click 3. This expands all data, showing individual students and the averages.

o    Observation: These levels allow you to quickly collapse and expand your data for different views.

2.     Remove Subtotals:

o    Select any cell within your data range.

o    Go to the Data tab > Outline group > Subtotal.

o    In the Subtotal dialog box, click Remove All.

o    Observation: All the inserted subtotal rows and the outline structure will be removed, and your data will return to its state before Subtotal was applied (though it will remain sorted).

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet or a new dataset:

1.     Goal Seek for Pass/Fail:

o    For a student who Failed, use Goal Seek to find out what Subject 4 marks they need to achieve a Percentage of 35% (to Pass). Accept the change temporarily, note the required marks, then cancel.

o    What are the marks needed for Subject 4?

2.     Goal Seek for Grade B:

o    For a student who received Grade C, use Goal Seek to determine what marks they need in Subject 3 to achieve a Percentage of 60% (for Grade B). Again, note the marks and then cancel.

3.     Subtotal by Result:

o    Sort your marksheet by the Result column (Pass/Fail).

o    Apply Subtotal to calculate the Count of students for each Result (Pass/Fail).

o    Practice collapsing and expanding the outline levels. Take a screenshot of the view when collapsed to level 2.

o    Remove the subtotals.

4.     Subtotal for Maximum Scores:

o    Sort your marksheet by Grade.

o    Apply Subtotal to find the Maximum Percentage achieved within each Grade (A, B, C, etc.).

o    Remove the subtotals.

5.     Combined Subtotal:

o    On a new sheet, create a small table with Department, Employee Name, and Salary.

o    Populate with 2-3 departments and a few employees each.

o    Sort the data first by Department, then by Salary (ascending).

o    Apply Subtotal to find the Sum of Salary for each Department.

o    Then, without removing the first subtotal, apply Subtotal again to find the Average of Salary for each Employee Name within each department (this is a tricky one, ensure "Replace current subtotals" is unchecked for the second subtotal). Observe the outline levels.

o    Remove all subtotals.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with your Marksheet sheet and any new sheets created for exercises.

·         For Exercise 1 and 2, clearly state the marks required to achieve the goal.

·         Provide the screenshot for Exercise 3.

·         Be prepared to demonstrate how to use Goal Seek and Subtotal to your instructor.

·         Be able to explain the situations where Goal Seek is most useful and why sorting is crucial for Subtotal.